Solution needed for eliminating multiple returned rows when joining tables

[melvinhagar]

I'm needing to join Table 1 (primary table containing one record per employee) to Table 2 (secondary table containing multiple records per employee). I only wish to join 1 row from Table 2 based upon a date field comparison between Table 1and Table 2. Specifically, my join needs to join two records based on the following psuedo SQL:

Join Table 1 record to Table 2 WHERE...
Table 1 Employee ID = Table 2 Employee ID
AND
Table 2 Date Field is <= Table 1 Date Field (need Table 2 row with MAXIMUM Date Field value that satisfies the <= logic)

I've tried several things, but no success. It always returns multiple rows-- one for each occurence in Table 2.

Help appreciated.

[CJ_London]

try something like

SELECT *
FROM table1 inner join table2 on Table1.EmployeeID = Table2.EmployeeID
WHERE Table2.date=(SELECT Max(date) from table2 T where employeeID=Table1.employeeID and date<=table1.date)

[melvinhagar]

Thanks for the tip. All well and good, but unfortunately Acess provides no help with the proper syntax.... the "(", the brackets "[", etc. So, i'm sill no better off. If you have a few minutes and can construct using my details provided below, I would be most appreciative.

Table 1 primary table: SWP Employee Historical Database – Terminations
Table 2 secondary table: SWP Employee Historical Database – Job & Organization Data

Table 1 fields: Workday Empl ID
Term Date

Table 2 fields: Workday Empl ID
Record Effective Date
Empl Name

I need ALL records from Table 1 (primary table) joined with only ONE record from Table 2 where

Table 1.Workday Empl ID = Table 2.Workday Empl ID

AND

Table 2.Record Effective Date <= Table 1.Term Date

[CJ_London]

square brackets are only required if the table/field name has spaces or non alpha numeric characters. Looks like you have spaces and '&'. Either way they should be removed otherwise you will get weird errors - probably the reason for your comment 'Acess provides no help with the proper syntax'

with regards helping, substitute table1 and table2 for the names of your tables and date and employeeID for the name of your date and employee field respectively, using square brackets where required.

[Bulzie]

You can try it with the Query Designer also if you are not as experienced with VBA. Once it is working then look at the SQL to see how the coding works.

Query1: Link 2 tables on EmpID, include all records from Table1 and Table2, on the Date field from Table2, criteria for Table2 Date would be the <= Table1.Date (Run it and see if it is correct)
Query2: Use Query1, select all fields. Use the Totals/GroupBy Button, on Table2Date, select MAX. Make sure Table2.Date it is the first column.

[melvinhagar]

still no go.... can't get passed all of the snytax errors... ending up with something that looks like:

