Retrieve only the record with the max value within more record withthe same FK

[Nicola M]

Hi all! Access 2003

I'm trying to build a query to do what I wrote in the subject.
I have a table T_PEOPLE with a relation of 1 to many toward other
tables. Obviously, these tables can contain more than a record with the
FK of the T_PEOPLE.
I'm not able to extract, with a query that include both T_People and
T_ATableOnSideMany only one record for each IDPEOPLE I have in T_PEOPLE.
My experiment with first, max, top 1 etc don't work properly.
Thank you in advance for any suggestion or tip.

Nicola M

 

[MGFoster]

Hi all! Access 2003

I'm trying to build a query to do what I wrote in the subject.
I have a table T_PEOPLE with a relation of 1 to many toward other
tables. Obviously, these tables can contain more than a record with the
FK of the T_PEOPLE.
I'm not able to extract, with a query that include both T_People and
T_ATableOnSideMany only one record for each IDPEOPLE I have in T_PEOPLE.
My experiment with first, max, top 1 etc don't work properly.
Thank you in advance for any suggestion or tip.

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

Your problem definition is unclear. Perhaps this as the WHERE clause:

FROM TableManySide As S INNER JOIN ....
WHERE date_column = (SELECT MAX(date_column) FROM TableManySide
WHERE ID = S.ID)

This type of query has been discussed many times on this newsgroup -
search Google groups for more examples.

--
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/AwUBSXFJOIechKqOuFEgEQJ5sQCfUUgUdIdkwe/4caNNkhsfII6wEjgAoOwd
do9H/nnkzjxOmB6HJyaTRZmG
=CUh8
-----END PGP SIGNATURE-----

 

[Nicola M]

MGFoster ha scritto:

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

Thank you fo the answer

Your problem definition is unclear. Perhaps this as the WHERE clause:

I have in T_PEOPLE n records.
Also, in T_PEOPLE_IDCARDS, I could have 1 or many records with the same
IDPEOPLE value. For example I could have 3 records in T_PEOPLE_IDCARDS
with the following data:
IDCARDKEY IDPEOPLE NUMBER
.... ... ...
146 100 17500
200 100 20200
235 100 23150

I need a selection query that extract from T_PEOPLE_IDCARD, for each
IDPEOPLE in T_PEOPLE, only the record with the max IDCARDKEY value in
the T_PEOPLE_IDCARDS table, i.e that the person own in this moment.

FROM TableManySide As S INNER JOIN ....
WHERE date_column = (SELECT MAX(date_column) FROM TableManySide
WHERE ID = S.ID)

This type of query has been discussed many times on this newsgroup -
search Google groups for more examples.

I found some examples and theoretical articles about this issue but
notwithstanding all my attempts my queries don't work yet :-(

Nicola M

 

[MGFoster]

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

Perhaps this:

SELECT MAX(IDCARDKEY) AS MaxIDCardKey, IDPEOPLE
FROM T_PEOPLE_IDCARDS
GROUP BY IDPEOPLE

Or this:

SELECT IDCardKey, IDPeople, [Number]
FROM T_People_IDCards AS C INNER JOIN T_People As P
ON C.IDPeople=P.IDPeople
WHERE IDCardKey = (SELECT MAX(IDCardKey) FROM T_People_IDCards WHERE
IDPeople=P.IDPeople)

--
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/AwUBSXJ6B4echKqOuFEgEQKiIACg6IC1VlmH1d+UsFp0gLv5GnGcvsEAoMYD
hC1kqH+bODCxYQiqicvZ8jxM
=pZop
-----END PGP SIGNATURE-----