simple question i think

L

Lebowski

Hi,

i have two tables, that contains data in two columns. I want to
display only these rows from Table A that do not exist in Table B.
Rows are considered equal if data in both columns are equal.

I have an idea how to to it, but its too complex (needs at least 3
queries). I wonder if there's a possibility to do it using only one
query.

Thanks for help
 
L

Lebowski

"find unmatched" query?
Click to expand...

The problem with "Find Unmatched" query is that the data in both
tables is in two columns and this query compares data from only one
column. I need a query that exclude rows if the whole data in the rows
is the same.
 
L

Lebowski

Can you provide more details about the two tables? I will try to help if I
can.
Click to expand...

both TABLE A and TABLE B have columns SUPPLIER, CODE

the problem is that the same supplier can have different codes in the
tables, for example:

in TABLE A there is "Supplier One" with code "125"
and
in TABLE B there is "Supplier One" with code "345"

because the codes are different i should get two entries in my
"Unmatched Query": Supplier One/125 and Supplier One/345

What i want to get i would call "Include ALL records from TABLE A and
only those records from TABLE B where the joined fields ARE NOT equal"
 
S

scubadiver

The simplest idea I have is that you simply subtract one code from the
other. If the difference does not equal zero then exclude the record.

Is "supplier one" the same in both tables? If so why not have one table for
"supplier" and one table for "code" so you have a 1-to-many relationship?

I think you may have created some difficulty for yourself with the design.
 
B

BruceM

Your last sentence describes a query that includes both tables. In query
design view, right click the join line, click Join Properties, and choose
the appropriate join type.

However, I don't think that is quite what you need. I agree that your
structure may be part of the problem. Supplier should be stored in one
table. Codes associated with that supplier should be stored in a related
table. That way you could have any number of codes, and retrieving the list
would be a simple matter.

Having said that, if the only two fields you need are Supplier and Code you
could use SELECT DISTINCTROW instead of SELECT in the query's SQL. In query
design view, click View > Properties, and set Unique Records to Yes.
 
L

Larry Linson

The following works when there is only one record with a given Supplier and
Code in each table... as would be the case if the combination of Supplier
and Code were defined at the Primary Key, or as a Unique Index. Other
configurations may require a different approach.

In the Query Builder add TABLE A and TABLE B to a new Query. Drag and drop
to join the SUPPLIER field, and do the same for the CODE. Click each of the
join lines, choose Join Properties, and choose "All records from Table A and
only those that match from Table B". In the query grid, drag down the
SUPPLIER and CODE fields from each Table. In the first criteria line under
the SUPPLIER and CODE from Table B, enter Is Null (criteria on the same line
are AND conditions). And, if you don't want the empty Fields to display on
the Query, uncheck the Show box on each of these two Fields, also.

Larry Linson
Microsoft Access MVP
 
Top