Multiple records not needed in query

[Gustavo]

Unclear as to what I am doing wrong. HELP! Example of what I am doing.

SELECT DISTINCT PCMS_PCMS_CHDOC_DATA.CH_UID AS Num_ID, DimGrpLkup.lkup_trans
FROM PCMS_PCMS_CHDOC_DATA INNER JOIN DimGrpLkup ON PCMS_PCMS_CHDOC_DATA.PRODUCT_ID = DimGrpLkup.lkup_product
WHERE (((PCMS_PCMS_CHDOC_DATA.CREATE_DATE)>=#6/17/2006#) AND ((PCMS_PCMS_CHDOC_DATA.SEQ)=1));

I have two tables "CHDOC_DATA.PRODUCT_ID" and "GpLkup.lkup_product". When I include another column like "Lkup.lkup_trans" from the second table I get multple records. What is up?

 

[PHV]

Is lkup_product the PrimaryKey of DimGrpLkup ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gustavo]

No it is not the primary key. It contains duplicate entries in that column. I tried making it primary but Access does not like that.

 

[PHV]

So, for each record of PCMS_PCMS_CHDOC_DATA you get all the records of DimGrpLkup having lkup_product=PRODUCT_ID

You may find the following of interest:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886