Select Newest Child Record

[Guest]

I have two tables, Offender and Address, that are related based on the field
ID. The Address table contains multiple addresses for a given ID and an
AddrerssDate field. I need my query to select information from the Offender
table and the most recent address from the Address table based upon the
current record shown in the Offender Information form. I have reviewed past
postings and found the following example, but can't quite get the coding
right to make this work.

Example that I found:

SELECT Events.EventTime, Events.Address, Events.SubAddr, Events.UserID,
Users.FirstName, Users.Surname
FROM Events INNER JOIN Users ON Events.UserID = Users.UserID
WHERE ((((SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID))=(SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID AND SubAddr = 1)))
ORDER BY Events.UserID, Events.EventTime DESC;

BTW: As this query will return only one record per UserID, I think the
second column in the ORDER BY clause is redundant.

What I have now that returns ALL addresses:

SELECT OffenderAddress.ID, OffenderAddress.*, Offenders.FName,
Offenders.MInitial, Offenders.LName
FROM Offenders LEFT JOIN OffenderAddress ON Offenders.ID = OffenderAddress.ID
WHERE (((OffenderAddress.ID)=[Forms]![Offender Information]![ID]));

Thanks for any assistance.

Randy Hartwick

 

[Tom Ellison]

Dear Randy:

Reformatting and adding aliases to your query, for my own reference:

SELECT OA.ID, OA.*, O.FName,
O.MInitial, O.LName
FROM Offenders O
LEFT JOIN OffenderAddress OA
ON O.ID = OA.ID
WHERE OA.ID = [Forms]![Offender Information]![ID]

To limit this to the most recent row from OffenderAddress there must be a
column in OffenderAddress for the date/time of that record. I do not have
the name of that column (as far as I can see) so I'll just call it DateTime.
You can substitute the actual name of the column in my work below:

SELECT OA.ID, OA.*, O.FName,
O.MInitial, O.LName
FROM Offenders O
LEFT JOIN OffenderAddress OA
ON O.ID = OA.ID
WHERE OA.ID = [Forms]![Offender Information]![ID]
AND OA.DateTime = (SELECT MAX(T.DateTime)
FROM OffenderAddress T
WHERE T.ID = OA.ID)

This uses a "correlated subquery" and "aliasing" (I give you the terms so
you can search and study them).

Tom Ellison

 

[Guest]

Tom,

Thanks for your reply. Sorry I forgot to mention that I do have a date
field in the address record.

I am confused on your one reference to the value T in the last three lines
of your revised query. I am not sure what the T refers to. The three
specific references are:

T.DateTime
Offender Address T
T.ID

Thanks,

Randy

Dear Randy:

Reformatting and adding aliases to your query, for my own reference:

SELECT OA.ID, OA.*, O.FName,
O.MInitial, O.LName
FROM Offenders O
LEFT JOIN OffenderAddress OA
ON O.ID = OA.ID
WHERE OA.ID = [Forms]![Offender Information]![ID]

To limit this to the most recent row from OffenderAddress there must be a
column in OffenderAddress for the date/time of that record. I do not have
the name of that column (as far as I can see) so I'll just call it DateTime.
You can substitute the actual name of the column in my work below:

SELECT OA.ID, OA.*, O.FName,
O.MInitial, O.LName
FROM Offenders O
LEFT JOIN OffenderAddress OA
ON O.ID = OA.ID
WHERE OA.ID = [Forms]![Offender Information]![ID]
AND OA.DateTime = (SELECT MAX(T.DateTime)
FROM OffenderAddress T
WHERE T.ID = OA.ID)

This uses a "correlated subquery" and "aliasing" (I give you the terms so
you can search and study them).

Tom Ellison

I have two tables, Offender and Address, that are related based on the
field
ID. The Address table contains multiple addresses for a given ID and an
AddrerssDate field. I need my query to select information from the
Offender
table and the most recent address from the Address table based upon the
current record shown in the Offender Information form. I have reviewed
past
postings and found the following example, but can't quite get the coding
right to make this work.

Example that I found:

SELECT Events.EventTime, Events.Address, Events.SubAddr, Events.UserID,
Users.FirstName, Users.Surname
FROM Events INNER JOIN Users ON Events.UserID = Users.UserID
WHERE ((((SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID))=(SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID AND SubAddr = 1)))
ORDER BY Events.UserID, Events.EventTime DESC;

BTW: As this query will return only one record per UserID, I think the
second column in the ORDER BY clause is redundant.

What I have now that returns ALL addresses:

