Exclude records

[deejay_totoro]

Hello all,

I have a query which references to tables.

Both tables share a common reference number.

What I would like to do show all records that DON'T match!

So in English - it would be:

"Please show all the records that don't match."

How would I do this?

Many thanks!

dj_T

 

[Mile-O]

Use this structure:

<> [Parameter]

 

[deejay_totoro]

<>

Use this structure:

<> [Parameter]

Hello,

Thanks for your reply.

But I'm not so experienced. Im not sure what you mean.

Could you explain a little more? that would be great!

thanks!

dj_T

 

[o1110010]

Use this structure:

<> [Parameter]

He means as an expression, for the Criteria. If you wanted to sort out everything that had the word green in it, you would use <> *green*

Or did we misunderstand you?

 

[deejay_totoro]

He means as an expression, for the Criteria. If you wanted to sort out everything that had the word green in it, you would use <> *green*

Or did we misunderstand you?

Ah - maybe its a little different. The joining field is unique to each record.

I have two tables. One table is a master list. Each record on this table has a unique ID.

Now, the other table contains a similar list but the only thing that links these two tables is the ID field.

So in this case I can't say "if green comes up, then exlcude" because it will change with each record.

What I need to say is "if these two fields match, then don't inlcude the record in the query results."

For example

ID (Table 1)
01
02
03
04
05
06
07
08

ID (Table 2)
01
05

In this case, the only records I would like to see in the query result would be those without IDs: 01 & 05. So the result would be:

ID (Table 1)
02
03
04
06
07
08

I hope this is a little more clear!

Thanks again,

dj_T

 

[o1110010]

The Find Unmatched Query Wizard should help you out. Queries -> New -> Find Unmatched Query Wizard.

Edit: Aw bloody. That's for one table. Going to have to go with VBA it appears.

Double Edit: I was wrong! The description of the Find Unmatched Query Wizard threw me off. I setup the related tables with the related data that you gave for an example and the wizard worked.

 

[o1110010]

Also if you insisted on having the result data dump to a table, just finish the wizard, goto the design view of it, right-click any grey area on the query and goto Query Type (or click Queries on the toolbar), and select Make-Table Query. (Or append if you'd rather append to an existing table.)