Table differences

[brownti via AccessMonster.com]

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

SELECT [DeliveryNew].Address, [DeliveryNew].DeliveryDate, [DeliveryNew].
Description
FROM [DeliveryOld], [DeliveryNew]
WHERE ((([DeliveryNew].Address)=[DeliveryOld].[Address]) AND (([DeliveryNew].
DeliveryDate)<>[DeliveryOld].[DeliveryDate]) AND (([DeliveryNew].Description)
=[DeliveryOld].[Description]))
ORDER BY [DeliveryNew].Address, [DeliveryNew].DeliveryDate;

 

[John W. Vinson]

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.

 

[brownti via AccessMonster.com]

This doesnt seem to work...It shows a bunch of records with 6 columns. I
cant figure out what each query is doing if i try and break them apart. The
end result should only show three columns, address, deliverydate, and
description. i think i should be able to use the first part that i have, and
then combine that with a query that looks for records that dont exist in
DeliveryOld but do exist in DeliveryNew. Again here is the working query
that i have:

SELECT [DeliveryNew].Address, [DeliveryNew].DeliveryDate, [DeliveryNew].
Description
FROM DeliveryOld, DeliveryNew
WHERE ((([DeliveryNew].Address)=[DeliveryOld].Address) And (([DeliveryNew].
DeliveryDate)<>[DeliveryOld].DeliveryDate) And (([DeliveryNew].Description)=
[DeliveryOld].Description))
ORDER BY [DeliveryNew].Address, [DeliveryNew].DeliveryDate;

Thanks for any help you can give

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

[quoted text clipped - 4 lines]

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.