If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
data query
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? |
#2
|
|||
|
|||
data query
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 "Ofer Cohen" wrote: Use the query wizard to create an unmatch record query that will list all the records in my2003 but are not listed in monitor2003 -- Good Luck BS"D "fishtofly" wrote: 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? |
#3
|
|||
|
|||
data query
On Tue, 27 Mar 2007 19:02:06 -0700, fishtofly
wrote: 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] |
#4
|
|||
|
|||
data query
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 "fishtofly" wrote: 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 "Ofer Cohen" wrote: Use the query wizard to create an unmatch record query that will list all the records in my2003 but are not listed in monitor2003 -- Good Luck BS"D "fishtofly" wrote: 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? |
#5
|
|||
|
|||
data query
Thanks John,
It worked. This is going to help a lot. Cheers! "John W. Vinson" wrote: On Tue, 27 Mar 2007 19:02:06 -0700, fishtofly wrote: 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] |
Thread Tools | |
Display Modes | |
|
|