Solution needed for eliminating multiple returned rows when joining tables

[CJ_London]

I repeat my previous answer

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

[Bulzie]

Try what I suggested, simple 2 queries (if it works ). Don't always have to use VBA, Access query builder is powerful, why not use it. If it works look at the SQL code to learn syntax.

[melvinhagar]

Executed as indicated,.... syntax errors returned again with those changes. The following SQL will run (no errors), but just not returning desired results.


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])))
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'));

[melvinhagar]

To Bulzie: not sure what you mean with Query2 when you say, "Use Query1,....". This surely cannot be this diffiult. If it is, then I'm not too impressed with Access Query. My current version of the SQL is below, which will execute. It just returns the multiple rows because of table 2 (Job & Organization Table).

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])))
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'));

[Bulzie]

Create Query1 and Save it. Run it to make sure it brings back the records like your example, all records <=Date.
Create Query2, Select Query1 as the "table" inside and do what I said for this second query. Run it and see if it works and gives you what you are requesting.

[melvinhagar]

To Bulzie: Yep, that worked for my one test employee. It's still desirable to be able to do it within one query, but at least I may have a workaround. Thanks guy!!!

[melvinhagar]

To Bulzie: I guess I spoke to soon. The solution worked for my one "test" employee, now it's not working for the entire set of data.

[CJ_London]

what happened to the code I provided in post #8 - restated here from your code with corrections

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([Record Effective Date]) FROM [SWP Employee Historical Database - Job & Organization Data] T Where [Workday Empl ID]=[SWP Employee Historical Database - Terminations].[Workday Empl ID] And [Record Effective Date]<= [SWP Employee Historical Database - Terminations].[Term Date])

is all you should need

or using aliasing to make it more readable

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

[melvinhagar]

To Bulzie: I made a minor change to set "Max" on the record effective date and the employee ID and it seemed to work now.

[melvinhagar]

To Bulzie: I spoke to soon.. still getting mult rows fromt the 2nd table... it's not returning the Max row...

[melvinhagar]

To Ajax: The corrected SQL you supplied is running,... but it's been nearly 30 minutes and results aren't back..

[Bulzie]

Are you talking about Query1 results or Query2 are returning multiple records. Query1 does return multiple records for same ID. Query2 is where you group it to get 1 record. Make sure in Query2 the Date field you have Max on is he first field in Query2.

[melvinhagar]

It was Query2 still returning mult rows... I'll recheck it. Currently, the SQL supplied by Ajax is still running.... anxious to see what it will return-- if it ever finishes.

[CJ_London]

To Ajax: The corrected SQL you supplied is running,... but it's been nearly 30 minutes and results aren't back..

you need to have indexed the appropriate fields for a good performance. In this case

[Workday Empl ID] in both tables
and [SWP Employee Historical Database - Job & Organization Data].[Record Effective Date]

[melvinhagar]

Thanks. I will confirm indexes on my tables if/when the query ever finishes. about 45 minutes now. Table 1 has 5,000 rows,... Table 2 has 900,000 rows....