On Tue, 30 Dec 2008 21:50:03 GMT, "brownti via AccessMonster.com" <u31540[ at ]uwe>
wrote:
[Quoted Text] >I get a weekly schedule sent to me in an excel file that has about 200 rows
>of addresses, descriptions, and dates. In the past we have gone through last
>weeks list and this weeks list to find differences. This takes way too long
>for something that access can do. I have got to the point of comparing
>existing records to last weeks records but not been able show new records
>that were not on last weeks. Here is the SQL that i am using to compare
>tables. Can anyone please help me adapt it to also show records that didnt
>exist on DeliveryOld but now do on DeliveryNew and vica versa? THanks
To do this you need a "Full Outer Join", which is not supported in Access; you
can get around this limitation by combining a Left Outer Join with a Right
Outer Join using a UNION query:
SELECT [DeliveryOld].Address, [DeliveryOld].DeliveryDate, [DeliveryOld].
Description, [DeliveryNew].Address, [DeliveryNew].DeliveryDate, [DeliveryNew].
Description
FROM [DeliveryOld] LEFT JOIN [DeliveryNew] ON
[DeliveryNew].Address=[DeliveryOld].[Address]
WHERE ([DeliveryNew].DeliveryDate<>[DeliveryOld].[DeliveryDate] OR
[DeliveryOld].[DeliveryDate] IS NULL)
AND ([DeliveryNew].Description<>[DeliveryOld].[Description] OR
[DeliveryOld].[Description] IS NULL)
UNION
SELECT [DeliveryOld].Address, [DeliveryOld].DeliveryDate, [DeliveryOld].
Description, [DeliveryNew].Address, [DeliveryNew].DeliveryDate, [DeliveryNew].
Description
FROM [DeliveryOld] RIGHT JOIN [DeliveryNew] ON
[DeliveryNew].Address=[DeliveryOld].[Address]
WHERE ([DeliveryNew].DeliveryDate<>[DeliveryOld].[DeliveryDate] OR
[DeliveryNew].[DeliveryDate] IS NULL)
AND ([DeliveryNew].Description<>[DeliveryOld].[Description] OR
[DeliveryNew].[Description] IS NULL)
This will need some tweaking I imagine; but the first query will show all
records where there is a matching address in both tables with a different
delivery date or description, or there is a record in deliveryold that
doesn't exist in deliverynew; the second SELECT will in addition show those
records that have disappeared from deliveryold. The UNION will combine them to
show all changed records.
--
John W. Vinson [MVP]
|