By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Creating a query from a continuous form

100+
P: 109
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
Expand|Select|Wrap|Line Numbers
  1. Field;Type;IndexInfo
  2. App_ID; Autonumber; PK  
  3. App_Per_ID; Number 
  4. App_Date; Date/Time 
  5. App_Outcome_ID; Number 
  6. App_Comments; Text 
  7.  
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:
Expand|Select|Wrap|Line Numbers
  1. App_ID    App_Per_ID    App_Date    App_Outcome_ID    App_Comments
  2. 1              2       02/10/2009         2
  3. 2              2       08/10/2009         2
  4. 3              2       19/10/2009         1
  5.  
If I run a query to select the last appointment, outcome and date then it will return the following:
Expand|Select|Wrap|Line Numbers
  1. App_Per_ID    App_Date    App_Outcome_ID    
  2. 2           08/10/2009         2
  3. 2           19/10/2009         1
  4.  
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!
Feb 15 '10 #1

✓ answered by nico5038

You would need a subquery like:
Expand|Select|Wrap|Line Numbers
  1. select App_Per_ID, App_Date, App_Outcome_ID
  2. from tbl_Appointment app
  3. 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)

Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,627
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]:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Appointment.App_Per_ID, Max(tbl_Appointment.App_Date) AS [Maximum App_Date], 
  2. Count(tbl_Appointment.App_Outcome_ID) AS [Outcome IDs]
  3. FROM tbl_Appointment
  4. GROUP BY tbl_Appointment.App_Per_ID;
  5.  
OUTCOME:
Expand|Select|Wrap|Line Numbers
  1. App_Per_ID    Maximum App_Date    Outcome IDs
  2.      2           10/19/2009            3
  3.  
Feb 16 '10 #2

100+
P: 109
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!
Feb 16 '10 #3

ADezii
Expert 5K+
P: 8,627
I'm pretty sure that I can arrive at a code-based solution if you are interested, just let me know.
Feb 16 '10 #4

100+
P: 109
Yes please! I am definitely interested, total hero!
Feb 16 '10 #5

ADezii
Expert 5K+
P: 8,627
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.
Feb 16 '10 #6

100+
P: 109
Take all the time you need man, I'm thankful enough that you're helping me anyway.
Feb 16 '10 #7

nico5038
Expert 2.5K+
P: 3,072
You would need a subquery like:
Expand|Select|Wrap|Line Numbers
  1. select App_Per_ID, App_Date, App_Outcome_ID
  2. from tbl_Appointment app
  3. 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)
Feb 16 '10 #8

100+
P: 109
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!
Feb 16 '10 #9

ADezii
Expert 5K+
P: 8,627
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)
Feb 16 '10 #10

100+
P: 109
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!
Feb 16 '10 #11

ADezii
Expert 5K+
P: 8,627
Here is the code-based solution along with the Test Database:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim strUnique As String
  3. Dim intAppOutcomeID As Integer
  4. Dim dteLatestDateForPerID As Date
  5. Dim rstResults As DAO.Recordset             'To populate tblResults
  6. Dim rstUniqueAppPerID As DAO.Recordset      'Unique [App_Per_ID]s
  7. Dim intResponse As Integer
  8.  
  9. strUnique = "SELECT DISTINCT [App_Per_ID] FROM tbl_Appointment ORDER BY [App_Per_ID];"
  10.  
  11. 'Remove any pre-existing Records from the Results Table (tblResults)
  12. CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError
  13.  
  14. Set MyDB = CurrentDb
  15. Set rstUniqueAppPerID = MyDB.OpenRecordset(strUnique, dbOpenForwardOnly)
  16. Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
  17.  
  18. With rstUniqueAppPerID
  19.   Do While Not .EOF
  20.     'Retrieve the Latest Date for the given [App_Per_ID]
  21.     dteLatestDateForPerID = DMax("[App_Date]", "tbl_Appointment", "[App_Per_ID] = " & ![App_Per_ID])
  22.  
  23.     'Retrieve the [App_Outcome_ID] for the given [App_Per_ID] and [App_Date]
  24.     intAppOutcomeID = DLookup("[App_Outcome_ID]", "tbl_Appointment", "[App_Per_ID] = " & ![App_Per_ID] & _
  25.                               " And [App_Date] = #" & dteLatestDateForPerID & "#")
  26.       rstResults.AddNew
  27.         rstResults![App_Per_ID] = ![App_Per_ID]
  28.         rstResults![Latest_Date] = dteLatestDateForPerID
  29.         rstResults![Outcome_ID] = intAppOutcomeID
  30.       rstResults.Update
  31.     'MsgBox ![App_Per_ID] & " ==> " & dteLatestDateForPerID & " ==> " & intAppOutcomeID
  32.       .MoveNext
  33.   Loop
  34. End With
  35.  
  36. rstResults.Close
  37. rstUniqueAppPerID.Close
  38. Set rstUniqueAppPerID = Nothing
  39. Set rstResults = Nothing
  40.  
  41. intResponse = MsgBox("Open Results Table?", vbYesNo + vbQuestion + vbDefaultButton1, "Results Table")
  42. If intResponse = vbYes Then
  43.   DoCmd.OpenTable "tblResults", acViewNormal, acReadOnly
  44.     DoCmd.Maximize
  45. End If
Attached Files
File Type: zip Continuous.zip (18.6 KB, 74 views)
Feb 16 '10 #12

100+
P: 109
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.
Feb 17 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.