Query making more records

[grgimpy]

SELECT DISTINCTROW [RRCC2 Production Log].[RRCC2-Date/Time], [RRCC2 Production Log].[Part Number], [RRCC2 Production Log].[Discrete Job], [RRCC2 Production Log].[RRCC2-Operator], [RRCC2 PM Log].[RRCC2-Etch Rate]
FROM [RRCC2 PM Log] INNER JOIN [RRCC2 Production Log] ON [RRCC2 PM Log].[Discrete Job] = [RRCC2 Production Log].[Discrete Job]
ORDER BY [RRCC2 Production Log].[RRCC2-Date/Time] DESC;

I have two tables ([RRCC2 Production Log],[RRCC2 PM Log]) with the similar field [Discrete Job]. I want to display all the records in the table [RRCC2 Production Log] and only the records that have the same [Discrete Job] in [RRCC2 PM Log]. Not all the records in [RRCC2 PM Log] have [Discrete Job] values. This is a one to one relationship.

I have about 2700 records in the [RRCC2 Production Log] table, but when I create this query, it turns out 5700 records. It makes up new records, and I can't figure out how to stop that. The field [Discrete Job] should only have one record each, yet multiple records are created based on the other field's values.

Any help would be greatly appreciated.

 

[Remou]

Are there any duplicates of Discrete Job in either of the two tables?

 

[grgimpy]

Are there any duplicates of Discrete Job in either of the two tables?

there shouldn't be, maybe just a few. Right now I am getting the same [RRCC2-Date/Time], [Discrete Job], and [RRCC2-Operator] fields (which are all correct records) to replicate with different [RRCC2-Etch Rate] fields where only one is correct, then 3 or 4 other records are made up based on random [RRCC2-Etch Rate] fields that I think have something to do with matching up with the [RRCC2-Operator] field.

hope that makes some sense

 

[Golom]

To display ALL records from one table together with matching records from another you need an outer (LEFT or RIGHT) join. DISTINCTROW does nothing for you because it operates only when you select fields from some but not all tables involved in the join. You have fields from all tables.

SELECT P.[RRCC2-Date/Time], P.[Part Number], P.[Discrete Job]
     , P.[RRCC2-Operator], R.[RRCC2-Etch Rate]

FROM [RRCC2 Production Log] As P LEFT JOIN [RRCC2 PM Log] As R 
     ON P.[Discrete Job] = R.[Discrete Job]

ORDER BY P.[RRCC2-Date/Time] DESC;

I just inserted some table aliases to make things easier to read.

 

[Remou]

If you have any duplicates, you will get extra records when you join two tables. What is the unique key for each table?

 

[grgimpy]

Just an autonumber field for primary key. I find the extra records coming from Discrete Jobs that are not duplicates also.