Finding differences between 2 set of data

[johnb]

I have 2 tables of data. This weeks data and last weeks
data. Both have the same number of columns. Both will have
different number of rows. Fields to focus on are
MaterialNo, DeliveryDate and DeliveryQuantity.

Tables are not normalised. The relationship is many-to-
many. Tables can have upto 10,000 rows. Many instances of
the principal fields exist in each table. Concatinating
the 3 fields produces a unique key.

I need to know where changes have ocurred in DeliveryDate
and/or DeliveryQuantity.

However. Comparing row x in this weeks table with row x in
last weeks can produce wrong results due to a record
missing in either week

I am unsure how to approach this problem. I tried using 2
arrays but got bogged down in the VBA details. Comments
please

 

[Ken Snell]

You could run a query that joins the two tables and returns all fields of
interest. You then could look at the values in the fields to see if they're
different.

SQL statement would be something like this for the query:

SELECT Table1.MaterialNo, Table1.DeliveryDate, Table1.DeliveryQuantity,
Table2.MaterialNo, Table2.DeliveryDate, Table2.DeliveryQuantity
FROM Table1 LEFT JOIN Table2
ON Table1.PrimaryField1 = Table2.PrimaryField1 AND
Table1.PrimaryField2 = Table2.PrimaryField2 AND
Table1.PrimaryField3 = Table2.PrimaryField3;

Opening a recordset based on the above, and then searching it for the
desired "primarykey" values, will allow you to compare the MaterialNo, etc.
fields' values from the two tables.

 

[Marc]

<snip>

I have 2 tables of data. This weeks data and last weeks
data. Both have the same number of columns. Both will have
different number of rows. Fields to focus on are
MaterialNo, DeliveryDate and DeliveryQuantity.

Tables are not normalised. The relationship is many-to-
many. Tables can have upto 10,000 rows. Many instances of
the principal fields exist in each table. Concatinating
the 3 fields produces a unique key.

I need to know where changes have ocurred in DeliveryDate
and/or DeliveryQuantity.

However. Comparing row x in this weeks table with row x in
last weeks can produce wrong results due to a record
missing in either week

I am unsure how to approach this problem. I tried using 2
arrays but got bogged down in the VBA details. Comments
please

I would try a union query (go into query designer then SQL view) and make a
determining field so that you always read this weeks record first if it
exists.
Then in the VBA read the query

rs.Open "queryname"
while rs.EOF = false
if rs.whatever = savedfieldofpreviouswhatever ' 2nd record of pair
find rs2thisweek where rs2.whatever = rs.whatever
find rs3lastweek where rs3whatever = savedfieldofpreviouswhatever
perform calcs for the different weeks
' output to new file ??
else
if savedfieldof previouswhatever > 0 ' don't process first rec
' you only found one record
' do the processing for one record. if it matters which week, add
' another if to determine whether to do rs2 or rs3
end if
' if they're not equal you're reading the first record of the pair
savedfieldofpreviouswhatever = rs.whatever
endif
rs.movenext
wend
'repeat some processing to cater for last record.

HTH
Marc

 

[Old_VBA]

In Access 97 there used to be a very easy way to do this using the Query Wizard called an "Unmatched Query"--unfortunately newer versions of Access don't seem to have that wizard as part of the package, and the help search engine doesn't recognize the keyword "unmatched". Of course, since I always used the Wizard, the thinking part of my brain never mastered the skill independently....

If anyone can remember what those Unmatched Queries did and how they did it, I would be very grateful
----- Marc wrote: ----

I have 2 tables of data. This weeks data and last week
data. Both have the same number of columns. Both will hav
different number of rows. Fields to focus on ar
MaterialNo, DeliveryDate and DeliveryQuantity
many. Tables can have upto 10,000 rows. Many instances o
the principal fields exist in each table. Concatinatin
the 3 fields produces a unique key
last weeks can produce wrong results due to a recor
missing in either wee
arrays but got bogged down in the VBA details. Comment
pleas

I would try a union query (go into query designer then SQL view) and make
determining field so that you always read this weeks record first if i
exists
Then in the VBA read the quer

rs.Open "queryname
while rs.EOF = fals
if rs.whatever = savedfieldofpreviouswhatever ' 2nd record of pai
find rs2thisweek where rs2.whatever = rs.whateve
find rs3lastweek where rs3whatever = savedfieldofpreviouswhateve
perform calcs for the different week
' output to new file ?
els
if savedfieldof previouswhatever > 0 ' don't process first re
' you only found one recor
' do the processing for one record. if it matters which week, ad
' another if to determine whether to do rs2 or rs
end i
' if they're not equal you're reading the first record of the pai
savedfieldofpreviouswhatever = rs.whateve
endi
rs.movenex
wen
'repeat some processing to cater for last record

HT
Mar

 

[Ken Snell]

It's in the versions later than A97. When in the query window of the
database window, click on New icon on toolbar. That will let you have choice
of wizards.


--
Ken Snell
<MS ACCESS MVP>

In Access 97 there used to be a very easy way to do this using the Query

Wizard called an "Unmatched Query"--unfortunately newer versions of Access
don't seem to have that wizard as part of the package, and the help search
engine doesn't recognize the keyword "unmatched". Of course, since I always
used the Wizard, the thinking part of my brain never mastered the skill
independently.....