Query Assistance (Last or Most Recent)

[dhinton]

I am trying to create two reports from an Access database that relate to a
field called placement type. In the first report, I want to run a report
listing the most recent placement type for clients (a single client may have
multiple placement types in the database). The query criteria that I am
using is:

Last("Date of Placement")

When I execute the query I get an error message indicating that I cannot
have aggregate function in WHERE clause (tbl_Placements.[Date of
Placement]=Last("Date of Placement")).

My second report/query that I want is to compare the last placement type
with the current placement type and determine if it is a stepdown in the
level of placement. I have no clue how to do this. I think that solving the
first query problem is part of the solution and I know that somewhere I will
have to define what constitutes a step down in placement, but don't know how
to go about doing this.
In the second report

 

[Daryl S]

Dhinton -

A couple items.

'Last' returns the last record in a recordset or query. If you have sorted
by date, then this would be the most recent record. If you don't have an
ORDER BY clause, then this record could be anything. I would recommend 'Max'
rather than 'Last', assuming [Date of Placement] is a true date field.

The "Date of Placement" is a string, not a fieldname. If you want to pull
the only the record with the last date of placement, then your WHERE clause
would look more like this (use your client FK for ClientID):

WHERE tbl_Placements.[Date of Placement]=(SELECT Max([Date of Placement])
FROM tbl_Placements AS tP
WHERE tbl_Placements.[ClientID]=tP.[ClientID])

For the second report/query you will need to join the table to itself (on
your client ID) because you will need information from two records (the most
recent and the one just prior to that). Here is the shell of the query -
substitute your field names.

Select P1.ClientID, P1.PlacementType AS CurrType, P1.[Date of Placement] As
CurrDate,
P2.PlacementType AS PriorType, P2.[Date of Placement] AS PriorDate
FROM FROM tbl_Placements AS P1 INNER JOIN tbl_Placements AS P2
ON P1.[ClientID]=P2.[ClientID]
WHERE P1.[Date of Placement]=(SELECT Max([Date of Placement]) FROM
tbl_Placements AS tP
WHERE P1.[ClientID]=tP.[ClientID])
AND P2.[Date of Placement]=(SELECT Max([Date of Placement]) FROM
tbl_Placements AS tP
WHERE P2.[ClientID]=tP.[ClientID] AND tP.[Date of Placement] < P1.[Date
of Placement])

Give it a try, and if you have problems, post your SQL with your issues.

 

[dhinton]

Thanks--That helped some--But I want the report to list all clients with
their last placement type so I don't want to have to enter a client id to
execute the query. Also, when I do execute the query--regardless of what
clientid I enter, it brings back the same record. It may be somehow tied to
an issue that seems to have happened once or twice. Somehow when users are
entering records, it is not recording the case id.

Here is the query that I have and I have also included a copy of the results.

SELECT tbl_Placements.[Case ID], tbl_Placements.[Date of Placement],
tbl_Placements.[Placement Type]
FROM tbl_Placements
WHERE tbl_Placements.[Date of Placement]=(SELECT Max([Date of Placement])
FROM tbl_Placements AS tP
WHERE tbl_Placements.[ClientID]=tP.[ClientID])

Results when executed.

Case ID Date of Placement Placement Type
2/11/2010 Hospital

I haven't tried the second portion of your response yet.

Dhinton -

A couple items.

'Last' returns the last record in a recordset or query. If you have sorted
by date, then this would be the most recent record. If you don't have an
ORDER BY clause, then this record could be anything. I would recommend 'Max'
rather than 'Last', assuming [Date of Placement] is a true date field.

The "Date of Placement" is a string, not a fieldname. If you want to pull
the only the record with the last date of placement, then your WHERE clause
would look more like this (use your client FK for ClientID):

WHERE tbl_Placements.[Date of Placement]=(SELECT Max([Date of Placement])
FROM tbl_Placements AS tP
WHERE tbl_Placements.[ClientID]=tP.[ClientID])

For the second report/query you will need to join the table to itself (on
your client ID) because you will need information from two records (the most
recent and the one just prior to that). Here is the shell of the query -
substitute your field names.

Select P1.ClientID, P1.PlacementType AS CurrType, P1.[Date of Placement] As
CurrDate,
P2.PlacementType AS PriorType, P2.[Date of Placement] AS PriorDate
FROM FROM tbl_Placements AS P1 INNER JOIN tbl_Placements AS P2
ON P1.[ClientID]=P2.[ClientID]
WHERE P1.[Date of Placement]=(SELECT Max([Date of Placement]) FROM
tbl_Placements AS tP
WHERE P1.[ClientID]=tP.[ClientID])
AND P2.[Date of Placement]=(SELECT Max([Date of Placement]) FROM
tbl_Placements AS tP
WHERE P2.[ClientID]=tP.[ClientID] AND tP.[Date of Placement] < P1.[Date
of Placement])

Give it a try, and if you have problems, post your SQL with your issues.

--
Daryl S

I am trying to create two reports from an Access database that relate to a
field called placement type. In the first report, I want to run a report
listing the most recent placement type for clients (a single client may have
multiple placement types in the database). The query criteria that I am
using is:

Last("Date of Placement")

When I execute the query I get an error message indicating that I cannot
have aggregate function in WHERE clause (tbl_Placements.[Date of
Placement]=Last("Date of Placement")).

My second report/query that I want is to compare the last placement type
with the current placement type and determine if it is a stepdown in the
level of placement. I have no clue how to do this. I think that solving the
first query problem is part of the solution and I know that somewhere I will
have to define what constitutes a step down in placement, but don't know how
to go about doing this.
In the second report

 

[De Jager]

I am trying to create two reports from an Access database that relate to a
field called placement type. In the first report, I want to run a report
listing the most recent placement type for clients (a single client may
have
multiple placement types in the database). The query criteria that I am
using is:

Last("Date of Placement")

When I execute the query I get an error message indicating that I cannot
have aggregate function in WHERE clause (tbl_Placements.[Date of
Placement]=Last("Date of Placement")).

My second report/query that I want is to compare the last placement type
with the current placement type and determine if it is a stepdown in the
level of placement. I have no clue how to do this. I think that solving
the
first query problem is part of the solution and I know that somewhere I
will
have to define what constitutes a step down in placement, but don't know
how
to go about doing this.
In the second report

 

[joelgeraldine]

uiompùù***