SELECT [SWP Employee Historical Database - Terminations].[Workday Empl ID], [SWP Employee Historical Database - Terminations].[Term Date], [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date], [SWP Employee Historical Database - Job & Organization Data].[Empl Name]
FROM [SWP Employee Historical Database - Terminations] INNER JOIN [SWP Employee Historical Database - Job & Organization Data] ON [SWP Employee Historical Database - Terminations].[Workday Empl ID] = [SWP Employee Historical Database - Job & Organization Data].[Workday Empl ID] WHERE [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]=(SELECT Max([SWP Employee Historical Database - Job & Organization Data].[Record Effective Date FROM [SWP Employee Historical Database - Job & Organization Data] Where [SWP Employee Historical Database - Job & Organization Data].[Workday Empl ID]=[SWP Employee Historical Database - Terminations].[Workday Empl ID] And [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]<= [SWP Employee Historical Database - Terminations].[Term Date]))
GROUP BY [SWP Employee Historical Database - Terminations].[Workday Empl ID], [SWP Employee Historical Database - Terminations].[Term Date], [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date], [SWP Employee Historical Database - Job & Organization Data].[Empl Name]
HAVING ((([SWP Employee Historical Database - Terminations].[Workday Empl ID])='62111566'));

[orange]

You are not doing yourself any favors with the names you have for your tables and fields.
See w3schools SQL for SQL syntax issues.

Here is a reformatting of your SQL that may be helpful"

SELECT [SWP Employee Historical Database - Terminations].[Workday Empl ID]
    ,[SWP Employee Historical Database - Terminations].[Term Date]
    ,[SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]
    ,[SWP Employee Historical Database - Job & Organization Data].[Empl Name]
FROM [SWP Employee Historical Database - Terminations]
INNER JOIN [SWP Employee Historical Database - Job & Organization Data] ON [SWP Employee Historical Database - Terminations].[Workday Empl ID] = [SWP Employee Historical Database - Job & Organization Data].[Workday Empl ID]
WHERE [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date] = (
        SELECT Max([SWP Employee Historical Database - Job & Organization Data].[Record Effective Date FROM [SWP Employee Historical Database - Job & Organization Data] WHERE [SWP Employee Historical Database - Job & Organization Data].[Workday Empl ID] = [SWP Employee Historical Database - Terminations].[Workday Empl ID]
                AND [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date] <= [SWP Employee Historical Database - Terminations].[Term Date])
        )
GROUP BY [SWP Employee Historical Database - Terminations].[Workday Empl ID]
    ,[SWP Employee Historical Database - Terminations].[Term Date]
    ,[SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]
    ,[SWP Employee Historical Database - Job & Organization Data].[Empl Name]
HAVING ((([SWP Employee Historical Database - Terminations].[Workday Empl ID]) = '62111566'));

[CJ_London]

it would help with readability of you had much shorter table names or used aliasing and removed spaces and non alphanumeric characters as previous advised, but looks like you have missed out the bits in red and included the bits in green which must not be there (so remove them)

SELECT [SWP Employee Historical Database - Terminations].[Workday Empl ID], [SWP Employee Historical Database - Terminations].[Term Date], [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date], [SWP Employee Historical Database - Job & Organization Data].[Empl Name]
 FROM [SWP Employee Historical Database - Terminations] INNER JOIN [SWP Employee Historical Database - Job & Organization Data] ON [SWP Employee Historical Database - Terminations].[Workday Empl ID] = [SWP Employee Historical Database - Job & Organization Data].[Workday Empl ID] WHERE [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]=(SELECT Max([SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]) FROM [SWP Employee Historical Database - Job & Organization Data] T Where  [SWP Employee Historical Database - Job & Organization Data].[Workday Empl ID]=[SWP Employee Historical Database - Terminations].[Workday Empl ID] And [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]<= [SWP Employee Historical Database - Terminations].[Term Date]))
 GROUP BY [SWP Employee Historical Database - Terminations].[Workday Empl ID], [SWP Employee Historical Database - Terminations].[Term Date], [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date], [SWP Employee Historical Database - Job & Organization Data].[Empl Name]
 HAVING ((([SWP Employee Historical Database - Terminations].[Workday Empl ID])='62111566'));

Also not sure why you are grouping since you are not summing or counting - use SELECT DISTINCT instead

[CJ_London]

One final thing:

if Workday empl ID is a number, then lose the single quotes from ='62111566'

[melvinhagar]

thanks..... table/fields names not chosen with the aniticpation of having to go into the SQL. I assumed that I would have to in joining my tables... but I was wrong... still getting syntax errors on SQL supplied by Orange... but I'll keep working with it to see if I can get it to execute.

[melvinhagar]

Got the SQL to execute for my test employee (62111566), but it still returned 4 rows instead 1.... I'm wanting just the "Max" row as requested in the SQL, instead it return all 4 rows on Table 2 where the Record Effective Date was <= to my Term Date on Table 1. Geez.... seems I'm back to where I started...

SELECT [SWP Employee Historical Database - Terminations].[Workday Empl ID], [SWP Employee Historical Database - Terminations].[Term Date], [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date], [SWP Employee Historical Database - Job & Organization Data].[Empl Name]
FROM [SWP Employee Historical Database - Terminations] INNER JOIN [SWP Employee Historical Database - Job & Organization Data] ON [SWP Employee Historical Database - Terminations].[Workday Empl ID] = [SWP Employee Historical Database - Job & Organization Data].[Workday Empl ID]
WHERE ((([SWP Employee Historical Database - Terminations].[Workday Empl ID])='62111566') AND (([SWP Employee Historical Database - Job & Organization Data].[Record Effective Date])=(SELECT Max([SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]) FROM [SWP Employee Historical Database - Job & Organization Data] T Where [SWP Employee Historical Database - Job & Organization Data].[Workday Empl ID]=[SWP Employee Historical Database - Terminations].[Workday Empl ID] And [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]<= [SWP Employee Historical Database - Terminations].[Term Date]
GROUP BY [SWP Employee Historical Database - Terminations].[Workday Empl ID], [SWP Employee Historical Database - Terminations].[Term Date], [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date], [SWP Employee Historical Database - Job & Organization Data].[Empl Name])));

[orange]

The SQL I provided was ONLY a reformatting of the SQL you provided.
Perhaps you could do a compact and repair , then zip a copy of your database.

What exactly was returned? What did you expect?

[melvinhagar]

See below: Employee ID and Term Date from Table 1, repeated 4 times for the 4 rows returned from Table 2 showing the Record Effective Dates <= to the Term Date.... I want it to only return Record Effective Date of 07/31/2014 which is the one that is Max, <= Term Date.

[CJ_London]

your code is unbelievably difficult to read - it's giving me a headache.

1. you seem to be missing a bracket before the group by
2. you have left in the bits in green - they should be removed

[melvinhagar]

See below: Employee ID and Term Date from Table 1, repeated 4 times for the 4 rows returned from Table 2 showing the Record Effective Dates <= to the Term Date.... I want it to only return Record Effective Date of 07/31/2014 which is the one that is Max, <= Term Date