vb for record comparison

[user451]

My problem is this: I have been programming in xBase dialects and
clipper for 15 years and keep putting off getting up to speed on OO
stuff. Anyway, my other, real problem is this:

I need to compare two tables in Access, and write any differences to a
third table. I'm so used to just looping through with code (with 3
workspaces and 3 pointers), that I'm at a loss how to get started. If
someone can point me in the right direction, I'm sure I can write the
logic for determining how the tables are different. A quick idea of
what I want:

Table 1 & 2:
Fields: State_ID (char 2)
Tag_num (char 5)
Origin (char 20)
Num_widgets (INT)
Num_pies (INT)
Num_birds (INT)
Num_shoes (INT)
Table 3 would list changes from 1 to 2, over 1 period of time:
For example at 1pm, Table 1 data was input, and a record of:
(NY 1234A Baltimore 24 2 0 5) exists.
At 3pm, Table 2 data is input and a records exists:
(NY 1234A Baltimore 24 2 1 5)

desired output is Table 2 data copied to Table 3.
However, if all the data remains the same, no entry to be made in
table 3. Also, if a State_ID, Tag_num exists in table 2 but not Table
1, then the record in Table two would be copied to Table 3. In other
words, I only want to see the changes in table 3. I can do the code, I
just need a head start of record navigation and the workspace concept
using Access. Thanks in advance for your help. And the reason that I
just don't sit down for a few days and work it out is that it is very
time-sensitive.

 

[Tim Ferguson]

Table 1 & 2:
Fields: State_ID (char 2)
Tag_num (char 5)
Origin (char 20)
Num_widgets (INT)
Num_pies (INT)
Num_birds (INT)
Num_shoes (INT)

desired output is Table 2 data copied to Table 3.

However, if all the data remains the same, no entry to be made in
table 3. Also, if a State_ID, Tag_num exists in table 2 but not Table
1, then the record in Table two would be copied to Table 3.

I don't think I'd use VBA at all for this one; it should be manageable as
a single query. The idea is to match the tables on all the fields and
then select the ones where there is no match:

INSERT INTO Table3
(State_ID, Tag_Num, Origin,
Num_Widgets, Num_Pies, Num_Birds, Num_Shoes)
SELECT t2.StateID, t2.Tag_Num, t2.Origin,
t2.Num_Widgets, t2.Num_Pies, t2.Num_Birds, t2.Num_Shoes
FROM Table1 AS t1 LEFT JOIN Table2 AS t2
ON t1.State_ID = t2.State_ID
AND t1.Tag_Num = t2.TagNum
AND t1.Origin = t2.Origin
AND t1.Num_Widgets = t2.Num_Widgets
AND t1.Num_Pies = t2.Num_Pies
AND t1.Num_Birds = t2.Num_Birds
AND t1.Num_Shoes = t2.Num_Shoes
WHERE
t1.State_ID IS NULL



This supposes that the Primary Key of the table is State_ID, although you
don't say so. Also, I have not checked this for correctness as I am
guessing that the field names are made-up!

Hope it helps


Tim F

 

[Albert D. Kallal]

As mentieond, you don't need code.

So, the solution here is to build a query that joins the two tables. Fire up
the query builder, and drop in table1, and table 2.

Draw the join line from tag in table1 to table2. Also, draw a join line from
stateID between the two tables also.

Now, from table 1, drag and drop the 4 fields into the query grid. (you do
NOT need to put the tag, and state ID into the query grid, as we know these
are the SAME already! Note that this query is only going to return records
that are different, and this is thus step 1

ok, so we placed the following (from table1) into the query grid:

Origin, Num_widgets, Num_pies, Num_birds, Num_shoes


(by the way, if we had more fields, I might take a different approach..but
you only got a few fields).

Ok, now, lets make this query return any differences. In the criteria field
for Origin, type in:

<> table2.Origin (the editor will put brackets around this like
[table2].[Origin]

Repeat the above process for the next 3 fields

Now, you can save the query. If you run the query, ONLY records that are
different in those 4 fields will be returned. At this point, we could write
a loop in code to process this, and update. However, there is no need. Now
that you got the query working, and you verified that a few records returned
by the query are in fact different records. The next thing is to turn this
into a update query. Bring up this query in design view. click on the menu
"query"

then select update query. In the update to: field, simply type in the fields
again from table 2 (you could cut and paste from the criteria field, and
remove the "<>"

Again, now save this query. If we run this query, then any record with the
same tag, and same state_ID that has a DIFFERENCE in any of the remain 4
fields will now be updated to be the same. You can now run this query, and
it will do the update for you. Hey, note at this point we have not written
ONE line of code!! To now write code that does the update, we go:

docmd.RunSql "yourQuery"

So, at this point, we have ONE line of code to do this!! And, if you wish,
you can just double click on the query, and not even bother with the above
one line of code.

Now, we need to do the 2nd part. The 2nd part is to add any record in table
1 (tag + stateID) that does not exist in table 2. Again, lets fire up the
query builder (by the way, I think by now you are beginning to understand
the trick in ms-access is to learn, and understand sql. I should say that I
learned sql in 1991, and that was using FoxPro (so, I am VERY familiar with
the xBase language...and worked a good number of years with xBase variants
such as FoxPro).)

Ok, this time, we want the query to return records that don't exist in
table1, are in table2, and want to output to table3. drop in table2, table1
(the order in which you drop in makes a difference here). Draw the join line
from table2 to table1 (tag). However, this relation join we just made will
ONLY return records from both tables where the values are equal. Obviously,
the 1st table will NOT have the values. So, double click on the join line.
We want to make a left join (all records from table2, and only those from
table1). Think "LEFT" join mans all the values on the "left" side, and the
guys on the right side do NOT have to exist for the join to work. Now, we
simply drag and drop from table1 into the query grid tagID. In the criteria
field, we go:

is null

All this means is that we put in a condition that the query should ONLY
return records where tagID does NOT exist. Do the same thing for sateID.
Join line, change to left join, put in criteria of is null.

Now, simply drop in the rest of the fields from table2. Save the query, Test
the query. This query will return ONLY records that don't exist in table
one. Now, change the query to a append query (open up query, go
query->append query...when it prompts you, just type in table3). Now, just
enter all the correct field values for AppendTo (they will all be the same
as "field).

Ok, this query will simply append any record that don't exist

at this point, really, you could just run the query queries from the
ms-access UI, and not write ONE line code. Cool eh? (that takes a real whack
of code in a xBase language..and at this point we don't even need any
code!!!).

You could as mentioned, have the two queries and run in code.

docmd.RunSql "queryUpdateDifferntGuys"
docmd.RunSql "queryCopyGuysThatDoNotExist"

So, at this point, I count two lines of code to only "run" the sql we made.

As you mentioend, you are in a hurry here. I suppose I could
get around to writing some sample code..but you don't really
need any (you know, open 3 tables in code..loop...etc.).