Selecting last date for every record from 2nd table

[TriFith]

Hello everybody,
May I ask you for help with sql query what I can´t even name?

I will try to describe my issue...
I have two tables (tblCustomers and tblPMs). TblPMs contains foreign key to tblCustomers and it contains multiple records for every customer.
My goal is to create query what will pick the last record (regarding the EffectiveDay column in tblPMs) for every customer stored in tblCustomers.

Attached is the stucture of tables. I believe there should be some simple way how to do that, I just can´t find the right way

Thanks for advice
TriFith

[JamesDeckert]

sort the query by EffectiveDay descending. Then do a top 1 (ribbon has design>query setup>'return', enter 1)

[TriFith]

sort the query by EffectiveDay descending. Then do a top 1 (ribbon has design>query setup>'return', enter 1)

Hi,

thanks for your reply... This will gime me only one record with last EffectiveDate and customer related to this record. That is pretty primitive query.
My issue is to get records for all customers with the last EffectiveDate.
I´ve heard there is a possibility to set sub query as a counted field, but it means there will be too many sql querry running and it will take extremly long to get results...

[JamesDeckert]

What about clicking the totals button then group by ID.
Seems like that would return the top 1 for each ID.

[TriFith]

What about clicking the totals button then group by ID.
Seems like that would return the top 1 for each ID.

Can you write the sql code please?
Firstly I think directive SELECT TOP xx is performed before GROUP directive, so I still have only 1 record for this query.
Also If I will use aggregate function, I have to use it on all fields. I can´t use GROUP BY only for one field...

I have tried this code and it will give me only one record as a result.

SELECT TOP 1
   tblCustomers.Customer,
   tblPMs.EffectiveDate,
   tblCustomers.Interval,
   tblCustomers.Remarks
FROM
   tblCustomers INNER JOIN tblPMs ON tblCustomers.ID = tblPMs.CustomerID
GROUP BY
   tblCustomers.Customer,
   tblPMs.EffectiveDate,
   tblCustomers.Interval,
   tblCustomers.Remarks
ORDER BY
   tblPMs.EffectiveDate DESC;

[InsuranceGuy]

This was a fun logic puzzle when I first encountered it. I like the two query solution because it's so easy to remember.

Query1:

Select CustomerID, max(EffectiveDate) MaxEffectiveDate from tblPMs

Query2:

Select tblCustomers.*, tblPMs.* from 
tblCustomers, tblPMs, Query1
where
tblCustomers.ID=tblPMs.CustomerID
and tblCustomers.ID = Query1.CustomerID
and tblPMs.EffectiveDate = Query1.MaxEffectiveDate

[TriFith]

This was a fun logic puzzle when I first encountered it. I like the two query solution because it's so easy to remember.

Query1:

Select CustomerID, max(EffectiveDate) MaxEffectiveDate from tblPMs

Query2:

Select tblCustomers.*, tblPMs.* from 
tblCustomers, tblPMs, Query1
where
tblCustomers.ID=tblPMs.CustomerID
and tblCustomers.ID = Query1.CustomerID
and tblPMs.EffectiveDate = Query1.MaxEffectiveDate

Unfortunatelly Query1 can´t work
CustomerID is not aggregated by some function

But this query guided me to the right solution... It´s ridiculously simple :-D :-D
Following query will pick max EffectiveDate for every CustomerID.

SELECT tblPMs.CustomerID, Max(tblPMs.EffectiveDate) AS MaxOfEffectiveDateFROM tblPMs
GROUP BY tblPMs.CustomerID;

[InsuranceGuy]

But this query guided me to the right solution... It´s ridiculously simple :-D :-D
Following query will pick max EffectiveDate for every CustomerID.

SELECT tblPMs.CustomerID, Max(tblPMs.EffectiveDate) AS MaxOfEffectiveDateFROM tblPMs
GROUP BY tblPMs.CustomerID;

You got it! Sorry for the miss on my part.