Need help with Query with several joins 1

[guitarzan]

I am some difficulty writing a query in Access, and was wondering if I can get some advice. Here is the basic structure:

The tblBillingPeriods table will just contain start/end dates, which for now is:

   id   StartDate    EndDate
   1    01/01/2005   12/31/2005 
   2    01/01/2006   12/31/2006

The tblBillingRates table contains the various employee's rates for the given billing periods:

   id   billingperiodID    empID    rateamount
   1    1                  1	        $50.00
   2    2                  1            $75.00
   3    1                  2           $100.00
   4    2                  2           $125.00

The tblTimeRecords table is the main data table, and contains records of work items done by an employee, the date performed, task performed, customer, hours spent, description, etc.

   id   timrecEmpID   timrecDate   hrs, cust, task, desc, etc
   1    1             06/10/2005
   2    2             06/10/2006

When I query the tblTimeRecords table, I would like to include the correct rate based on where the timrecDate falls into the date range in tlbBillingPeriods. for example:

   id   timrecEmpID   timrecDate   rateamount
   1    1             06/10/2005       $50.00
   2    2             06/10/2006      $125.00

I can't put my finger on how to set up the join to allow this easily, does anyone have any advice on how this could be set up? Or should I be setting these tables up differently? Thanks!

 

[PHV]

you may try something like this:
SELECT T.id, T.timrecEmpID, T.timrecDate, R.rateamount
FROM (tblTimeRecords AS T
INNER JOIN tblBillingRates AS B ON T.timrecEmpID = B.empID)
INNER JOIN tblBillingPeriods AS P ON B.billingperiodID = P.id
WHERE T.timrecDate Between P.StartDate And P.EndDate

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

 

[guitarzan]

My apologies for the delay in responding, PHV... Thank you, that worked great. (I thought it was going to be more complicated than that actually)

I have another question...

My only problem with the way this query works is that if I have time record for an employee on a given date, and I have neglected to add tblBillingRates record for that employee in that time period... the record will not show up in the query. Is there a way to have all records show up, and force the rate to 0 if a match is not found?).

Thanks!

 

[lespaul]

you need to change the INNER JOIN to a LEFT JOIN

SELECT T.id, T.timrecEmpID, T.timrecDate, R.rateamount
FROM (tblTimeRecords AS T
LEFT JOIN tblBillingRates AS B ON T.timrecEmpID = B.empID)
INNER JOIN tblBillingPeriods AS P ON B.billingperiodID = P.id
WHERE T.timrecDate Between P.StartDate And P.EndDate


check out Understanding SQL Joins to see why!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[guitarzan]

Hi lespaul,

Thanks for the reply. I should have posted my updated query, where I'm using two left joins.

SELECT T.id, T.timrecEmpID, T.timrecDate, B.rateamount
FROM (tblTimeRecords AS T 
LEFT JOIN tblBillingRates AS B ON T.timrecEmpID = B.empID) 
LEFT JOIN tblBillingPeriods AS P ON B.billingperiodID = P.id
WHERE T.timrecDate Between [P].[StartDate] And [P].[EndDate]

But still, if I enter a date in T.timrecDate that does not fall between P.StartDate and P.EndDate, the record does not show up.

When I tried your suggestion (with a left join, then an inner join), I got "...contains ambiguous outer joins", which I can't say I understand very well. So I'll check out your link in the meantime