Solved

SQL Query

Posted on 2013-11-14
3
330 Views
Last Modified: 2013-12-12
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
Comment
Question by:Sean Meyer
3 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 350 total points
Comment Utility
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 150 total points
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now