PLZ HELP!!! matched qurey 1

[Herdrich]

OK i have a major issue. I have a database that keeps track of issued badges on a server. a few people decided to copy the entire database instead of creating a shortcut to their desktops. So i have 4 copys of the extremely large database about 4200 entries each. i need to be able to create a query that only shows the entries that do not match on all 4 of the databases. if a SSN matches on all 4 then i dont want to see it if it only matches on 3 or less i want to see it. Thank you for your time

~Glenn

 

[BrianLe]

What do you plan to do after you have the records that are not in all 4 tables? I may be all wrong, but I think it would be better to create a table that has the ones that are in any of the tables. To do that, I think you could create an append query to combine the records from each of the 4 tables into one table (Tbl1). Then create a "SELECT DISTINCT" query of the combined table to get the records that were in any of the original 4 tables.

SELECT DISTINCT Tbl1.SSN, Tbl1.YourFieldName
FROM Tbl1;

Does that help?

 

[Herdrich]

once i remove all the data that is the same from the 4 lists i will be able to print out the 4 lists and and figure out what entries were added and what was deleted so i can restore my list. So i guess i actually want to see what is the same not what is different. Then remove what i found from each of the lists.

 

[BrianLe]

Let's assume you have 4 tables with fields SSN,Name

Table1
1,Bill
2,Tom
4,Dave

Table2
1,Bill
4,Dave
5,John

Table3
1,Bill
2,Tom
3,Steve

Table4
1,Bill
3,Steve
4,Dave

Do you want to end up a table like the following?

Tbl1

1,Bill
2,Tom
3,Steve
4,Dave
5,John

 

[GhostWolf]

That sounds like what he's after.

Something like this bit of pseudocode might work:

-------------------------------
select * from each remote db into wrkTable
-------------------------------
select distinct ssn, count(*) as qty
into wrkTable2
from wrkTable
group by ssn
-------------------------------
delete from wrkTable2 where qty=4
-------------------------------
insert [fieldnames]
into mainDbTable
from wrkTable as t0
inner join wrkTable2 as t1
on t1.ssn = t0.ssn
-------------------------------
delete from wrkTable
delete from wrkTable2
-------------------------------

 

[Herdrich]

Some what like that ex
Table1
1,Bill
4,Dave

Table2
1,Bill
4,Dave
3,Steve

Table3
1,Bill
3,Steve

Table4
1,Bill
4,Dave
3,Steve
6,James

Bill is in all of the tables so he stays.
Dave is missing from Table 3 so he has to be removed from all
Steve is missing from Table 1 so he has to be removed from all
James is added in table 4 so he has to be added to all

My main goal is to remove all of the bills then only have to deal with the names that are different and i could do that manually.

 

[GhostWolf]

So

if wrkTable2.qty=1 then
insert [filednames] into mainDbTable (etc)
else delete from mainDbTable

 

[Herdrich]

If i could get this code working it may be the answer if i just combine all of the tables together then run something like the pseudocode below.


if ssn = 3 then remove
else
ssn = 1 then add to tblgood
else
ssn = 4 add to tblgood

 

[GhostWolf]

How about


if wrkTable2.qty in (1,4)
add to tblGood
else
delete from tblGood


One small problem though: if qty = 4 it's entirely possible that one already exists in tblGood. Is tblGood being rebuilt every time this process runs?

 

[Herdrich]

tblgood was just an example of all the good files going into a destination. That database would be empty until the code is ran.

Where would i place that code and what dose wrkTable2.qty refer to? The table that all the data is in?

 

[GhostWolf]

The second step in my first post creates wrkTable2; qty is one of the fields in it.

As to where to put the code to do this: good question! As long as it has a path to all five databases...

You can either use VBA to code the process in a module, or you can create a series of queries and execute them via macro.

 

[BrianLe]

Once you have the list of records that are either in all four tables or in only 1 table, what are you going to do? Are you going to go into each table and manually delete all the other records? From your example, what records do you want to end up with in each of the 4 tables after you do the manual manipulation? Do intend to do this only once and then control access to the table?

 

[Herdrich]

if i have to manually alter the records i will but i want to keep an entry if it is only in one table or if it is in all of the tables. so if it is in 3 of the tables i dont want that record

 

[BrianLe]

So from your example, you want to end up with a table with Bill and James. From my example you would want a table with Bill and John. If that can be done without manual manipulation, is that what you would like?

 

[Herdrich]

Yep. Right now i have it again split up but i have a list of adds and deletes. I can add the ones i need with no problem but how do i delete all the entry's from the good list that match the delete list?

 

[BrianLe]

I think this will work.

Make a table "Combined" with fields SSN and Name.

Then combine all the tables into table "Combined" with 4 "Append Queries" (Combined Query1,Combined Query2, Combined Query3, Combined Query4).

"Combined Query1"
INSERT INTO Combined (SSN,Name)
SELECT Table1.SSN, Table1.Name
FROM Table1;

Change Table1 to Table2,Table3,Table4 for the other 3 append queries.

Then create a "Select" query "Combined Query" to query the table Combined to find how many of each SSN are in table Combined.

"Combined Query"
SELECT SSN,Name,Count(SSN) AS Count
FROM Combined
Group by SSN,Name;

Then create a "Select" query "Combined Query Query" to find the records with counts of 1 or 4.

"Combined Query Query"
SELECT [Combined Query].SSN,[Combined Query].Name
FROM [Combined Query]
WHERE ((([Combined Query].Count)=1 Or ([Combined Query].Count)=4));

Then create a table "tblGood" with a "Make Table Query" "Combined Query Query Query" that puts the results of the "Combined Query Query" into tblGood

"Combined Query Query Query"
SELECT [Combined Query Query].SSN, [Combined Query Query].Name
INTO tblGood
FROM [Combined Query Query];

Each time you want to run the process, you will have to first delete the records from table "Combined".

PS - I'm using Access 97.

Hope that helps.

 

[Herdrich]

thx for the help got everything back to normal this morning.