Selecting from Multiple Records by Date

G

Guest

I have a table containing repeat records for indiviual items (ie. the
record's ID field is the same and the record's revision date is different).

I need to generate a query that pulls out only the latest revision date
record for each item.

Example:

ID REV

1 1/30
1 2/2
2 1/15
2 1/30
2 2/6
3 1/18

Result
ID REV

1 2/2
2 2/6
3 1/18

Thanks, J4
 
M

Marshall Barton

j4 said:
I have a table containing repeat records for indiviual items (ie. the
record's ID field is the same and the record's revision date is different).

I need to generate a query that pulls out only the latest revision date
record for each item.

Example:

ID REV

1 1/30
1 2/2
2 1/15
2 1/30
2 2/6
3 1/18

Result
ID REV

1 2/2
2 2/6
3 1/18
Click to expand...


Try this kind of query:

SELECT T.ID, T.Rev
FROM table As T
WHERE T.Rev = (SELECT Max(X.Rev)
FROM table As X
WHERE X.ID = T.ID)
 
G

Guest

Marsh,

Thanks for the help, I need some further assistance.

I used what you gave me an adjusted for my database. However I have never
written in SQL.

I have included what the SQL put in as criteria and the whole SQL below.

Though it runs with no error, I am only getting some of the records. And
can't tell why these are being returned.

Could you help some more, thanks.

Criteria in Revision Date Field:

(SELECT MAX([DRAWING ITEMS].[DRAWING REVISION DATE])
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING ID] = [DRAWING ITEMS].[DRAWING ID])

Whole SQL for Query:

SELECT [DRAWING ITEMS].[PHASE ASSOCIATION], [DRAWING ITEMS].[DRAWING ID],
[DRAWING ITEMS].[DRAWING TITLE], [DRAWING ITEMS].[DRAWING REVISION DATE],
[DRAWING ITEMS].[BULLETIN ASSOCIATION], [DRAWING ITEMS].DESCRIPTION, [DRAWING
ITEMS].[DATE RECEIVED], [DRAWING ITEMS].NOTES, [DRAWING ITEMS].[DRAWING
GENERATOR], [DRAWING ITEMS].[SHOP DRAWING], [DRAWING ITEMS].[PROJECT ID],
[DRAWING ITEMS].[PROJECT TITLE]
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING REVISION DATE] = (SELECT MAX([DRAWING
ITEMS].[DRAWING REVISION DATE])
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING ID] = [DRAWING ITEMS].[DRAWING ID])
ORDER BY [DRAWING ITEMS].[PHASE ASSOCIATION], [DRAWING ITEMS].[DRAWING ID],
[DRAWING ITEMS].[DRAWING REVISION DATE];
 
M

Marshall Barton

You must use the table name aliases so the subquery can
figure out whether a field comes from itself or the main
query:

SELECT T.[PHASE ASSOCIATION],T.[DRAWING ID],
T.[DRAWING TITLE],T.[DRAWING REVISION DATE],
T.[BULLETIN ASSOCIATION], T.DESCRIPTION,
T.[DATE RECEIVED], T.NOTES,
T.[DRAWING GENERATOR],T.[SHOP DRAWING],
T.[PROJECT ID], T.[PROJECT TITLE]
FROM [DRAWING ITEMS] As T
WHERE T.[DRAWING REVISION DATE] =
(SELECT MAX(X.[DRAWING REVISION DATE])
FROM [DRAWING ITEMS] As X
WHERE X.[DRAWING ID] = T.[DRAWING ID])
ORDER BY T.[PHASE ASSOCIATION], T.[DRAWING ID],
T.[DRAWING REVISION DATE];
--
Marsh
MVP [MS Access]

I used what you gave me an adjusted for my database. However I have never
written in SQL.

I have included what the SQL put in as criteria and the whole SQL below.

Though it runs with no error, I am only getting some of the records. And
can't tell why these are being returned. [snip]
Whole SQL for Query:

