Finding unique records in a query

[RSteph]

I'm trying to move information from an old database, to a new database I'm
creating. I've got a query with group memberships (goupNo, ContactId,
datejoin) for the desired groups - Query1, I've got another query that pulls
information from the old data for the same 3 items for the same set of groups
- Query2. I believe that some of the records in the old database have already
been added to the new database, so I'm looking for a way to find all those
records that are in the old database that are NOT in the new database.

Is there an easy way to create a third query that will give me all the rows
(groupNo, contactID, dateJoin) when the GroupNo/ContactId in Query2 (old
data) does not exist in Query1 (new data)?

Sort of use one query to remove items from another... I know you can use a
WHERE [ColumnName] NOT IN (Query string), but is there a way to have that
check 2 columns at the same time? Would something like this work:

WHERE GroupNo,contactID NOT IN (Query2)

 

[Golfinray]

Try the unmatched query wizard. Open query and select it, load the tables and
fields you want and run.

 

[RSteph]

I tried that, and it looks like it would work in theory. Unfortunantly I need
to check based on two field (GroupNo, and ContactID), and it only lets me
pick one. So if anyone else is listed in a group, it removes all those group
listings, and if an individual is in another group I'm not checking for right
now, then again it will remove all the listings for that individual. I need a
way to remove only those where those two fields match (only those where the
person is already listed in the group).

Thank you for the thought though, I've never used the kind of query before.

Try the unmatched query wizard. Open query and select it, load the tables and
fields you want and run.

I'm trying to move information from an old database, to a new database I'm
creating. I've got a query with group memberships (goupNo, ContactId,
datejoin) for the desired groups - Query1, I've got another query that pulls
information from the old data for the same 3 items for the same set of groups
- Query2. I believe that some of the records in the old database have already
been added to the new database, so I'm looking for a way to find all those
records that are in the old database that are NOT in the new database.

Is there an easy way to create a third query that will give me all the rows
(groupNo, contactID, dateJoin) when the GroupNo/ContactId in Query2 (old
data) does not exist in Query1 (new data)?

Sort of use one query to remove items from another... I know you can use a
WHERE [ColumnName] NOT IN (Query string), but is there a way to have that
check 2 columns at the same time? Would something like this work:

WHERE GroupNo,contactID NOT IN (Query2)

 

[KARL DEWEY]

Unfortunantly I need to check based on two field (GroupNo, and ContactID),
and it only lets me pick one.
After using the wizard open the query in design view and add the other field.
--
KARL DEWEY
Build a little - Test a little

I tried that, and it looks like it would work in theory. Unfortunantly I need
to check based on two field (GroupNo, and ContactID), and it only lets me
pick one. So if anyone else is listed in a group, it removes all those group
listings, and if an individual is in another group I'm not checking for right
now, then again it will remove all the listings for that individual. I need a
way to remove only those where those two fields match (only those where the
person is already listed in the group).

Thank you for the thought though, I've never used the kind of query before.

Try the unmatched query wizard. Open query and select it, load the tables and
fields you want and run.

I'm trying to move information from an old database, to a new database I'm
creating. I've got a query with group memberships (goupNo, ContactId,
datejoin) for the desired groups - Query1, I've got another query that pulls
information from the old data for the same 3 items for the same set of groups
- Query2. I believe that some of the records in the old database have already
been added to the new database, so I'm looking for a way to find all those
records that are in the old database that are NOT in the new database.

Is there an easy way to create a third query that will give me all the rows
(groupNo, contactID, dateJoin) when the GroupNo/ContactId in Query2 (old
data) does not exist in Query1 (new data)?

Sort of use one query to remove items from another... I know you can use a
WHERE [ColumnName] NOT IN (Query string), but is there a way to have that
check 2 columns at the same time? Would something like this work:

WHERE GroupNo,contactID NOT IN (Query2)