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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |