Access 2000 database question

[nczman]

I have an Access 2000 database with two tables. Table 1 is the master with 78000 records. Table 2 is a sub set of table 1 with about 15000 records.

What I am trying to do, is create a 3rd table. This table will also be a subset of table 1, but will be a list of the records that are not listed in table 2.

Thanks in Advance

 

[Anne Troy]

First of all, you say that Table2 is a subset of Table1. If table 2 and table 1 have the same basic data, then they should all be in one table.

Suppose table1 is your company's branch offices in California, and table2 is your company's branch offices in Nevada. What you need to do is create another field in table1 called State. Make all your California's have CA in that field. Then pull al your table2's in and make them NV.

I can't really help with table3 until I know about table 1 and 2. If you've got information you'd rather not "broadcast" online, then you can send me your database if you like, or save the DB to a new name, delete all but a few of the records, and send that.

dreamboat@nni.com

 

[nczman]

Table 1 is all of the solutions ( whether on not they are mutliple) that we have ever used at the support line. We only have about 8 or 9 products, so we really have propably about 5000 valid solutions. The rest are already listed somewhere else.

table 2 was created as a copy of table 1 and I did my mass delete of what I thought was unused solutions. If it has been used less than 5 times and has not been used in the last 2 years. There were solutions to products that we do not use that were mass deleted.

Now they are wanting a list of what I deleted... all 60000 records.

 

[downwitchyobadself]

If I understand you correctly (note that bold thar), you have one of two choices. Both involve running a make-table query which will generate table3.

Either

- You have some sort of unique key, or set of keys, common to table1 and table2, in which case you build the query by adding table1 and table2 to the "add tables" bit, create a join from each table1.KeyField to each table2.KeyField, and double-click each join, make it an "outer join", option number 2, all records from table1 and only those records from table2 that match, then set your criteria to where one of the table2.KeyField s "Is Null".

- or you just run it against table1, and reverse the criteria you used to pull out the table2 records. Something like DteLogged < DateAdd("yyyy",-2,dateyoumade table2) and then a second group-by query counting each instance, only pull those <5 or <=5.
Sloppier, and may not get all 60000, but should get pretty close.

I'm not sure how much of this reply you can understand. You'll let us know, though, and we'll help you out from there.

(Dreamy's right, by the way, in a sense. If table2 replaces table1, you should've deleted the table2 records from table1 when you made table2. Then you wouldn't need a table3.)

 

[nczman]

I tried option 2 and it was sloppy. Bosses threw a hissy. We are moving from an Oracle database to another system. I moved the solutions to Access so that I could clean up some of the duplicate or out of date solutions.

I cut the solutions using quite a few queries in Access. Some were random deletes from a query, some were mass deletes. They kind of freaked over deleting 60000 thinking that just maybe there were some that should be moved to the new database.

Now they are wanting to review what I have deleted.

I tried to do a query from Table 1 Joining Table 2 where Joined fields from both were equal. I thought then, I could do a Select all records, then delete and what I had left would be those I had originally deleted. Problem was, it would not let me delete. I am not sure I understand option 1, but I will see where outer join takes me.

Also, I am not writing queries. I am using the query that comes with Access and entering Likes and = in the query designer.

 

[nczman]

could not figure that one out. I also tried to change the query to a delete, but kept getting that I needed to select the table and then I just did not know the syntex.

 

[wedor]

Do these tables have a primary key? or a unique field for each record?

 

[downwitchyobadself]

Okay, I've attached an example of the what the design view of the select query should look like. How you do it:

1. Create a new select query. Add Table1 and Table2.
2. Create joins from Table1 to Table2 between each and every field required to independently identify a record in both tables (there must be no doubt that the field or combination of fields yields a single common record in the two tables.
3. Double-click each join, set it to option 2. This tells Access to include everything in Table1, and only include a Table2 record if it matches up.
4. Set the criteria for one of the Table2 key fields to "Is Null". This tells Access to exclude every record where the key exists in Table2, thus excluding any record common to both tables.
5. Save this query, call it whatever you like, say Query1. (Make sure it contains all fields you wish to output to Table3.)
6. Create a new make-table query, to output to Table3. Add Query1 as the source. Select all fields you wish to output.

I repeat that this method is only viable if you have used unique identifiers (an ID field, or a combination of ID fields, is usually the answer) which are common to both tables. If you use an AutoNumber field, for example, and one record is 10000 in Table1 and 12015 in Table2, you're SOL.

Hope this gets you going.

 

[nczman]

That works great, downwitchyobadself. Life is so much simpler when it works...

Thanks again.