Query returns multiple records

[mkelly]

I have a query to run a report. The query is limited by job number "this is also the key". However when I run the report it asks for the job number once that is entered it returns 3600 duplicate records?? How can I get it to only return 1 copy of the record I am looking for??

SELECT [Job Table].[Today's Date], [Job Table].[Date Due], [Job Table].[Time Due], [Job Table].[Customer Name], [Customer Table].[Department Name], [Job Table].[Charge Number], [Customer Table].Phone, [Customer Table].[Mail Code], [Job Table].[Client Number], [Job Table].[Copy Type 1], [Job Table].[Number of Originals 1], [Job Table].[Number of Copies 1], [Number of Originals 1]*[Number of Copies 1] AS [Total Copies 1], [Total Copies 1]*[CPC 1] AS [Total Cost 1], [Job Table].[Machine Type 1], [Machine Type 1].[Copies Per Minute 1], [Total Copies 1]/([Copies Per Minute 1]*0.65) AS [Time 1], [Time 1]/60 AS [Hours 1], [Job Table].[Copy Type 2], [Job Table].[Number of Originals 2], [Job Table].[Number of Copies 2], [Number of Originals 2]*[Number of Copies 2] AS [Total Copies 2], [Total Copies 2]*[CPC 2] AS [Total Cost 2], [Job Table].[Machine Type 2], [Machine Type 2].[Copies Per Minute 2], [Total Copies 2]/([Copies Per Minute 2]*0.65) AS [Time 2], [Time 2]/60 AS [Hours 2], [Job Table].[Bindery Type 1], [Job Table].[Number of Binds 1], [Bindery Type 1].[Bindery Time 1], [Number of Binds 1]*[CPB 1] AS [Bindery Cost 1], [Number of Binds 1]*[Bindery Time 1] AS [Bindery Minutes 1], [Bindery Minutes 1]/60 AS [Bindery Hours 1], [Job Table].[Bindery Type 2], [Job Table].[Number of Binds 2], [Bindery Type 2].[Bindery Time 2], [Number of Binds 2]*[CPB 2] AS [Bindery Cost 2], [Number of Binds 2]*[Bindery Time 2] AS [Bindery Minutes 2], [Bindery Minutes 2]/60 AS [Bindery Hours 2], [Job Table].[Operator Name], [Job Table].[Completed On-Time], [Total Cost 1]+[Total Cost 2]+[Bindery Cost 1]+[Bindery Cost 2] AS [Total Cost 3], [Time 1]+[Time 2]+[Bindery Minutes 1]+[Bindery Minutes 2] AS [Total Minutes], [Hours 1]+[Hours 2]+[Bindery Hours 1]+[Bindery Hours 2] AS [Total Hours], [Job Table].[Rework Reason], [Job Table].[CPC 1], [Job Table].[CPC 2], [Job Table].[CPB 1], [Job Table].[CPB 2], [Job Table].[Job Number]
FROM [Paper Type 1], [Print Sides 1], [Paper Type 2], [Print Sides 2], (((((((([Job Table] INNER JOIN [Customer Table] ON [Job Table].[Customer Name] = [Customer Table].[Customer Name]) INNER JOIN [Copy Type 1] ON [Job Table].[Copy Type 1] = [Copy Type 1].[Copy Type 1]) INNER JOIN [Machine Type 1] ON [Job Table].[Machine Type 1] = [Machine Type 1].[Machine Type 1]) INNER JOIN [Copy Type 2] ON [Job Table].[Copy Type 2] = [Copy Type 2].[Copy Type 2]) INNER JOIN [Machine Type 2] ON [Job Table].[Machine Type 2] = [Machine Type 2].[Machine Type 2]) INNER JOIN [Bindery Type 1] ON [Job Table].[Bindery Type 1] = [Bindery Type 1].[Bindery Type 1]) INNER JOIN [Bindery Type 2] ON [Job Table].[Bindery Type 2] = [Bindery Type 2].[Bindery Type 2]) INNER JOIN [Operator Name] ON [Job Table].[Operator Name] = [Operator Name].[Operator Name]) INNER JOIN [Rework Reason] ON [Job Table].[Rework Reason] = [Rework Reason].[Rework Reason]
WHERE ((([Job Table].[Job Number])=[Enter Job Number]));

 

[ajetrumpet]

interesting avatar...

 

[namliam]

The duplicates are comming from one or more of the following:
1) Your joins are not right
2) You have duplicates in one (or more) of the joined tables

((((((((
[Job Table] 
  INNER JOIN [Customer Table] ON [Job Table].[Customer Name] = [Customer Table].[Customer Name]) 
  INNER JOIN [Copy Type 1]    ON [Job Table].[Copy Type 1] = [Copy Type 1].[Copy Type 1]) 
  INNER JOIN [Machine Type 1] ON [Job Table].[Machine Type 1] = [Machine Type 1].[Machine Type 1]) 
  INNER JOIN [Copy Type 2]    ON [Job Table].[Copy Type 2] = [Copy Type 2].[Copy Type 2]) 
  INNER JOIN [Machine Type 2] ON [Job Table].[Machine Type 2] = [Machine Type 2].[Machine Type 2]) 
  INNER JOIN [Bindery Type 1] ON [Job Table].[Bindery Type 1] = [Bindery Type 1].[Bindery Type 1]) 
  INNER JOIN [Bindery Type 2] ON [Job Table].[Bindery Type 2] = [Bindery Type 2].[Bindery Type 2]) 
  INNER JOIN [Operator Name]  ON [Job Table].[Operator Name] = [Operator Name].[Operator Name]) 
  INNER JOIN [Rework Reason]  ON [Job Table].[Rework Reason] = [Rework Reason].[Rework Reason]
WHERE ((([Job Table].[Job Number])=[Enter Job Number]));

Also ...

Ussually (note ussualy) there are better ways to design things than using Type 1, Type 2 etc...

 

[Rabbie]

Also it is never a good idea to have spaces in Table and Field names.

The use of tables called xxx Type 1 and xxx Type 2 arouses suspicions that the data may not be properly normalised.

Also how is the field [Job Table].[Today's Date] populated. Does it change every day.

What happens when you run your query in the query design grid. How many records in each table? Perhaps you could post a copy of your DB