Return Record for Latest Transaction

[cixelsyd]

I have a table containing transaction data for many
items (Partno). Each item may have several entries in the table due to
transactions on different dates (TDate).
I wish to select the complete record for each item for the most recent
transactions (TDate) from a table containing many records. How do I structure
the query?

Table

Partno1 Date1 DataX DataY
Partno1 Date2 DataX DataY
Partno1 Date3 DataX DataY > most recent date
Partno2 Date1 DataX DataY
Partno3 Date1 DataX DataY

I wish to return

Partno1 Date3 DataX DataY > most recent date
Partno2 Date1 DataX DataY
Partno3 Date1 DataX DataY

 

[Michel Walsh]

Four possible solutions at http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT PartNo, TDate, data_x, data_y
FROM table_name As T
WHERE TDate = (SELECT MAX(TDate) FROM table_name
WHERE PartNo= T.PartNo)

Change column & table names to suit your set up.

For a speedy query make sure the part_nbr and xactn_date columns are
indexed.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWUdqYechKqOuFEgEQJNmACg9Yl5f/hbIJYYy1G3JEuIqMMN6pkAoLVo
2wAXhsxcmuJKktZYsLobXkWY
=cvMJ
-----END PGP SIGNATURE-----

 

[cixelsyd]

More info, I'm joining data from 2 tables, can't seem to get the query to
work ,,,

Here's what the entire query looks like:

SELECT A.PartNo,
A.TDate,
B.data_x,
A.data_y
FROM B RIGHT JOIN A ON B.data_y=A.data_y AS T
(WHERE A.TDate=(SELECT MAX(A.TDate) FROM A
WHERE A.PartNo=T.PartNo))
ORDER BY A.PartNo;

Rookie requires Guidance

Try this:

SELECT PartNo, TDate, data_x, data_y
FROM table_name As T
WHERE TDate = (SELECT MAX(TDate) FROM table_name
WHERE PartNo= T.PartNo)

Change column & table names to suit your set up.

For a speedy query make sure the part_nbr and xactn_date columns are
indexed.

I have a table containing transaction data for many
items (Partno). Each item may have several entries in the table due to

[quoted text clipped - 16 lines]

Partno2 Date1 DataX DataY
Partno3 Date1 DataX DataY

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The "As T" is an alias for the table that holds the PartNo, so it
shouldn't be after the ON phrase, it should be after the table name that
holds the PartNo.

Try:

SELECT T.PartNo,
T.TDate,
B.data_x,
T.data_y
FROM B RIGHT JOIN A As T ON B.data_y=T.data_y
WHERE T.TDate=(SELECT MAX(A.TDate)
FROM A INNER JOIN B ON B.data_y=A.data_y
WHERE A.PartNo=T.PartNo)
ORDER BY T.PartNo;

The WHERE clause does not require parentheses surrounding it.

The A.PartNo in the sub-query's WHERE clause is compared to the PartNo
in the main query's A table (aliased as table T). We need to alias the
A table in the main query so we can reference it in the sub-query
without confusing it with the A table in the sub-query.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWUusYechKqOuFEgEQI6fwCff+200zK1stzJFqb8qjWEEw9G1UIAnRk8
r5F+d5oIBeZm/b3er2A6mX/S
=NTrO
-----END PGP SIGNATURE-----

More info, I'm joining data from 2 tables, can't seem to get the query to
work ,,,

Here's what the entire query looks like:

SELECT A.PartNo,
A.TDate,
B.data_x,
A.data_y
FROM B RIGHT JOIN A ON B.data_y=A.data_y AS T
(WHERE A.TDate=(SELECT MAX(A.TDate) FROM A
WHERE A.PartNo=T.PartNo))
ORDER BY A.PartNo;

Rookie requires Guidance

Try this:

SELECT PartNo, TDate, data_x, data_y
FROM table_name As T
WHERE TDate = (SELECT MAX(TDate) FROM table_name
WHERE PartNo= T.PartNo)

Change column & table names to suit your set up.

For a speedy query make sure the part_nbr and xactn_date columns are
indexed.

I have a table containing transaction data for many
items (Partno). Each item may have several entries in the table due to

[quoted text clipped - 16 lines]

Partno2 Date1 DataX DataY
Partno3 Date1 DataX DataY

 

[cixelsyd via AccessMonster.com]

MG,

Works well - thanks !

A few more questions:

1. A.Date can sometimes be a NULL record .. I am attempting to keep these
records and replace the NULL with "NotPurchased" using the following

