<> data query

[fishtofly]

I have two tables and both have a field called tag_id. The first table
"my2003" has about 5000 records. The second table "monitor2003" is a subset
of table "my2003" and has about 3000 records. I need help building a query
that will give me the records in table "my2003" where the "tag_id" field is
<> to the "tag_id" field in the "monitor2003" table. Also which type of
joining of the two tables is needed?

 

[fishtofly]

I tried using the query wizard to create an unmatch record query but it did
not work. For the field of tag_id in my2003, I used the query wizard and
wrote"[my2003]![tag_id]<>[monitor2003]![tag_id]". It came up with all the
records in the table of my2003. I need the unique tag_id's that are in
my2003 and not in monitor2003.

Any help would be greatly appreciated.

Thanks

 

[John W. Vinson]

I tried using the query wizard to create an unmatch record query but it did
not work. For the field of tag_id in my2003, I used the query wizard and
wrote"[my2003]![tag_id]<>[monitor2003]![tag_id]". It came up with all the
records in the table of my2003. I need the unique tag_id's that are in
my2003 and not in monitor2003.

Then you didn't use the Unmatched Query wizard - that's not what it does!

Each record in [my2003] is, in fact, unequal to *some* record in [monitor2003]
- in fact, it's probably unequal to all or all but one of them.

To roll your own query, follow these steps:

1. Create a new Query adding both tables, joined by tag_id. (This finds just
what you DON'T want, the records which do match. But watch...)
2. Select the Join line and view its properties; select option 2 (or maybe 3)
- "Show all records in my2003 and matching records in monitor2003". (This is
even worse, it shows everything in my2003, matched or not. Patience...!)
3. Select ONLY the tag_id field from Monitor2003 into the grid, along with all
the fields you want to see from My2003. Put a criterion of

IS NULL

under Monitor2003.tag_id.

Open the query and you'll see just those records which DON'T match.

John W. Vinson [MVP]

 

[Marco]

Hi. Well I use the Unmatch querie wizard and it workd for me.

You have to tell to the wizard witch foeld he has to compare.
Marco

I tried using the query wizard to create an unmatch record query but it did
not work. For the field of tag_id in my2003, I used the query wizard and
wrote"[my2003]![tag_id]<>[monitor2003]![tag_id]". It came up with all the
records in the table of my2003. I need the unique tag_id's that are in
my2003 and not in monitor2003.

Any help would be greatly appreciated.

Thanks

Use the query wizard to create an unmatch record query that will list all the
records in my2003 but are not listed in monitor2003

 

[fishtofly]

Thanks John,

It worked. This is going to help a lot.

Cheers!

I tried using the query wizard to create an unmatch record query but it did
not work. For the field of tag_id in my2003, I used the query wizard and
wrote"[my2003]![tag_id]<>[monitor2003]![tag_id]". It came up with all the
records in the table of my2003. I need the unique tag_id's that are in
my2003 and not in monitor2003.

Then you didn't use the Unmatched Query wizard - that's not what it does!

Each record in [my2003] is, in fact, unequal to *some* record in [monitor2003]
- in fact, it's probably unequal to all or all but one of them.

To roll your own query, follow these steps:

1. Create a new Query adding both tables, joined by tag_id. (This finds just
what you DON'T want, the records which do match. But watch...)
2. Select the Join line and view its properties; select option 2 (or maybe 3)
- "Show all records in my2003 and matching records in monitor2003". (This is
even worse, it shows everything in my2003, matched or not. Patience...!)
3. Select ONLY the tag_id field from Monitor2003 into the grid, along with all
the fields you want to see from My2003. Put a criterion of

IS NULL

under Monitor2003.tag_id.

Open the query and you'll see just those records which DON'T match.

John W. Vinson [MVP]