Faulty Relations???

J

Jeff C

I am fighting this query which keeps giving me duplicate results despite my
joins. I am guessing it may be the overall design but am trying to work with
what I have. Could someone point me towards the right direction?



SELECT qry_01Inside_Calculate.Badge AS Emp_ID, qry_01Inside_Calculate.Name,
Shifts_Bid.Shift_ID, qry_01Inside_Calculate.[SS#] AS SSN,
Shifts_Bid.Day_Worked, Shifts_Bid.Shift_Worked, Shifts_Bid.Hours_Worked,
Shifts_Bid.Bid_Rate, qry_01Inside_Calculate.Productive_Rate AS Base_Rate,
Shifts_Bid.Processed, qry_01Inside_Calculate.OTR
FROM Shifts_Bid INNER JOIN qry_01Inside_Calculate ON Shifts_Bid.Emp_ID =
qry_01Inside_Calculate.Badge
WHERE (((Shifts_Bid.Day_Worked) Between
[Forms]![frm_RptPayPeriod]![txt_BeginPay] And
[Forms]![frm_RptPayPeriod]![txt_EndPay]) AND ((Shifts_Bid.Processed)=No))
ORDER BY Shifts_Bid.Day_Worked DESC;
 
D

Douglas J. Steele

What's qry_01Inside_Calculate, and what are the details of all of the tables
involved (especially the PK of each)?
 
J

John Vinson

I am fighting this query which keeps giving me duplicate results despite my
joins. I am guessing it may be the overall design but am trying to work with
what I have. Could someone point me towards the right direction?



SELECT qry_01Inside_Calculate.Badge AS Emp_ID, qry_01Inside_Calculate.Name,
Shifts_Bid.Shift_ID, qry_01Inside_Calculate.[SS#] AS SSN,
Shifts_Bid.Day_Worked, Shifts_Bid.Shift_Worked, Shifts_Bid.Hours_Worked,
Shifts_Bid.Bid_Rate, qry_01Inside_Calculate.Productive_Rate AS Base_Rate,
Shifts_Bid.Processed, qry_01Inside_Calculate.OTR
FROM Shifts_Bid INNER JOIN qry_01Inside_Calculate ON Shifts_Bid.Emp_ID =
qry_01Inside_Calculate.Badge
WHERE (((Shifts_Bid.Day_Worked) Between
[Forms]![frm_RptPayPeriod]![txt_BeginPay] And
[Forms]![frm_RptPayPeriod]![txt_EndPay]) AND ((Shifts_Bid.Processed)=No))
ORDER BY Shifts_Bid.Day_Worked DESC;
Click to expand...

If qry_01InsideCalculate has multiple records for a given value of
Badge, then you'll see multiple copies of the data in Shifts_Bid -
that's how queries work. Or, vice versa - if each record in the query
is related to several records in Shifts_Bid, you'll see duplicates.

What is in fact the relationship between the two?

What are you seeing duplicated?

If you pull up the records for that person's Emp_ID in
qry_01Inside_Calculate how many records do you see? How many records
are there in Shifts_Bid for that Emp_ID?

John W. Vinson[MVP]
 
Top