joining tables or look up

[SChua]

I have two tables:

1. the first has sales information by item number and date (and other information) for a particular period
2. the second has item number and item description (it is a comprehensive list of all item numbers).

I would like to put the item description into the first table, by matching the item number.

I initially used the query design with the two tables:

1. joined them by item number (outer join including all rows on table 1 and only matching rows in table 2).
2. selected the fields from table 1 and the item description field from table 2.

However, when I do this, the query returns duplicate records, and the number of records more than that of the original Table 1. Why is this so?
Am I doing the join correctly? or should I be using a dlookup expression or something else??

Thanks.

 

[CJ_London]

Noting wrong with what you are doing in principle.

The reason for duplicates is probably because you have duplicate entries in the item table.

Not sure why you need an outer join - I would expect a left join between table1 and table2

The other thing to try is DISTINCT - either modify your SQL from SELECT to SELECT DISTINCT or do the same thing in the query design view (right click and select properties, clcik on the top pane and change Unique Records from no to yes)

 

[SChua]

Thanks - that helped. There were duplicates in the item table. And yes, it was a left join I meant, not outer join.
Thanks again.