A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Moving from xBase/Clipper



 
 
Thread Tools Display Modes
  #1  
Old February 3rd, 2005, 06:55 AM
external usenet poster
 
Posts: n/a
Default Moving from xBase/Clipper

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.
  #2  
Old February 3rd, 2005, 08:25 PM
Albert D. Kallal
external usenet poster
 
Posts: n/a
Default

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!!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Chart with 39-week moving average Herbert Chan Charts and Charting 2 September 7th, 2004 02:29 PM
Outlook (2003) archive and moving problems ToD General Discussion 1 July 20th, 2004 04:36 PM
I need help moving my current address book to my new PC!!!! Dan Ramsey Contacts 2 June 3rd, 2004 09:21 PM
moving numbers between columns without moving words Papadapa Worksheet Functions 2 December 18th, 2003 05:36 PM
Wighted Moving average Peter Worksheet Functions 1 October 17th, 2003 01:19 PM


All times are GMT +1. The time now is 05:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.