Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

SQL Query

Access 2010

Ok I am using Access to get data from 375,000 records.  I know I have 23,000 unique people associated with the records.  columns A,B,C,D are all static information about the person.  I also have a date last seen which I want to use the max date.

How do I use the max of the date to find the latest instance of the complete record.  Every time I try to use the max date and include any column F,G,H, I  it gives me all of the records.

I have searched and I can't quite tie the answers to my data.

I have a unique id column (A),  person ID column (B), and a date last seen column (E), I can't figure out how to get the max date (E) and still include the (A) to let me join to the rest of my data.
0
Sean Meyer
Asked:
Sean Meyer
2 Solutions
 
mbizupCommented:
Start with a grouping query:

Select personID, max(datefield) as lastSeen
From yourTable
Group by personID

Save the query.

Create another query joining your people table to that query, linking it on personID and LastSeen.
0
 
Rey Obrero (Capricorn1)Commented:
try this single query


select a.*
from TableName as a
inner join
(select b.[person ID],max(b.[date last seen]) as maxdate
      from tableName as b
      group by b.[person ID]) as c
on a.[person ID]=c.[person ID] and a.[date last seen]=c.[maxDate]


.change tableName with the actual name of table and fields accordingly
0
 
awking00Commented:
select a.*
from yourtable a,
(select personid, max(date) as maxdt
 from yourtable
 group by personid) as b
where a.personid = b.personid
  and a.date = b.maxdt;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now