Creating a query from a continuous form

[hedges98]

Hello!

I am having a bit of trouble trying to figure out how to create a query based on data retrieved from a continuous form. The table it is based on looks like this:

Table = tbl_Appointment

Field;Type;IndexInfo
App_ID; Autonumber; PK  
App_Per_ID; Number 
App_Date; Date/Time 
App_Outcome_ID; Number 
App_Comments; Text 
 

What I want to do is run a query to return the last appointment and its outcome from each individual client (App_Per_ID is the client ID).
I've tried using the Last and Max functions but it doesn't do what I need.
For example, a client could have appointment information that looks like the following:

App_ID    App_Per_ID    App_Date    App_Outcome_ID    App_Comments
1              2       02/10/2009         2
2              2       08/10/2009         2
3              2       19/10/2009         1
 

If I run a query to select the last appointment, outcome and date then it will return the following:

App_Per_ID    App_Date    App_Outcome_ID    
2           08/10/2009         2
2           19/10/2009         1
 

I can see why it is doing it - it's selecting the last result for each different outcome but I'm at a complete loss at how to make it return ONLY the last appointment regardless of how many it has had before (so basically, in the example above I would like it to just return the bottom record...)

Is this possible? I'm probably missing something glaringly obvious

Thanks in advance!

[ADezii]

The following SQL Statement will list the latest [App_Date] for a given [App_Per_ID] as well as the Number of [App_Outcome_ID]s for the [App_Per_ID]:

SELECT tbl_Appointment.App_Per_ID, Max(tbl_Appointment.App_Date) AS [Maximum App_Date], 
Count(tbl_Appointment.App_Outcome_ID) AS [Outcome IDs]
FROM tbl_Appointment
GROUP BY tbl_Appointment.App_Per_ID;
 

OUTCOME:

App_Per_ID    Maximum App_Date    Outcome IDs
     2           10/19/2009            3
 

[hedges98]

That's not quite what I need, sorry if I wasn't clear. I need to display just the last appointment and its outcome for each client only. From these results I was going to do a count of last outcomes. Although after staring at it for the best part of a day, I'm thinking it isn't possible.

Thanks for your efforts though ADezii!

[ADezii]

I'm pretty sure that I can arrive at a code-based solution if you are interested, just let me know.

[hedges98]

Yes please! I am definitely interested, total hero!

[ADezii]

Just give me a little time, since it won't be that simple. In the meantime I'm going to ask a couple of Experts who are more proficient than I if there is, in fact, an SQL based solution.

[hedges98]

Take all the time you need man, I'm thankful enough that you're helping me anyway.

[nico5038]

You would need a subquery like:

select App_Per_ID, App_Date, App_Outcome_ID
from tbl_Appointment app
WHERE app.App_Date = (select Max(app1.App_Date) from tbl_Appointment app1 where app1.App_Per_ID = app.App_Per_ID)

There's still one minor "problem", as when there are two appointments on the max date, two will be returned...
This could be solved by adding a unique (autonumber with increment + 1) index and extracting in such a case the highest autonumber.

Nic;o)

[hedges98]

Thank you nico5038, that's absolutely perfect.

There is only one appointment per day so that problem is a total non-issue. My brain can take a bit of a rest now.

Thanks to ADezii for the input as well!

[ADezii]

Thanks Nico, since my head was hurting from banging it against the wall. I arrived at a code-based solution, but it is considerably more complex! (LOL)

[hedges98]

Out of interest, could you post the code-based solution? It will probably pass completely over my head but I wouldn't mind trying to look over it and understand it!

[ADezii]

Here is the code-based solution along with the Test Database:

Dim MyDB As DAO.Database
Dim strUnique As String
Dim intAppOutcomeID As Integer
Dim dteLatestDateForPerID As Date
Dim rstResults As DAO.Recordset             'To populate tblResults
Dim rstUniqueAppPerID As DAO.Recordset      'Unique [App_Per_ID]s
Dim intResponse As Integer
 
strUnique = "SELECT DISTINCT [App_Per_ID] FROM tbl_Appointment ORDER BY [App_Per_ID];"
 
'Remove any pre-existing Records from the Results Table (tblResults)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError
 
Set MyDB = CurrentDb
Set rstUniqueAppPerID = MyDB.OpenRecordset(strUnique, dbOpenForwardOnly)
Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
 
With rstUniqueAppPerID
  Do While Not .EOF
    'Retrieve the Latest Date for the given [App_Per_ID]
    dteLatestDateForPerID = DMax("[App_Date]", "tbl_Appointment", "[App_Per_ID] = " & ![App_Per_ID])
 
    'Retrieve the [App_Outcome_ID] for the given [App_Per_ID] and [App_Date]
    intAppOutcomeID = DLookup("[App_Outcome_ID]", "tbl_Appointment", "[App_Per_ID] = " & ![App_Per_ID] & _
                              " And [App_Date] = #" & dteLatestDateForPerID & "#")
      rstResults.AddNew
        rstResults![App_Per_ID] = ![App_Per_ID]
        rstResults![Latest_Date] = dteLatestDateForPerID
        rstResults![Outcome_ID] = intAppOutcomeID
      rstResults.Update
    'MsgBox ![App_Per_ID] & " ==> " & dteLatestDateForPerID & " ==> " & intAppOutcomeID
      .MoveNext
  Loop
End With
 
rstResults.Close
rstUniqueAppPerID.Close
Set rstUniqueAppPerID = Nothing
Set rstResults = Nothing
 
intResponse = MsgBox("Open Results Table?", vbYesNo + vbQuestion + vbDefaultButton1, "Results Table")
If intResponse = vbYes Then
  DoCmd.OpenTable "tblResults", acViewNormal, acReadOnly
    DoCmd.Maximize
End If

[hedges98]

Thanks ADezii, I sort of understand how you came to that solution although I'd never have got there myself as I've never done anything with Recordsets and so on.