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

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

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?

mls wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

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);

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"mls via AccessMonster.com" u55943@uwe wrote in message
news:a0f9b452946fa@uwe...
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.

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


Allen Browne wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

Copy and paste the SQL from your query for analyses.

--
Build a little, test a little.


"mls via AccessMonster.com" wrote:

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


Allen Browne wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

.

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);




KARL DEWEY wrote:
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.

--
Message posted via http://www.accessmonster.com

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

mls via AccessMonster.com wrote:
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);




KARL DEWEY wrote:
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.

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);



John Spencer wrote:
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]
to other fields because I don't wan tot perform any summary function on
them.

--
Message posted via http://www.accessmonster.com

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

John Spencer wrote:
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

mls via AccessMonster.com wrote:
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);




KARL DEWEY wrote:
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.

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


John Spencer wrote:
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.

--
Message posted via http://www.accessmonster.com