help with join please

(first off sorry if in wrong forum -- if so please let me know which is
best)
Its been a long time since I have done this and need some help! Basically I
have two tables that I want to join using a query that works in both SQL 7
and Jet. Here is the select statement I am using:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime, [Employee] Text;
SELECT L.WorkDate, L.Pay, L.Tips, A.AdvAmount
FROM tblLabor AS L LEFT JOIN tblAdvances AS A ON (L.WorkDate =
A.AdvanceDate) AND (L.Employee = A.Employee)
WHERE L.WorkDate BETWEEN [StartDate] AND [EndDate] AND L.Employee =
[Employee]
ORDER BY L.WorkDate
This works great except when I have more than one record in tblLabor with
the same date & employee (like a split shift) -- because then a single
records from tblAdvances gets joined to each record in tblLabor with the
same date.
I hope this makes sense and thanks in advance for your help!
DianaWilliams
I'm not sure that understand you. Can you post DDL + smaple data + expected
result?
Perhaps you need to use INNER JOIN instead of LEFT.
"Williams" <DianaValdezW@.prodigy.net.mx> wrote in message
news:OWYWvFHHFHA.2456@.TK2MSFTNGP09.phx.gbl...
> (first off sorry if in wrong forum -- if so please let me know which is
> best)
> Its been a long time since I have done this and need some help! Basically
I
> have two tables that I want to join using a query that works in both SQL 7
> and Jet. Here is the select statement I am using:
> PARAMETERS [StartDate] DateTime, [EndDate] DateTime, [Employee] Text;
> SELECT L.WorkDate, L.Pay, L.Tips, A.AdvAmount
> FROM tblLabor AS L LEFT JOIN tblAdvances AS A ON (L.WorkDate =
> A.AdvanceDate) AND (L.Employee = A.Employee)
> WHERE L.WorkDate BETWEEN [StartDate] AND [EndDate] AND L.Employee =
> [Employee]
> ORDER BY L.WorkDate
> This works great except when I have more than one record in tblLabor with
> the same date & employee (like a split shift) -- because then a single
> records from tblAdvances gets joined to each record in tblLabor with the
> same date.
> I hope this makes sense and thanks in advance for your help!
> Diana
>