SELECT OffenderAddress.ID, OffenderAddress.*, Offenders.FName,
Offenders.MInitial, Offenders.LName
FROM Offenders LEFT JOIN OffenderAddress ON Offenders.ID =
OffenderAddress.ID
WHERE (((OffenderAddress.ID)=[Forms]![Offender Information]![ID]));

Thanks for any assistance.

Randy Hartwick

 

[Tom Ellison]

Dear Randy:

One essential point is that I did not use "Offender Address" but
"OffenderAddress" (with no space). This is, I thought, the name of one of
your tables. If it does have a space in it, then it would have to be
[Offender Address] instead.

The T is an alias. The query must independently access two copies of the
same table. At least one of them must be aliased so each reference to the
table (or its fields) can be specified unambiguously. This is an essential
concept in being able to perform the functions of a correlated subquery (as
well as other somewhat advanced query techniques). You should not that I
aliased the Offenders table as O as well, and the other instance of the
OffenderAddress table as OA.

The query treats the two instances of the OffenderAddress table as
distinctly as if they were two different tables. Everywhere in the query
you need to be concise about specifying to which of these two instance you
refer. Well, actually, this is only true within the "scope" of the portions
of the query where both are available. As a subquery, the scope of the
alias "T" is limited to the area within the parens. Outside this there is
no possible conflict between the two instances of OffenderAddress. There is
still the possibility of ambiguity between Offenders and OffenderAddress,
especially to the extent that they may have column names in common. Any
column name that is unique would not have to have the qualifycation telling
from which table it originates. The database engine can resolve these
without help. Still, it's best to qualify everything in all but the
simplest queries. If you were to later add a new column to one of the
tables, this could cause many queries to become "broken" because this would
introduce an ambiguity if the new column name matches a column name in
another table. So, "fully qualifying" all the columns in any query with
more than one instance of one table is generally a very good practice. Any
time you add a second table to a query that does not have fully qualified
column names it is recommended you add full qualification to the existing
query before proceeding. Doing so using Aliases both improves readability
and prepares for possible later reference to multiple copies of the same
table, or of duplicate column names in different tables.

Tom Ellison

Tom,

Thanks for your reply. Sorry I forgot to mention that I do have a date
field in the address record.

I am confused on your one reference to the value T in the last three lines
of your revised query. I am not sure what the T refers to. The three
specific references are:

T.DateTime
Offender Address T
T.ID

Thanks,

Randy

Dear Randy:

Reformatting and adding aliases to your query, for my own reference:

SELECT OA.ID, OA.*, O.FName,
O.MInitial, O.LName
FROM Offenders O
LEFT JOIN OffenderAddress OA
ON O.ID = OA.ID
WHERE OA.ID = [Forms]![Offender Information]![ID]

To limit this to the most recent row from OffenderAddress there must be a
column in OffenderAddress for the date/time of that record. I do not
have
the name of that column (as far as I can see) so I'll just call it
DateTime.
You can substitute the actual name of the column in my work below:

SELECT OA.ID, OA.*, O.FName,
O.MInitial, O.LName
FROM Offenders O
LEFT JOIN OffenderAddress OA
ON O.ID = OA.ID
WHERE OA.ID = [Forms]![Offender Information]![ID]
AND OA.DateTime = (SELECT MAX(T.DateTime)
FROM OffenderAddress T
WHERE T.ID = OA.ID)

This uses a "correlated subquery" and "aliasing" (I give you the terms so
you can search and study them).

Tom Ellison


message

I have two tables, Offender and Address, that are related based on the
field
ID. The Address table contains multiple addresses for a given ID and
an
AddrerssDate field. I need my query to select information from the
Offender
table and the most recent address from the Address table based upon the
current record shown in the Offender Information form. I have reviewed
past
postings and found the following example, but can't quite get the
coding
right to make this work.

Example that I found:

SELECT Events.EventTime, Events.Address, Events.SubAddr, Events.UserID,
Users.FirstName, Users.Surname
FROM Events INNER JOIN Users ON Events.UserID = Users.UserID
WHERE ((((SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID))=(SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID AND SubAddr = 1)))
ORDER BY Events.UserID, Events.EventTime DESC;

BTW: As this query will return only one record per UserID, I think the
second column in the ORDER BY clause is redundant.

What I have now that returns ALL addresses:

SELECT OffenderAddress.ID, OffenderAddress.*, Offenders.FName,
Offenders.MInitial, Offenders.LName
FROM Offenders LEFT JOIN OffenderAddress ON Offenders.ID =
OffenderAddress.ID
WHERE (((OffenderAddress.ID)=[Forms]![Offender Information]![ID]));

