How to avoid duplicate records in two queries

[hkgary33 via AccessMonster.com]

Dear all,
I've built two queries (qrySearch1 & qrySearch2), and both of them shows same
fields but different results because of the difference of the design of each
query. Now, I would like to combine this two queries based on two key fields
"FileNo" & "StaffID" in these two queries. The expected result is to avoid
duplicate records shown based on these fields.
For example, in qrySearch1, it consists of these records:
FileNo StaffID
1 777
3 666
7 444

And in qrySearch2, it consists of these records:
FileNo StaffID
1 444
1 777
6 555

Then in the resultant query, I would like to expect:
FileNo StaffID
1 444
1 777
3 666
6 555

Can anybody help me to join these two queries so as to produce such result?I
really have no idea on this, plz help!!

Thanks so much!!!!!

Gary

 

[Guest]

I'm guessing your final result would also include FileNo 7, StaffID 444 from
qrySearch1. If so, here is a possible solution, but probably not the
smoothest:

Turn qrySearch1 into a MakeTable query called say, tblSearchAll. After
running that query, go into the design of it and make FileNo and StaffID a
two field Primary Key. Then turn qrySearch2 into and Append query and append
it to tblSearchAll. When you run it, any new FileNo/StaffID cominations will
be added, but ones already existing will not (you will get an error message
giving a count of those that were already there). If you need to run this
periodically, always run qrySearch1 first to replace the old tblSearchAll
with all new data. You will likely have to reset the two field Primary Key
again each time. Don't forget to Compact and Repair this database once and
awhile to cut down on the size.