SELECT [DRAWING ITEMS].[PHASE ASSOCIATION], [DRAWING ITEMS].[DRAWING ID],
[DRAWING ITEMS].[DRAWING TITLE], [DRAWING ITEMS].[DRAWING REVISION DATE],
[DRAWING ITEMS].[BULLETIN ASSOCIATION], [DRAWING ITEMS].DESCRIPTION, [DRAWING
ITEMS].[DATE RECEIVED], [DRAWING ITEMS].NOTES, [DRAWING ITEMS].[DRAWING
GENERATOR], [DRAWING ITEMS].[SHOP DRAWING], [DRAWING ITEMS].[PROJECT ID],
[DRAWING ITEMS].[PROJECT TITLE]
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING REVISION DATE] = (SELECT MAX([DRAWING
ITEMS].[DRAWING REVISION DATE])
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING ID] = [DRAWING ITEMS].[DRAWING ID])
ORDER BY [DRAWING ITEMS].[PHASE ASSOCIATION], [DRAWING ITEMS].[DRAWING ID],
[DRAWING ITEMS].[DRAWING REVISION DATE];


Marshall Barton said:
Try this kind of query:

SELECT T.ID, T.Rev
FROM table As T
WHERE T.Rev = (SELECT Max(X.Rev)
FROM table As X
WHERE X.ID = T.ID)
Click to expand...
Click to expand...
 
G

Guest

Thanks, it is working now.

Where is the best source to learn some of these techniques?

J4

Marshall Barton said:
You must use the table name aliases so the subquery can
figure out whether a field comes from itself or the main
query:

SELECT T.[PHASE ASSOCIATION],T.[DRAWING ID],
T.[DRAWING TITLE],T.[DRAWING REVISION DATE],
T.[BULLETIN ASSOCIATION], T.DESCRIPTION,
T.[DATE RECEIVED], T.NOTES,
T.[DRAWING GENERATOR],T.[SHOP DRAWING],
T.[PROJECT ID], T.[PROJECT TITLE]
FROM [DRAWING ITEMS] As T
WHERE T.[DRAWING REVISION DATE] =
(SELECT MAX(X.[DRAWING REVISION DATE])
FROM [DRAWING ITEMS] As X
WHERE X.[DRAWING ID] = T.[DRAWING ID])
ORDER BY T.[PHASE ASSOCIATION], T.[DRAWING ID],
T.[DRAWING REVISION DATE];
--
Marsh
MVP [MS Access]

I used what you gave me an adjusted for my database. However I have never
written in SQL.

I have included what the SQL put in as criteria and the whole SQL below.

Though it runs with no error, I am only getting some of the records. And
can't tell why these are being returned. [snip]
Whole SQL for Query:

SELECT [DRAWING ITEMS].[PHASE ASSOCIATION], [DRAWING ITEMS].[DRAWING ID],
[DRAWING ITEMS].[DRAWING TITLE], [DRAWING ITEMS].[DRAWING REVISION DATE],
[DRAWING ITEMS].[BULLETIN ASSOCIATION], [DRAWING ITEMS].DESCRIPTION, [DRAWING
ITEMS].[DATE RECEIVED], [DRAWING ITEMS].NOTES, [DRAWING ITEMS].[DRAWING
GENERATOR], [DRAWING ITEMS].[SHOP DRAWING], [DRAWING ITEMS].[PROJECT ID],
[DRAWING ITEMS].[PROJECT TITLE]
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING REVISION DATE] = (SELECT MAX([DRAWING
ITEMS].[DRAWING REVISION DATE])
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING ID] = [DRAWING ITEMS].[DRAWING ID])
ORDER BY [DRAWING ITEMS].[PHASE ASSOCIATION], [DRAWING ITEMS].[DRAWING ID],
[DRAWING ITEMS].[DRAWING REVISION DATE];

j4 wrote:
I have a table containing repeat records for indiviual items (ie. the
record's ID field is the same and the record's revision date is different).

I need to generate a query that pulls out only the latest revision date
record for each item.

