how to get last records out of 4 records per customer?

[mls via AccessMonster.com]

I am having 4 records for each customer with different dates, so how can I
get the last records for this customer. I also want to see 4 other fields in
my results along with the customer no and date.
I tried to use last in my select query for customer, but what should I give
to other fields because I don't wan tot perform any summary function on them.

Thanks.

 

[mls via AccessMonster.com]

Infact I want to get all the details of a customer for his latest order
number. so for example a customer has 4 orders then I want to see all his
details for order_num4 irrespective of dates. How can I get them?

 

[Allen Browne]

Time to learn about subqueries.
http://allenbrowne.com/subquery-01.html

This kind of thing:

SELECT Table1.*
FROM Table1
WHERE ID =
(SELECT TOP 1 ID
FROM Table1 AS Dupe
WHERE Dupe.CustomerID = Table1.CustomerID
ORDER BY TheDate DESC, ID DESC);

 

[mls via AccessMonster.com]

Some how my query is failing at WHERE Dupe.CustomerID = Table1.CustomerID
it says : "Invalid use of '.','!' or '()' in expression. but I don't see any
of those characters.
I run this different ways and every time it is failing there only

Time to learn about subqueries.
http://allenbrowne.com/subquery-01.html

This kind of thing:

SELECT Table1.*
FROM Table1
WHERE ID =
(SELECT TOP 1 ID
FROM Table1 AS Dupe
WHERE Dupe.CustomerID = Table1.CustomerID
ORDER BY TheDate DESC, ID DESC);

I am having 4 records for each customer with different dates, so how can
I get the last records for this customer. I also want to see 4 other

[quoted text clipped - 4 lines]

to other fields because I don't wan tot perform any summary function on
them.

 

[KARL DEWEY]

Copy and paste the SQL from your query for analyses.

--
Build a little, test a little.

Some how my query is failing at WHERE Dupe.CustomerID = Table1.CustomerID
it says : "Invalid use of '.','!' or '()' in expression. but I don't see any
of those characters.
I run this different ways and every time it is failing there only

Time to learn about subqueries.
http://allenbrowne.com/subquery-01.html

This kind of thing:

SELECT Table1.*
FROM Table1
WHERE ID =
(SELECT TOP 1 ID
FROM Table1 AS Dupe
WHERE Dupe.CustomerID = Table1.CustomerID
ORDER BY TheDate DESC, ID DESC);

I am having 4 records for each customer with different dates, so how can
I get the last records for this customer. I also want to see 4 other

[quoted text clipped - 4 lines]

to other fields because I don't wan tot perform any summary function on
them.

--



.

 

[mls via AccessMonster.com]

I am trying to get the latest Test results for each ID.

SELECT SPECIMEN.ID, SPECIMEN.TestNo, SPECIMEN.Run_Date, SPECIMEN.
date_resulted
FROM SPECIMEN
WHERE SPECIMEN.ID IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.TestNo DESC);

Copy and paste the SQL from your query for analyses.

Some how my query is failing at WHERE Dupe.CustomerID = Table1.CustomerID
it says : "Invalid use of '.','!' or '()' in expression. but I don't see any

[quoted text clipped - 19 lines]

 

[John Spencer]

Take a look at your WHERE clause. You are attempting to match Id to TestNo.

SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.TestNo IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.TestNo DESC);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to get the latest Test results for each ID.

SELECT SPECIMEN.ID, SPECIMEN.TestNo, SPECIMEN.Run_Date, SPECIMEN.
date_resulted
FROM SPECIMEN
WHERE SPECIMEN.ID IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.TestNo DESC);

Copy and paste the SQL from your query for analyses.

Some how my query is failing at WHERE Dupe.CustomerID = Table1.CustomerID
it says : "Invalid use of '.','!' or '()' in expression. but I don't see any

[quoted text clipped - 19 lines]

to other fields because I don't wan tot perform any summary function on
them.

 

[mls via AccessMonster.com]

Even if I give ID it is not working, showing the same error

SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.TestNo IN (SELECT TOP 1 ID FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.TestNo DESC);

Take a look at your WHERE clause. You are attempting to match Id to TestNo.

SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.TestNo IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.TestNo DESC);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to get the latest Test results for each ID.

[quoted text clipped - 12 lines]

 

[John Spencer]

Also, I missed a SPACE between Specimen. and ID in the sub-query.

SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.TestNo IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN.ID
ORDER BY SPECIMEN.TestNo DESC);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Take a look at your WHERE clause. You are attempting to match Id to
TestNo.

SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.TestNo IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.TestNo DESC);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to get the latest Test results for each ID.

SELECT SPECIMEN.ID, SPECIMEN.TestNo, SPECIMEN.Run_Date, SPECIMEN.
date_resulted FROM SPECIMEN
WHERE SPECIMEN.ID IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID ORDER BY SPECIMEN.TestNo DESC);

Copy and paste the SQL from your query for analyses.

Some how my query is failing at WHERE Dupe.CustomerID =
Table1.CustomerID it says : "Invalid use of '.','!' or '()' in
expression. but I don't see any
[quoted text clipped - 19 lines]
to other fields because I don't wan tot perform any summary
function on them.

 

[mls via AccessMonster.com]

After removing the space that error is gone now.
But the query is resulting all the rows not just the top 1.

Also, I missed a SPACE between Specimen. and ID in the sub-query.

SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.TestNo IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN.ID
ORDER BY SPECIMEN.TestNo DESC);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Take a look at your WHERE clause. You are attempting to match Id to
TestNo.

[quoted text clipped - 27 lines]

 

[John Spencer]

Obviously we are not communicating well.

Post four rows of your data and indicate which row you want returned out of
the four.

Going back to your first post you said
"... 4 records for each customer with different dates, so how can I
get the last records for this customer ..."

That indicates to me that you want the record with the latest date (Run_Date
or Date_Resulted?) for each customer (what field identifies the customer?).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

After removing the space that error is gone now.
But the query is resulting all the rows not just the top 1.

Also, I missed a SPACE between Specimen. and ID in the sub-query.

SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.TestNo IN (SELECT TOP 1 TestNo FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN.ID
ORDER BY SPECIMEN.TestNo DESC);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Take a look at your WHERE clause. You are attempting to match Id to
TestNo.

[quoted text clipped - 27 lines]

to other fields because I don't wan tot perform any summary
function on them.

 

[mls via AccessMonster.com]

I want to retrieve Test 1 or Test 2 or test 3 which ever is latest record for
that ID.

ID TestNo Run_Date
2572 Test 1 19-Nov-09
2572 Test 2 20-Nov-09
2976 Test 1 10-Nov-09
2976 Test 2 17-Nov-09
2978 Test 1 11-Nov-09
2981 Test 1 11-Nov-09
2985 Test 1 11-Nov-09
2986 Test 3
2986 Test 1 11-Nov-09
2986 Test 2 17-Nov-09
2996 Test 3 11-Nov-09

Obviously we are not communicating well.

Post four rows of your data and indicate which row you want returned out of
the four.

Going back to your first post you said
"... 4 records for each customer with different dates, so how can I
get the last records for this customer ..."

That indicates to me that you want the record with the latest date (Run_Date
or Date_Resulted?) for each customer (what field identifies the customer?).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

After removing the space that error is gone now.
But the query is resulting all the rows not just the top 1.

[quoted text clipped - 19 lines]

 

[KARL DEWEY]

Try this --
SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.Run_Date = (SELECT TOP 1 Run_Date FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.Run_Date DESC);

--
Build a little, test a little.

I want to retrieve Test 1 or Test 2 or test 3 which ever is latest record for
that ID.

ID TestNo Run_Date
2572 Test 1 19-Nov-09
2572 Test 2 20-Nov-09
2976 Test 1 10-Nov-09
2976 Test 2 17-Nov-09
2978 Test 1 11-Nov-09
2981 Test 1 11-Nov-09
2985 Test 1 11-Nov-09
2986 Test 3
2986 Test 1 11-Nov-09
2986 Test 2 17-Nov-09
2996 Test 3 11-Nov-09

Obviously we are not communicating well.

Post four rows of your data and indicate which row you want returned out of
the four.

Going back to your first post you said
"... 4 records for each customer with different dates, so how can I
get the last records for this customer ..."

That indicates to me that you want the record with the latest date (Run_Date
or Date_Resulted?) for each customer (what field identifies the customer?).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

After removing the space that error is gone now.
But the query is resulting all the rows not just the top 1.

[quoted text clipped - 19 lines]

to other fields because I don't wan tot perform any summary
function on them.

--



.

 

[mls via AccessMonster.com]

run_date is also not working.

Try this --
SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.Run_Date = (SELECT TOP 1 Run_Date FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.Run_Date DESC);

I want to retrieve Test 1 or Test 2 or test 3 which ever is latest record for
that ID.

[quoted text clipped - 34 lines]

 

