Moving from xBase/Clipper

[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.

 

[Albert D. Kallal]

Ok. a few things:

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....


If I was not in a hurry, I could write you some sample code that would do
the above also (you know, open 3 tables in code..loop...etc.). You
mentioned, you are in a hurry..and just want the shortest solution, so I
gave you one that is quick, and don't even take code!!