Getting to may reocrd back,

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I have information that I need to get from two different
tables, the first table is the sales person. The second
table holds multiple sales information for each sales
person. I only want to bring one record back for each
sales person (the record with the most recent date). the
way I was doing it was trying all the records back for
that sales person from second table. My way:

SELECT firsttbl.name, secondtbl.date, secondtbl.salesinfo
FROM firsttbl, secondtbl
WHERE firsttbl.id = secondtbl.id
ORDER BY firsttbl.name ASC, secondtbl.date DESC

How can i get the most recent record by date once per
sales person? This is for a vb6 app, for a data report.
Does anybody have any Ideas?
 
Try this untested aircode

SELECT firsttbl.name, secondtbl.date, secondtbl.salesinfo
FROM firsttbl, secondtbl
WHERE firsttbl.id = secondtbl.id
AND secondtbl.date IN (SELECT Max(secondtbl.date) FROM
secondtbl WHERE secondtbl.id = firsttbl.id)

It will bring back all records for the last date

Hope This Helps
Gerald Stanley MCSD
 
It works like a charm, thank you very much, I have been
working at this code for 3 to 4 days now. Thanks again
Keep doing what your are doing, helping people like me. ;)
 
Back
Top