Max Date 1

[Apollo13a]

Hi, I've checked the forums and have seen this answered but I can't seem to get it working. I'm trying return the Max Date. Here's my SQL

SELECT Max(tblSalary.ChangeDate) AS MaxOfChangeDate, tblSalary.tblSalaryID, tblRates.Rate
FROM tblRates INNER JOIN tblSalary ON tblRates.tblRateID = tblSalary.tblRateID
GROUP BY tblSalary.tblSalaryID, tblRates.Rate;

Here are my records:

MaxOfChangeDate tblSalaryID Rate
10/4/2004 29 GF
10/4/2004 30 AGF
10/4/2004 31 F
10/4/2004 32 JW
10/4/2005 34 JW
10/5/2005 35 GF

I want to return only the latest date. So I would have a total 4 records.
Thanks, Jim

 

[SkipVought]

Hi,

Remove the tblSalaryID, then you'll get 4.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[Apollo13a]

Wow, did I overthink that or what??
Thanks Skip

 

[Apollo13a]

Hello, my tblSalary has a field that shows the actual salary ($) for each rate. When I add it to the query I get the 6 fields again. Any ideas on how I can show just the latest rates including the salary field?
Here's my latest SQL

SELECT Max(tblSalary.ChangeDate) AS MaxOfChangeDate, tblRates.Rate, tblSalary.Salary
FROM tblRates INNER JOIN tblSalary ON tblRates.tblRateID = tblSalary.tblRateID
GROUP BY tblRates.Rate, tblSalary.Salary;

thanks, Jim

 

[SkipVought]

Your query states...

return the max date for each rate/salary combination.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[Apollo13a]

ok, is there away to return just the latest salary/rate combination so I can get the most recent salary for each rate/salary combination?
Jim

 

[SkipVought]

That is what you posted.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[Apollo13a]

Gee, Thanks