Query to Pull Previous Record

[Reese]

[Solved] Query to Pull Previous Record

I have a form that is used to book a new event for a client who is already in the database.

Within that form I have a subform that is based on a query which displays information from that client's previous event. I did this using a solution found in the following thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=150368

It is based on pulling the second to last record that is related to the current client. It works perfectly when booking a new event that has taken place on a previous date. However, if the previous event occured on that same date (but at a previous time), it doesn't register.

I would prefer a query that would pull the record previous to the current one, instead of pulling the second to last record out of all that client's events.

Not only would this solve the time vs. date issue, it would also mean that if more events are booked, then a past event is opened in a form, the sub forms in that form will display the event just prior to the current record.

Does anyone have any suggestions?

 

[namliam]

Have you thought about trying to change the TOP 2 to a TOP 1?

 

[Reese]

I created a trial copy of the two queries and started experimenting with that.

Have you thought about trying to change the TOP 2 to a TOP 1?

The problem with that is it selects the last of the events, which is the event currently open in the main form. I want the previous event in the subforms.

I solved the same day issue by replacing the Date_of_Event in the SQL's WHERE criteria with Event_ID. The query still, however, pulls the second event of all the records associated with the client, rather than the event proceeding the currently open event.

I am going for the later functionality so I can:
-Create 3 events for a client
-Open the event 2 in a form to review it
-Have the query pull the information from event 1 rather than event 2

Here's the SQL for the first query:

SELECT [Event Information].Event_ID, [Event Information].Client_ID, [Event Information].Program_Code, [Event Information].Date_of_Event, [Event Information].Start_Time, [Event Information].End_Time, [Event Information].Client_Event, [Event Information].Program_Type, [Event Information].Program_Type_Other, [Event Information].Age_Start, [Event Information].Age_End, [Event Information].Audience_Notes, [Event Information].Theme, [Event Information].Event_Location, [Event Information].Event_Street, [Event Information].Event_City, [Event Information].Event_State, [Event Information].Event_Zip, [Event Information].Event_Phone_Number, [Event Information].Alt_Event_Contact, [Event Information].Alt_Event_Phone_Number, [Event Information].Cost_Category, [Event Information].Auditorium_Style, [Event Information].Indoors_Outdoors, [Event Information].Direction_Notes, [Event Information].Travel_Hours, [Event Information].Travel_Minutes, [Event Information].Other_Comments, [Event Information].Animal_Notes, [Event Information].Animal_1, [Event Information].Animal_2, [Event Information].Animal_3, [Event Information].Animal_4, [Event Information].Animal_5, [Event Information].Animal_6, [Event Information].Animal_7, [Event Information].Animal_8, [Event Information].Number_of_Participants, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee, [Event Information].Paid_By_PO, [Event Information].Paid, [Event Information].Unpaid_Contact_Date, [Event Information].Unpaid_Contact_Notes, [Event Information].Program_Cost, [Event Information].Cost_Per_Person, [Event Information].Cost_Per_Person_Other, [Event Information].[W-9_Needed], [Event Information].Insurance_Needed, [Event Information].Payment_Notes, [Event Information].Open_to_Public_Market, [Event Information].Distance, [Event Information].Canceled, [Event Information].Age_Start_Code, [Event Information].Age_End_Code
FROM [Event Information]
WHERE ( ( [Event Information].Program_Code="ZM" )
                AND ( [Event Information].Event_ID In
                           ( SELECT TOP 2  T.Event_ID
                             FROM [Event Information] T
                             WHERE T.Client_ID = [Event Information].Client_ID
                             ORDER BY T.Event_ID Desc
                            )
                          )
              );

And the second query:

SELECT TrialLastZMPart1Qry.Event_ID, TrialLastZMPart1Qry.Client_ID, TrialLastZMPart1Qry.Program_Code, TrialLastZMPart1Qry.Client_Event, TrialLastZMPart1Qry.Program_Type, TrialLastZMPart1Qry.Program_Type_Other, TrialLastZMPart1Qry.Date_of_Event, TrialLastZMPart1Qry.Start_Time, TrialLastZMPart1Qry.End_Time, TrialLastZMPart1Qry.Age_Start, TrialLastZMPart1Qry.Age_End, TrialLastZMPart1Qry.Audience_Notes, TrialLastZMPart1Qry.Theme, TrialLastZMPart1Qry.Event_Location, TrialLastZMPart1Qry.Event_Street, TrialLastZMPart1Qry.Event_City, TrialLastZMPart1Qry.Event_State, TrialLastZMPart1Qry.Event_Zip, TrialLastZMPart1Qry.Event_Phone_Number, TrialLastZMPart1Qry.Alt_Event_Contact, TrialLastZMPart1Qry.Alt_Event_Phone_Number, TrialLastZMPart1Qry.Cost_Category, TrialLastZMPart1Qry.Auditorium_Style, TrialLastZMPart1Qry.Indoors_Outdoors, TrialLastZMPart1Qry.Direction_Notes, TrialLastZMPart1Qry.Travel_Hours, TrialLastZMPart1Qry.Travel_Minutes, TrialLastZMPart1Qry.Other_Comments, TrialLastZMPart1Qry.Animal_Notes, TrialLastZMPart1Qry.Animal_1, TrialLastZMPart1Qry.Animal_2, TrialLastZMPart1Qry.Animal_3, TrialLastZMPart1Qry.Animal_4, TrialLastZMPart1Qry.Animal_5, TrialLastZMPart1Qry.Animal_6, TrialLastZMPart1Qry.Animal_7, TrialLastZMPart1Qry.Animal_8, TrialLastZMPart1Qry.Number_of_Participants, TrialLastZMPart1Qry.Auditorium_Cost, TrialLastZMPart1Qry.Millage_Fee, TrialLastZMPart1Qry.Paid_By_PO, TrialLastZMPart1Qry.Paid, TrialLastZMPart1Qry.Unpaid_Contact_Date, TrialLastZMPart1Qry.Unpaid_Contact_Notes, TrialLastZMPart1Qry.Program_Cost, TrialLastZMPart1Qry.Cost_Per_Person, TrialLastZMPart1Qry.Cost_Per_Person_Other, TrialLastZMPart1Qry.[W-9_Needed], TrialLastZMPart1Qry.Insurance_Needed, TrialLastZMPart1Qry.Payment_Notes, TrialLastZMPart1Qry.Open_to_Public_Market, TrialLastZMPart1Qry.Distance, TrialLastZMPart1Qry.Canceled, TrialLastZMPart1Qry.Age_Start_Code, TrialLastZMPart1Qry.Age_End_Code
FROM TrialLastZMPart1Qry
WHERE ( ( TrialLastZMPart1Qry.Event_ID
                  Not In ( SELECT MAX(Q1.Event_ID)
                                FROM TrialLastZMPart1Qry Q1
                                WHERE Q1.Client_ID = TrialLastZMPart1Qry.Client_ID
                              )
                )
              )
ORDER BY TrialLastZMPart1Qry.Event_ID DESC;

Is it possible to have some kind of criteria in the query that references information from the currently open record? Since the Invoice_Number is an automatically filled field whose value is +1 of the previous record, all events have an ever increasing value in that field.

I'm thinking that if I do something like:

WHERE ( [Event Information].Invoice_Number < The Invoice_Number of the currently opened record)

I know that I could do this by specifying the form:

WHERE ( [Event Information].Invoice_Number < Forms![Specific form name].Invoice_Number)

The problem is that this query is used in multiple forms and I'd rather not make multiple copies of the query, one for each form.

 

[Reese]

Does anyone have any further input or ideas? Thanks.

 

[Reese]

So after solving a different issue, I'm looking back into this one. Does anyone have any suggestions that would allow me to accomplish my goal without making a query for each individual form?

 

[plog]

Can you post sample data from your table (include field and table names), then based on that sample data identify which ones are the prior events.

 

[jdraw]

Here's a similar concept with the sql.

SELECT max(birthdate)
 from yourTable 'This done last and picks the second latest date
