total query with last date

[zionsaal]

I have a table with 5 fields
ID PersonID Date Amount and Type

I want a query with the last record of each person based on date and I
want the results like this

PersonID LastOfDate Amount Type

How can i do that
thanks

 

[John W. Vinson]

I have a table with 5 fields
ID PersonID Date Amount and Type

I want a query with the last record of each person based on date and I
want the results like this

PersonID LastOfDate Amount Type

How can i do that
thanks

A Subquery will do this:

SELECT PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID);

John W. Vinson [MVP]

 

[zionsaal]

I have a table with 5 fields
ID PersonID Date Amount and Type

I want a query with the last record of each person based on date and I
want the results like this

PersonID LastOfDate Amount Type

How can i do that
thanks

A Subquery will do this:

SELECT PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID);

John W. Vinson [MVP]

thank you john!

 

[zionsaal]

I have a table with 5 fields
ID PersonID Date Amount and Type

I want a query with the last record of each person based on date and I
want the results like this

PersonID LastOfDate Amount Type

How can i do that
thanks

A Subquery will do this:

SELECT PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID);

John W. Vinson [MVP]

thanks
your sql works faster then dmax but if a person hes tow trans in a
same date I get tow records for this person

 

[John W. Vinson]

I have a table with 5 fields
ID PersonID Date Amount and Type

I want a query with the last record of each person based on date and I
want the results like this

PersonID LastOfDate Amount Type

How can i do that
thanks

A Subquery will do this:

SELECT PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID);

John W. Vinson [MVP]

thanks
your sql works faster then dmax but if a person hes tow trans in a
same date I get tow records for this person

The same would be true of DMax, and the same is true logically. If you had the
transactions on stacks of paper, and you wanted the most recent transaction,
and you found two sheets of paper saying June 18 - which of those two is "the
most recent"? If there are two records, with different amounts and different
types, both on the same day, which one do you want to see?

You can, if you wish, store the date AND TIME of the transaction by setting it
to Now() rather than to Date(); unless there are two transactions the same
microsecond you'll only get one.


John W. Vinson [MVP]

 

[zionsaal]

On Tue, 19 Jun 2007 17:45:20 -0700, (e-mail address removed) wrote:
I have a table with 5 fields
ID PersonID Date Amount and Type
I want a query with the last record of each person based on date and I
want the results like this
PersonID LastOfDate Amount Type
How can i do that
thanks
A Subquery will do this:
SELECT PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID);
John W. Vinson [MVP]

thanks
your sql works faster then dmax but if a person hes tow trans in a
same date I get tow records for this person

The same would be true of DMax, and the same is true logically. If you had the
transactions on stacks of paper, and you wanted the most recent transaction,
and you found two sheets of paper saying June 18 - which of those two is "the
most recent"? If there are two records, with different amounts and different
types, both on the same day, which one do you want to see?

You can, if you wish, store the date AND TIME of the transaction by setting it
to Now() rather than to Date(); unless there are two transactions the same
microsecond you'll only get one.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

I want to see any one of those

 

[zionsaal]

On Jun 19, 8:59 pm, John W. Vinson
On Tue, 19 Jun 2007 17:45:20 -0700, (e-mail address removed) wrote:
I have a table with 5 fields
ID PersonID Date Amount and Type
I want a query with the last record of each person based on date and I
want the results like this
PersonID LastOfDate Amount Type
How can i do that
thanks
A Subquery will do this:
SELECT PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID);
John W. Vinson [MVP]
thanks
your sql works faster then dmax but if a person hes tow trans in a
same date I get tow records for this person

The same would be true of DMax, and the same is true logically. If you had the
transactions on stacks of paper, and you wanted the most recent transaction,
and you found two sheets of paper saying June 18 - which of those two is "the
most recent"? If there are two records, with different amounts and different
types, both on the same day, which one do you want to see?

You can, if you wish, store the date AND TIME of the transaction by setting it
to Now() rather than to Date(); unless there are two transactions the same
microsecond you'll only get one.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

I want to see any one of those- Hide quoted text -

- Show quoted text -

wath about

SELECT ReceiveDetail.MasterID, Last(ReceiveDetail.EDate) AS
LastOfEDate, Last(ReceiveDetail.CampaignID) AS LastOfCampaignID,
Last(ReceiveDetail.Amount) AS LastOfAmount
FROM ReceiveDetail
GROUP BY ReceiveDetail.MasterID;

 

[John W. Vinson]

wath about

SELECT ReceiveDetail.MasterID, Last(ReceiveDetail.EDate) AS
LastOfEDate, Last(ReceiveDetail.CampaignID) AS LastOfCampaignID,
Last(ReceiveDetail.Amount) AS LastOfAmount
FROM ReceiveDetail
GROUP BY ReceiveDetail.MasterID;

LAST is *really* misleading. It does NOT mean "the last record entered"
although if you use it that way it will work often enough to trick you into
trusting it.

LAST means "the last record in disk storage order" - and this order is
arbitrary. Access will store records wherever there is room. Often this will
be at the end of the table but you cannot count on it!

If you don't care which record, try a TOP 1 query:

SELECT TOP 1 PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID)
ORDER BY PersonID;


John W. Vinson [MVP]

 

[zionsaal]

wath about

SELECT ReceiveDetail.MasterID, Last(ReceiveDetail.EDate) AS
LastOfEDate, Last(ReceiveDetail.CampaignID) AS LastOfCampaignID,
Last(ReceiveDetail.Amount) AS LastOfAmount
FROM ReceiveDetail
GROUP BY ReceiveDetail.MasterID;

LAST is *really* misleading. It does NOT mean "the last record entered"
although if you use it that way it will work often enough to trick you into
trusting it.

LAST means "the last record in disk storage order" - and this order is
arbitrary. Access will store records wherever there is room. Often this will
be at the end of the table but you cannot count on it!

If you don't care which record, try a TOP 1 query:

SELECT TOP 1 PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID)
ORDER BY PersonID;

John W. Vinson [MVP]

thank you! very very much! john for helping me

If i put top 1 I receive only 1 record at all!
I wont from each person in the table just 1 record

 

[zionsaal]

LAST is *really* misleading. It does NOT mean "the last record entered"
although if you use it that way it will work often enough to trick you into
trusting it.

LAST means "the last record in disk storage order" - and this order is
arbitrary. Access will store records wherever there is room. Often this will
be at the end of the table but you cannot count on it!

If you don't care which record, try a TOP 1 query:

SELECT TOP 1 PersonID, [Date], Amount, Type
FROM tablename
WHERE [Date] =
(SELECT Max([Date]) FROM tablename AS X
WHERE X.PersonID = tablename.PersonID)
ORDER BY PersonID;

John W. Vinson [MVP]

thank you! very very much! john for helping me

If i put top 1 I receive only 1 record at all!
I wont from each person in the table just 1 record- Hide quoted text -

- Show quoted text -

is there a way to use the "top" word in this sql and it will work?