If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Assistance (Last or Most Recent)
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 |
#2
|
|||
|
|||
Query Assistance (Last or Most Recent)
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 "dhinton" wrote: 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 |
#3
|
|||
|
|||
Query Assistance (Last or Most Recent)
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. "Daryl S" wrote: 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 "dhinton" wrote: 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 |
#4
|
|||
|
|||
Query Assistance (Last or Most Recent)
"dhinton" wrote in message ... 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 |
#5
|
|||
|
|||
Query Assistance (Last or Most Recent)
|
Thread Tools | |
Display Modes | |
|
|