where birthdate
NOT IN
(select max(birthdate) from yourTable) 'This is done first and selects latest date

Concept is same.

 

[Reese]

Can you post sample data from your table (include field and table names), then based on that sample data identify which ones are the prior events.

Plog, do you mean just post it in text here in the thread or do you mean upload a sample of the database? Because the latter would take a while; I'd have to make a copy and slim it down significantly to only the relevant forms, tables, queries and a few sample entries.

If just having text works, here's some info:

Table Name: [Event Information]
Related Table Name: [Client Information]

Primary Key: Event_ID
Relationship Key with [Client Information]: Client_ID

Assume that all of the following samples have the same Client_ID and are therefore linked to the same client in [Client Information].

Record 1-- [Date_of_Event]: 1/5/14, [Program_Code]: ZM, [Program_Type]: (1) 45 Minute Formal, [Event_Location]: Binghamton High School, [Program_Cost]: $85

Record 2-- [Date_of_Event]: 1/20/14, [Program_Code]: ZM, [Program_Type]: (2) 45 Minute Formal, [Event_Location]: Binghamton High School, [Program_Cost]: $135

Record 3-- [Date_of_Event]: 3/10/14, [Program_Code]: ZM, [Program_Type]: (3) 45 Minute Formal, [Event_Location]: Binghamton High School, [Program_Cost]: $185


When initially entering Record 2, Record 1 is the prior event. When initially entering Record 3, Record 2 is the previous event. This works fine with my current system.

However, what I want to do is when opening a form to review Record 2 (after it has been entered), I want the queries to pull Record 1, which is prior to Record 2. Currently, using the TOP technique, it pulls Record 2, because it is the second to last out of all of the entries.

Jdraw, I'll play around with it. Thanks for the suggestion.

 

[plog]

What fields makes events group together? Is it the Program_Code, Program_Type and the Event_Location? Some combination of those fields?

How come you have 3 different Program_Type values of "45 Minute Formal"? Is the numeric value in parenthesis signficant?

 

[Reese]

What fields makes events group together?

It's the Client_ID field. I didn't include it in the listings since I stated that it would be the same. Sorry for the confusion. The [Program_Code] field also helps limit the information, since a client may have booked a Guided Tour (GT) in the past, and this query is only relevant to ZooMobiles (ZM).

Is the numeric value in parenthesis signficant?

Yes, it is. Clients can book 45 minute formals back to back, either just one, two or three of them. I suppose it probably would have helped if I put quotes around it. Again, sorry for the confusion.

 

[Reese]

Here's a similar concept with the sql.
Code:
SELECT max(birthdate) from yourTable 'This done last and picks the second latest date where birthdate NOT IN (select max(birthdate) from yourTable) 'This is done first and selects latest date
Concept is same.

Jdraw, this is based on date, correct? If so, then it may end up bring back the problem that if the two events in question are the same date, it won't pull the prior event.

I'd rather keep the query based on the Event_ID, now that I've discovered that this solves the "same day" issue. Or can this same technique be applied to the Event_ID?

 

[plog]

I guess I will have to make sample data then.

Event_ID
Event_ID, Client_ID, Date_of_Event, Program_Code
4, 2, 1/1/2014 1:00 PM, GT
7, 2, 1/6/2014 1:00 PM, ZM
8, 3, 1/7/2014 1:00 PM, ZM
11, 2, 1/12/2014 1:00 PM, ZM
15, 3, 1/12/2014 3:30 PM, ZM
16, 3, 1/12/2014 2:45 PM, GT
17, 3, 1/12/2014 2:00 PM, ZM
19, 3, 1/14/2014 2:00 PM, ZM

Based on what you've said, you need a query to make the below associations:

Event_ID, Prior_Event_ID
19, 15
17, 8
15, 17
11, 7
8,
7,


Is that accurate? Also, please tell me Date_Of_Event contains the time.

 

[Reese]

Also, please tell me Date_Of_Event contains the time.

I actually never realized that you can put BOTH in the same field. I've been using separate fields, [Start_Time] and [End_Time].

