How to write join select query in MSAccess?

Question

Say I have these tables with field names underneath.

DailyLeaveLedger
dldEmployeeID
dldLeaveDate

InvoiceHeader
invEmployeeID
invWeekEnding
InvNumberWeeksCovered

So I want a query selects leave days where an invoice covers that leave ie invWeekEnding 02 Oct and InvNumberWeeksCovered = 1 then any leave in the range 26 Sep to 02 Oct is ok. Also you have to have EmployeeiD fields match.

In MSAccess 2003 please?

Answer 1

Try this

SELECT InvoiceHeader.invEmployeeID, 
       InvoiceHeader.invWeekEnding, 
       DateAdd("d",-[InvNumberWeeksCovered]*7,[invWeekEnding]) AS StartDate,
       DailyLeaveLedger.dldLeaveDate
  FROM InvoiceHeader 
       INNER JOIN DailyLeaveLedger 
          ON InvoiceHeader.invEmployeeID = DailyLeaveLedger.dldEmployeeID
WHERE (((DailyLeaveLedger.dldLeaveDate) 
         Between [invWeekEnding] 
         And DateAdd("d",-[InvNumberWeeksCovered]*7,[invWeekEnding])));

What I did was add -7 * InvNumberWeeksCovered to the invdate, and then check for dates between these.

Hope that helps.