WHERE T.TDate=(SELECT MAX(IIf([A.TDate] IsNull, NotOrdered, [A.TDate]))
FROM A INNER JOIN B ON B.data_y=A.data_y
WHERE A.PartNo=T.PartNo)

This returns a syntax error - can IIf be nested inside SELECT MAX?

2.Per an earlier post, I'd like to include CREATE INDEX to speed the query -
where does this belong?

The "As T" is an alias for the table that holds the PartNo, so it
shouldn't be after the ON phrase, it should be after the table name that
holds the PartNo.

Try:

SELECT T.PartNo,
T.TDate,
B.data_x,
T.data_y
FROM B RIGHT JOIN A As T ON B.data_y=T.data_y
WHERE T.TDate=(SELECT MAX(A.TDate)
FROM A INNER JOIN B ON B.data_y=A.data_y
WHERE A.PartNo=T.PartNo)
ORDER BY T.PartNo;

The WHERE clause does not require parentheses surrounding it.

The A.PartNo in the sub-query's WHERE clause is compared to the PartNo
in the main query's A table (aliased as table T). We need to alias the
A table in the main query so we can reference it in the sub-query
without confusing it with the A table in the sub-query.

More info, I'm joining data from 2 tables, can't seem to get the query to
work ,,,

[quoted text clipped - 29 lines]

 

[cixelsyd via AccessMonster.com]

Update on NULL record (1. below), found my syntax "IsNull" should be "Is
Null"; my replacement value "NotOrdered" is invalid as the field type is
DATE/TIME, so I've replaced with "00-00-00" in the query ... I still do not
get any records returned for items with NULL values for A.TDate,

Help !

MG,

Works well - thanks !

A few more questions:

1. A.Date can sometimes be a NULL record .. I am attempting to keep these
records and replace the NULL with "NotPurchased" using the following

WHERE T.TDate=(SELECT MAX(IIf([A.TDate] IsNull, NotOrdered, [A.TDate]))
FROM A INNER JOIN B ON B.data_y=A.data_y
WHERE A.PartNo=T.PartNo)

This returns a syntax error - can IIf be nested inside SELECT MAX?

2.Per an earlier post, I'd like to include CREATE INDEX to speed the query -
where does this belong?

The "As T" is an alias for the table that holds the PartNo, so it
shouldn't be after the ON phrase, it should be after the table name that

[quoted text clipped - 24 lines]

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the Nz() function instead of the IIf() function:

MAX(Nz(A.TDate,"00/00/00"))

Nz() means: If the first parameter is NULL, use what is in the second
parameter.

A NULL date means an UNKNOWN date. When you change the date to
"00/00/00" and then compare it to the T.TDate of course there aren't
going to be any matches 'cuz no one has entered "00/00/00" as a TDate.
IOW, when the right-side A.TDate is "00/00/00"

WHERE T.TDate=(SELECT MAX(Nz(A.TDate,"00/00/00"))

translates to

WHERE "3/15/2008" =(SELECT "00/00/00"

They don't equal and, therefore, the record is not selected.

You could change it to

WHERE Nz(T.TDate,"00/00/00") = (SELECT MAX(Nz(A.TDate,"00/00/00"))

But I suspect you'll get some weird results.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWZwCYechKqOuFEgEQKXuQCgzqFLzjMaFZMfnrIUrfhcoWCfiCgAoLAS
ZsShKHdiKefIOmB9QQlJl61S
=IzCy
-----END PGP SIGNATURE-----

Update on NULL record (1. below), found my syntax "IsNull" should be "Is
Null"; my replacement value "NotOrdered" is invalid as the field type is
DATE/TIME, so I've replaced with "00-00-00" in the query ... I still do not
get any records returned for items with NULL values for A.TDate,

Help !

MG,

Works well - thanks !

A few more questions:

1. A.Date can sometimes be a NULL record .. I am attempting to keep these
records and replace the NULL with "NotPurchased" using the following

WHERE T.TDate=(SELECT MAX(IIf([A.TDate] IsNull, NotOrdered, [A.TDate]))
FROM A INNER JOIN B ON B.data_y=A.data_y
WHERE A.PartNo=T.PartNo)

This returns a syntax error - can IIf be nested inside SELECT MAX?

2.Per an earlier post, I'd like to include CREATE INDEX to speed the query -
where does this belong?

The "As T" is an alias for the table that holds the PartNo, so it
shouldn't be after the ON phrase, it should be after the table name that

[quoted text clipped - 24 lines]

Partno2 Date1 DataX DataY
Partno3 Date1 DataX DataY