A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

data query



 
 
Thread Tools Display Modes
  #1  
Old March 28th, 2007, 01:02 AM posted to microsoft.public.access.queries
fishtofly
external usenet poster
 
Posts: 3
Default 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  
Old March 28th, 2007, 03:02 AM posted to microsoft.public.access.queries
fishtofly
external usenet poster
 
Posts: 3
Default 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  
Old March 28th, 2007, 06:28 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old March 28th, 2007, 10:42 AM posted to microsoft.public.access.queries
Marco
external usenet poster
 
Posts: 535
Default 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  
Old March 28th, 2007, 04:54 PM posted to microsoft.public.access.queries
fishtofly
external usenet poster
 
Posts: 3
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.