Join with 2 conditions

[davida37]

Hi there,

using Access 2003.

I am use to using T-SQL (sql server). I am having problems joining 2 tables with 2 conditions in the join. in t-sql the following would work fine:

SELECT
x,y,z
FROM
table1 INNER JOIN table2
on (table1.memberid = table2.memberid AND table1.Date = table2.Date)

the query works without the 2nd condition in the on clause. i.e.

SELECT
x,y,z
FROM
table1 INNER JOIN table2
on (table1.memberid = table2.memberid)


BUT fails with it - or asks for parameter inputs.

Please help.

thanks in advance.
david

 

[jrbarnett]

This is because Date is a reserved word in Access.

Rename the column to something like InvoiceDate or RenewalDate in both columns, change the SQL then it will work fine.

John

 

[JerryKlmns]

Or maybe

SELECT
x,y,z
FROM table1 As T1 INNER JOIN table2 As T2 On
(T1.memberid = T2.memberid) AND
(T1.[Date] = T2.[Date])

 

[davida37]

thanks for your suggestions. Was actually a number of issues in the end - but now works.

thanks

FROM SalaryReport INNER JOIN EffectiveDate_Max ON (SalaryReport.EmployeeID=EffectiveDate_Max.EmployeeID) AND (SalaryReport.EffectiveDate=CDATE(EffectiveDate_Max.EffectiveDate));