How do I pull only the most current date? 1

[AidanCambel]

I have a database that holds information regarding customers that call in for support. There may be dozens of records for each customer, or 1 record. It all depends on how many times they have called in. But each record is dated.

I want to run a query to find the last time each customer called in for support. I have a list of just customer numbers - how can I tell a query to go into the table and pull the most current call-in date for each of these customer numbers?

Thanks.

 

[zevw]

THe way you would do it is group by the customer number like this you will only get 1 record per employee and then run the Max or Last function for date that will give you the most recent date the customer called in for support.

 

[JoeMiller]

You can use MAX to pull the highest date that a customer called in (which is the same as most recent). In a query pull the customer info you need, add the call table, then on the query toolbar click the Totals button (looks like SUM sign in Excel). Now change "Group By" to "Max" under your date field and you should be all set.

HTH Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

It is a simple aggregate query.

The table:
EntryDate Id DateN
1/1/00 123456 2/1/00
1/1/00 123456 3/1/00
1/1/00 123456 4/1/00
1/1/00 123456 5/1/00
1/1/00 123456 6/1/00
1/1/00 123456 7/1/00
4/1/00 456789 8/1/00
5/1/00 456789 9/1/00
6/1/00 456789 10/1/00
7/1/00 456789 11/1/00

The query Results:
MaxOfEntryDate1 Id
1/1/00 123456
7/1/00 456789

The query SQL:
SELECT Max(tbPMS.EntryDate) AS MaxOfEntryDate1, tbPMS.Id
FROM tbPMS
GROUP BY tbPMS.Id;




MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Cagliostro]

Yoou can try also datediff(d,YourDate,now()); John Fill

ivfmd@mail.md