Thanks for any assistance.

Randy Hartwick

 

[Guest]

Tom,

If I had more closely read your first answer I would have realized that you
were using an alais. Thank you for your very considerate and informative
reply.

Randy

Dear Randy:

One essential point is that I did not use "Offender Address" but
"OffenderAddress" (with no space). This is, I thought, the name of one of
your tables. If it does have a space in it, then it would have to be
[Offender Address] instead.

The T is an alias. The query must independently access two copies of the
same table. At least one of them must be aliased so each reference to the
table (or its fields) can be specified unambiguously. This is an essential
concept in being able to perform the functions of a correlated subquery (as
well as other somewhat advanced query techniques). You should not that I
aliased the Offenders table as O as well, and the other instance of the
OffenderAddress table as OA.

The query treats the two instances of the OffenderAddress table as
distinctly as if they were two different tables. Everywhere in the query
you need to be concise about specifying to which of these two instance you
refer. Well, actually, this is only true within the "scope" of the portions
of the query where both are available. As a subquery, the scope of the
alias "T" is limited to the area within the parens. Outside this there is
no possible conflict between the two instances of OffenderAddress. There is
still the possibility of ambiguity between Offenders and OffenderAddress,
especially to the extent that they may have column names in common. Any
column name that is unique would not have to have the qualifycation telling
from which table it originates. The database engine can resolve these
without help. Still, it's best to qualify everything in all but the
simplest queries. If you were to later add a new column to one of the
tables, this could cause many queries to become "broken" because this would
introduce an ambiguity if the new column name matches a column name in
another table. So, "fully qualifying" all the columns in any query with
more than one instance of one table is generally a very good practice. Any
time you add a second table to a query that does not have fully qualified
column names it is recommended you add full qualification to the existing
query before proceeding. Doing so using Aliases both improves readability
and prepares for possible later reference to multiple copies of the same
table, or of duplicate column names in different tables.

Tom Ellison

Tom,

Thanks for your reply. Sorry I forgot to mention that I do have a date
field in the address record.

I am confused on your one reference to the value T in the last three lines
of your revised query. I am not sure what the T refers to. The three
specific references are:

T.DateTime
Offender Address T
T.ID

Thanks,

Randy

Dear Randy:

Reformatting and adding aliases to your query, for my own reference:

SELECT OA.ID, OA.*, O.FName,
O.MInitial, O.LName
FROM Offenders O
LEFT JOIN OffenderAddress OA
ON O.ID = OA.ID
WHERE OA.ID = [Forms]![Offender Information]![ID]

To limit this to the most recent row from OffenderAddress there must be a
column in OffenderAddress for the date/time of that record. I do not
have
the name of that column (as far as I can see) so I'll just call it
DateTime.
You can substitute the actual name of the column in my work below:

SELECT OA.ID, OA.*, O.FName,
O.MInitial, O.LName
FROM Offenders O
LEFT JOIN OffenderAddress OA
ON O.ID = OA.ID
WHERE OA.ID = [Forms]![Offender Information]![ID]
AND OA.DateTime = (SELECT MAX(T.DateTime)
FROM OffenderAddress T
WHERE T.ID = OA.ID)

This uses a "correlated subquery" and "aliasing" (I give you the terms so
you can search and study them).

Tom Ellison


message
I have two tables, Offender and Address, that are related based on the
field
ID. The Address table contains multiple addresses for a given ID and
an
AddrerssDate field. I need my query to select information from the
Offender
table and the most recent address from the Address table based upon the
current record shown in the Offender Information form. I have reviewed
past
postings and found the following example, but can't quite get the
coding
right to make this work.

Example that I found:

SELECT Events.EventTime, Events.Address, Events.SubAddr, Events.UserID,
Users.FirstName, Users.Surname
FROM Events INNER JOIN Users ON Events.UserID = Users.UserID
WHERE ((((SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID))=(SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID AND SubAddr = 1)))
ORDER BY Events.UserID, Events.EventTime DESC;

BTW: As this query will return only one record per UserID, I think the
second column in the ORDER BY clause is redundant.

What I have now that returns ALL addresses:

SELECT OffenderAddress.ID, OffenderAddress.*, Offenders.FName,
Offenders.MInitial, Offenders.LName
FROM Offenders LEFT JOIN OffenderAddress ON Offenders.ID =
OffenderAddress.ID
WHERE (((OffenderAddress.ID)=[Forms]![Offender Information]![ID]));

Thanks for any assistance.

Randy Hartwick