Merge Recordsets with BOOLEAN criteria

[thewiseguy]

i have three recordsets rst1, rst2 and rstfinal, all with [IDNumber] as a common field.

i would like to somehow merge rst1 and rst2 into rstfinal using the following three fashions:

1) AND merge - find all IDNumber's common in both rst1 & rst2 and place in rstfinal. i.e. if rst1 and rst2 both have IDNumber 12345 in common, rstfinal will contain 12345 once.

2) OR merge - join both rst1 and rst2 and then replace duplicates with single entry. i.e. if rst1 contains 12345 and 11111, and rst2 contains 12345 and 22222, then rstfinal will contain 12345, 11111 and 22222.

3) NOT merge - rstfinal will contain all IDNumbers from rst1 unless they also appear in rst2. i.e. if rst1 contains 12345, 11111 & 22222 and rst2 has 12345 & 22222, then rstfinal will have only 11111.

can any1 plz set me on the right track as to how to achieve this.

 

[EMP]

See the three queries in the attached sample database.

 

[thewiseguy]

thank you EMP but i think i have not made myself clear...

using AND, OR and NOT on queries is fine but i'm trying to do it with recordsets that have already been based on a bit of sql code.

eg:

strsql1 = "SELECT [IDNumber] FROM [tblACCOUNTS] WHERE [IDNumber]<" & Me.txtIDNumber & ";"

Set rst1 = CurrentDb.Openrecordset(strsql1)

' /// then later on

strsql2 = "SELECT [IDNumber] FROM [tblUSERS] WHERE [IDNumber]<" & Me.txtIDNumber & ";"

Set rst2 = CurrentDb.Openrecordset(strsql2)

so i have rst1 and rst2 and then i want to use AND, OR and NOT on them independently and load into rstfinal.

It would be great if i could load recordsets into the queries you showed but i don't think you can do that (or don't know how) so i thought there might be a way in VBA...

i hope that makes more sense...

 

[EMP]

You can build three SQL statements in code (using the three queries in the sample database as examples) and open each of them in a recordset.