[John Spencer]

One method (can update records if needed):
SELECT ID, TestNo, Run_Date
FROM SPECIMEN
WHERE Run_Date IN
(SELECT TOP 1 Run_Date
FROM SPECIMEN as Temp
WHERE Temp.ID = SPECIMEN.ID
ORDER BY Temp.Run_Date DESC)

This method may be faster but you may not be able to update the records. Also
this query cannot be created in query design view, but must use the SQL view
to create the query.

SELECT SPECIMEN.ID, SPECIMEN.TestNo, SPECIMEN.Run_Date
FROM SPECIMEN INNER JOIN
( SELECT ID, Max(Run_Date) as Latest
FROM SPECIMEN
GROUP BY ID) as Temp
ON Temp.ID = SPECIMEN.ID
AND Temp.Latest = SPECIMEN.Run_Date


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I want to retrieve Test 1 or Test 2 or test 3 which ever is latest record for
that ID.

ID TestNo Run_Date
2572 Test 1 19-Nov-09
2572 Test 2 20-Nov-09
2976 Test 1 10-Nov-09
2976 Test 2 17-Nov-09
2978 Test 1 11-Nov-09
2981 Test 1 11-Nov-09
2985 Test 1 11-Nov-09
2986 Test 3
2986 Test 1 11-Nov-09
2986 Test 2 17-Nov-09
2996 Test 3 11-Nov-09

Obviously we are not communicating well.

Post four rows of your data and indicate which row you want returned out of
the four.

Going back to your first post you said
"... 4 records for each customer with different dates, so how can I
get the last records for this customer ..."

That indicates to me that you want the record with the latest date (Run_Date
or Date_Resulted?) for each customer (what field identifies the customer?).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

After removing the space that error is gone now.
But the query is resulting all the rows not just the top 1.

[quoted text clipped - 19 lines]

to other fields because I don't wan tot perform any summary
function on them.

 

[John Spencer]

"Not working" is not a good diagnostic statement for getting help.

Are you getting the wrong results, a syntax error, or is something else
happening? If the results are wrong, can you describe how they are wrong.

For instance, you might try changing the where statement to
Where Specimen.Run_Date IN (Select TOP 1 ...)

Or change the where to the following

WHERE Specimen.RunDate = (SELECT Max(Run_Date) From SPECIMEN as Dupe WHERE
Dupe.ID = SPECIMEN.ID)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

run_date is also not working.

Try this --
SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.Run_Date = (SELECT TOP 1 Run_Date FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.Run_Date DESC);

I want to retrieve Test 1 or Test 2 or test 3 which ever is latest record for
that ID.

[quoted text clipped - 34 lines]

to other fields because I don't wan tot perform any summary
function on them.

 

[KARL DEWEY]

Post some sample data.

--
Build a little, test a little.

run_date is also not working.

Try this --
SELECT SPECIMEN.ID, SPECIMEN.TestNo
, SPECIMEN.Run_Date
, SPECIMEN.date_resulted
FROM SPECIMEN
WHERE SPECIMEN.Run_Date = (SELECT TOP 1 Run_Date FROM SPECIMEN AS Dupe
WHERE Dupe.ID = SPECIMEN. ID
ORDER BY SPECIMEN.Run_Date DESC);

I want to retrieve Test 1 or Test 2 or test 3 which ever is latest record for
that ID.

[quoted text clipped - 34 lines]

to other fields because I don't wan tot perform any summary
function on them.

--



.

 

[mls via AccessMonster.com]

Thanks every one I used John's query to get top TestNo and it works fine.

One method (can update records if needed):
SELECT ID, TestNo, Run_Date
FROM SPECIMEN
WHERE Run_Date IN
(SELECT TOP 1 Run_Date
FROM SPECIMEN as Temp
WHERE Temp.ID = SPECIMEN.ID
ORDER BY Temp.Run_Date DESC)

This method may be faster but you may not be able to update the records. Also
this query cannot be created in query design view, but must use the SQL view
to create the query.

SELECT SPECIMEN.ID, SPECIMEN.TestNo, SPECIMEN.Run_Date
FROM SPECIMEN INNER JOIN
( SELECT ID, Max(Run_Date) as Latest
FROM SPECIMEN
GROUP BY ID) as Temp
ON Temp.ID = SPECIMEN.ID
AND Temp.Latest = SPECIMEN.Run_Date

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I want to retrieve Test 1 or Test 2 or test 3 which ever is latest record for
that ID.

[quoted text clipped - 34 lines]