Left Join Query Issue - MS Access

[mkc80]

I am trying to join two tables Table1 and Table2 using the query below. I want all the columns (and rows) from Table1 and Column 'DuplicateInIndex_BasedOnDate' from Table2.


The problem is, all the values in column DuplicateInIndex_BasedOnDate are being set to 1 after the left join, instead of on the rows where is a match in both the tables.
Can someone comment on where the problem lies?

The query I am using right now:


SELECT Table1.*, Table2.DuplicateInIndex_BasedOnDate
FROM Table1LEFT JOIN Table2 ON (Table1.

Name

= Table2.

Name

) AND (Table1.

Date-4

= Table2

Date-4

) AND (Table1.

Date-3

= Table2

Date-3

) AND (Table1.

Date-2

= Table2.

Date-2

) AND (Table1.

Date-1

= Table2.

Date-1

) AND (Table1.

CustomerID

= Table2.

CustomerID

) AND (Table1.

Site

= Table2.

Site

);







Table2:

Site	CustomerID	Date-1	Date-2	Date-3	Date-4	Name	DuplicateInIndex_BasedOnDate
00001	201	3/30/2011	4/6/2011	4/3/2011	4/6/2011	Hospital-1	1

Table1:

Site	CustomerID	Date-1	Date-2	Date-3	Date-4	Name
00001	101	5/21/2011	5/28/2011	5/21/2011	5/28/2011	Hospital-5
00001	101	12/12/2011	12/17/2011	12/12/2011	12/17/2011	Hospital-4
00001	101	3/28/2012	4/11/2012	3/28/2012	4/11/2012	Hospital-2
00001	201	3/30/2011	4/6/2011	4/3/2011	4/6/2011	Hospital-1
00001	202	9/5/2011	9/12/2011	9/5/2011	9/12/2011	Hospital-3
00001	202	9/16/2011	9/20/2011	9/16/2011	9/20/2011	Hospital-3
00001	202	9/29/2011	10/4/2011	9/29/2011	10/4/2011	Hospital-3

[June7]

Did you get this solved?

Could you post the actual SQL statement instead?


Advise not to use spaces and special characters/punctuation (underscore is exception) in names. If used, enclose name in []. Have you had problem with other queries using those Date-x fields?