Example:

ID REV

1 1/30
1 2/2
2 1/15
2 1/30
2 2/6
3 1/18

Result
ID REV

1 2/2
2 2/6
3 1/18
Click to expand...

Marshall Barton said:
Try this kind of query:

SELECT T.ID, T.Rev
FROM table As T
WHERE T.Rev = (SELECT Max(X.Rev)
FROM table As X
WHERE X.ID = T.ID)
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Best? I have no idea. A lot of folks like SQL Queries for
Mere Mortals by John Viescas (Access MVP). I don't have a
copy, but his other books that I do have are excellent.
--
Marsh
MVP [MS Access]

Thanks, it is working now.

Where is the best source to learn some of these techniques?


Marshall Barton said:
You must use the table name aliases so the subquery can
figure out whether a field comes from itself or the main
query:

SELECT T.[PHASE ASSOCIATION],T.[DRAWING ID],
T.[DRAWING TITLE],T.[DRAWING REVISION DATE],
T.[BULLETIN ASSOCIATION], T.DESCRIPTION,
T.[DATE RECEIVED], T.NOTES,
T.[DRAWING GENERATOR],T.[SHOP DRAWING],
T.[PROJECT ID], T.[PROJECT TITLE]
FROM [DRAWING ITEMS] As T
WHERE T.[DRAWING REVISION DATE] =
(SELECT MAX(X.[DRAWING REVISION DATE])
FROM [DRAWING ITEMS] As X
WHERE X.[DRAWING ID] = T.[DRAWING ID])
ORDER BY T.[PHASE ASSOCIATION], T.[DRAWING ID],
T.[DRAWING REVISION DATE];

I used what you gave me an adjusted for my database. However I have never
written in SQL.

I have included what the SQL put in as criteria and the whole SQL below.

Though it runs with no error, I am only getting some of the records. And
can't tell why these are being returned. [snip]
Whole SQL for Query:

SELECT [DRAWING ITEMS].[PHASE ASSOCIATION], [DRAWING ITEMS].[DRAWING ID],
[DRAWING ITEMS].[DRAWING TITLE], [DRAWING ITEMS].[DRAWING REVISION DATE],
[DRAWING ITEMS].[BULLETIN ASSOCIATION], [DRAWING ITEMS].DESCRIPTION, [DRAWING
ITEMS].[DATE RECEIVED], [DRAWING ITEMS].NOTES, [DRAWING ITEMS].[DRAWING
GENERATOR], [DRAWING ITEMS].[SHOP DRAWING], [DRAWING ITEMS].[PROJECT ID],
[DRAWING ITEMS].[PROJECT TITLE]
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING REVISION DATE] = (SELECT MAX([DRAWING
ITEMS].[DRAWING REVISION DATE])
FROM [DRAWING ITEMS]
WHERE [DRAWING ITEMS].[DRAWING ID] = [DRAWING ITEMS].[DRAWING ID])
ORDER BY [DRAWING ITEMS].[PHASE ASSOCIATION], [DRAWING ITEMS].[DRAWING ID],
[DRAWING ITEMS].[DRAWING REVISION DATE];


j4 wrote:
I have a table containing repeat records for indiviual items (ie. the
record's ID field is the same and the record's revision date is different).

I need to generate a query that pulls out only the latest revision date
record for each item.

Example:

ID REV

1 1/30
1 2/2
2 1/15
2 1/30
2 2/6
3 1/18

Result
ID REV

1 2/2
2 2/6
3 1/18


:
Try this kind of query:

SELECT T.ID, T.Rev
FROM table As T
WHERE T.Rev = (SELECT Max(X.Rev)
FROM table As X
WHERE X.ID = T.ID)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter records with "same" names by most current date 5
Subquery not integrating to query to display previous sum of quantities 3
Record Plus record in access query 8
Selecting first record from a sorted query 6
Queiry to return highest value or number in a field 6
largest value of field in query output 5
selecting records from various tables 1
Need some help with SQL 4

Top