Unfortunately, at this point everything is so established that I won't be able to change it--too many forms, queries and reports. Besides, I'd still need to have an [End_Time] field.

But, otherwise, yes, you are right.

And, sorry, I didn't think to use that format in terms of how to layout the information.

 

[plog]

Which time field should be used in determining the prior event?

 

[jdraw]

I was giving you SQL for the concept of selecting the second highest or second latest value based on some criteria. Could be amount or Date..., my example was Date but it's the concept that's important.

If you select the Latest of Something

eg Select theLatest from Table Where X
and then you want the second latest

you would do

Select theLatest from Table where X and
theLatest NOT in
(select theLatest from Table where X);

 

[plog]

My solution takes 2 sub-queries:

 SELECT [Event Information].Event_ID, [Event Information].Client_ID, CDate([Date_of_Event] & " " & [Start_Time]) AS EventStart
FROM [Event Information]
WHERE ((([Event Information].Program_Code)="ZM"));

Paste that SQL into a query and save it as 'sub_PriorEvents_1. It just cleans up your data (merges date and time fields into one) and filters them to just those records with 'ZM' values in Program_Code field.

SELECT sub_PriorEvents_1.Event_ID, sub_PriorEvents_1.Client_ID, DMax("[EventStart]","sub_PriorEvents_1","[Client_ID]=" & [Client_ID] & " AND [EventStart]<#" & [EventStart] & "#") AS Prior_EventStart
FROM sub_PriorEvents_1;

Paste the above SQL into another query and name it 'sub_PriorEvents_2'. It determines the date/time of the event that occured prior to each event in sub_PriorEvents_1. This query is the key to implementing those that occured on the same day. Lastly, the below SQL will produce a dataset which links a specific Event_ID to its prior event's Event_ID.

SELECT sub_PriorEvents_2.Event_ID, sub_PriorEvents_1.Event_ID AS Prior_Event_ID
FROM sub_PriorEvents_1 RIGHT JOIN sub_PriorEvents_2 ON (sub_PriorEvents_1.Client_ID = sub_PriorEvents_2.Client_ID) AND (sub_PriorEvents_1.EventStart = sub_PriorEvents_2.Prior_EventStart);

 

[Reese]

Which time field should be used in determining the prior event?

Optimally, both [Date_of_Event] AND [Start_Time], but since they are two separate fields, that makes it more difficult.

As mentioned in post #3, I had changed the criteria from the previous [Date_of_Event] to the previous [Event_ID]. But after remembering that I was told that primary keys (like Event_ID) can become negative or some other weird stuff, I'm thinking of changing it to [Invoice_Number], which is an indexed, no duplicates field that, when entering a new record into the [Events Information] table, has a default value of +1 to the previous record in that table.

Here's a new set of sample data that includes all of the information that would be involved:

Event_ID, Client_ID, Date_of_Event, Start_Time, Program_Code, Invoice Number
3, 2, 1/20/14, 2pm, ZM, 3
6, 2, 2/23/14, 9am, ZM, 6
7, 2, 2/23/14, 1pm, ZM, 7
70, 2, 5/1/14, 10am, GT, 70
72, 2, 5/12/14, 1pm, ZM, 72

I'm ignoring situations where a new event is booked for a date before the date of the previously booked event for that same client. While it MIGHT happen, it would be INCREDIBLY rare. A far more likely situation is two programs are booked for the same day (e.g. one in the morning and then a second one 4 hours later).

Jdraw, I'll try playing with your suggestion with both Event_ID and Invoice_Number.

Thanks for the help, plog & jdraw. I'll continue playing with this but I may end up having to do out of office stuff until next week.

 

[Reese]

I'll continue playing with this but I may end up having to do out of office stuff until next week.

In fact I may need to shovel snow in the next hour. The joys of living in NY state.

 

[Reese]

Plog, I didn't refresh the page soon enough to catch your latest post before finishing mine. I see what you're saying there and I'll try it out. Thanks!

 

[Reese]

Plog, I experimented a bit and your suggestion worked perfectly! Thanks.

Jdraw, I played around with your suggestion for a while but couldn't get it to work. Thanks anyway.