I have a form that displays information about an event that a returning client has booked. There are subforms to display information, based on a series of queries, from that client's last program. This allows the user to see what was done in the past, confirm with the client that it will be the same for the new event, and easily copy over the information into the new event's fields.
This is a well-established feature of my database and is based on a series of queries that I created with the help of people on this forum 2+ years ago. What made the queries so complex was the fact that they had to display only that client's exact previous event, based on the Date_of_Event and Start_Time fields.
Now I would like to add a new feature, if possible, and I am at a loss as to how to do so, because it would only apply to ONE client, not any others.
The Zoo--the organization that I work for and that uses this database--has it's own entry in the Client table, which is used when we create In House programs. Usually these programs are event specific (for our annual Zoo Brew event, for example) and I would like to add a criteria to the queries that display the information from the previous program so that it only displays that last event. E.g.: When entering this year's Zoo Brew, instead of pulling information from the zoo's previous event that is closet to Zoo Brew's date, the queries pull information from last year's Zoo Brew.
Here is the series of queries and how they work:
sub_PriorZM_1 pulls information from all ZooMobiles and merges the Date_of_Event and Start_Time fields into one date/time field.
sub_PriorZM_2 takes the Event_ID number of each client's previous event, matching it along side with the Event_ID number of each client's next event, based on the merged date/time field in sub_Prior_ZM_1.
Last_ZM_Link connects the two, displaying all of the details from the previous event entry to each Event_ID appropriately. (E.g. if Event_ID 200 and Event_ID 230 were booked by the same client and Event_ID 200 occured first, then this query shows all of Event_ID 200's information next to the Event_ID 230 entry.)
The subforms that display the previous program's last information are based on the Last_ZM_Link query and are linked to the main form by the Event_ID. So, using the previous example, if Event_ID 230 is displayed in the main form, information from Event_ID 200 is displayed in the subforms.
So now the question is if it is possible to (1) add a criteria that the queries only display the information from the previous program that matches the current programs Client_Event field, (2) make this criteria ONLY apply to In House programs booked under the Zoo's client entry (the Zoo's Client_ID is 2).
Does anyone think that's possible? And if so, how?
This is a well-established feature of my database and is based on a series of queries that I created with the help of people on this forum 2+ years ago. What made the queries so complex was the fact that they had to display only that client's exact previous event, based on the Date_of_Event and Start_Time fields.
Now I would like to add a new feature, if possible, and I am at a loss as to how to do so, because it would only apply to ONE client, not any others.
The Zoo--the organization that I work for and that uses this database--has it's own entry in the Client table, which is used when we create In House programs. Usually these programs are event specific (for our annual Zoo Brew event, for example) and I would like to add a criteria to the queries that display the information from the previous program so that it only displays that last event. E.g.: When entering this year's Zoo Brew, instead of pulling information from the zoo's previous event that is closet to Zoo Brew's date, the queries pull information from last year's Zoo Brew.
Here is the series of queries and how they work:
sub_PriorZM_1 pulls information from all ZooMobiles and merges the Date_of_Event and Start_Time fields into one date/time field.
Code:
SELECT [Event Information].Event_ID, [Event Information].Client_ID, CDate([Date_of_Event] & " " & [Start_Time]) AS EventStart, [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].Age_Start_Code, [Event Information].Age_End_Code, [Event Information].Audience_Notes, [Event Information].Theme, [Event Information].Event_Address_Same, [Event Information].Event_Location, [Event Information].Event_Street, [Event Information].Event_City, [Event Information].Event_State, [Event Information].Event_Zip, [Event Information].Alt_Event_Contact, [Event Information].Event_Phone_Number, [Event Information].Alt_Event_Phone_Number, [Event Information].Cost_Category, [Event Information].Auditorium_Style, [Event Information].Indoors_Outdoors, [Event Information].Direction_Notes, [Event Information].Distance, [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_8, [Event Information].Animal_7, [Event Information].Animal_6, [Event Information].Number_of_Participants, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee, [Event Information].Paid_By_PO, [Event Information].Paid, [Event Information].Pay_After, [Event Information].Payment_Notes, [Event Information].Canceled, [Event Information].Unpaid_Contact_Date, [Event Information].Unpaid_Contact_Notes, [Event Information].Cost_Per_Person, [Event Information].Cost_Per_Person_Other, [Event Information].[W-9_Needed], [Event Information].Insurance_Needed, [Event Information].Multiple_On_Billing, [Event Information].Open_to_Public_Market, [Event Information].Pay_At_Event, [Event Information].Unique_Mileage_Billing, [Event Information].Invoice_Date, [Event Information].Will_Pay_Direct_Deposit, [Event Information].Client_Type_Discount, [Event Information].Will_Reschedule
FROM [Event Information]
WHERE ((([Event Information].Program_Code)="ZM"));
Code:
SELECT sub_PriorZM_1.Event_ID, sub_PriorZM_1.Client_ID, DMax("[EventStart]","sub_PriorZM_1","[Client_ID]=" & [Client_ID] & " AND [EventStart]<#" & [EventStart] & "#") AS Prior_EventStart
FROM sub_PriorZM_1;
Code:
SELECT sub_PriorZM_2.Event_ID, sub_PriorZM_1.Event_ID AS Prior_Event_ID, sub_PriorZM_1.Date_of_Event, sub_PriorZM_1.Start_Time, sub_PriorZM_1.End_Time, sub_PriorZM_1.Client_Event, sub_PriorZM_1.Program_Type, sub_PriorZM_1.Program_Type_Other, sub_PriorZM_1.Age_Start, sub_PriorZM_1.Age_End, sub_PriorZM_1.Age_Start_Code, sub_PriorZM_1.Age_End_Code, sub_PriorZM_1.Audience_Notes, sub_PriorZM_1.Theme, sub_PriorZM_1.Event_Address_Same, sub_PriorZM_1.Event_Location, sub_PriorZM_1.Event_Street, sub_PriorZM_1.Event_City, sub_PriorZM_1.Event_State, sub_PriorZM_1.Event_Zip, sub_PriorZM_1.Alt_Event_Contact, sub_PriorZM_1.Event_Phone_Number, sub_PriorZM_1.Alt_Event_Phone_Number, sub_PriorZM_1.Cost_Category, sub_PriorZM_1.Auditorium_Style, sub_PriorZM_1.Indoors_Outdoors, sub_PriorZM_1.Direction_Notes, sub_PriorZM_1.Distance, sub_PriorZM_1.Travel_Hours, sub_PriorZM_1.Travel_Minutes, sub_PriorZM_1.Other_Comments, sub_PriorZM_1.Animal_Notes, sub_PriorZM_1.Animal_1, sub_PriorZM_1.Animal_2, sub_PriorZM_1.Animal_3, sub_PriorZM_1.Animal_4, sub_PriorZM_1.Animal_5, sub_PriorZM_1.Animal_8, sub_PriorZM_1.Animal_7, sub_PriorZM_1.Animal_6, sub_PriorZM_1.Number_of_Participants, sub_PriorZM_1.Program_Cost, sub_PriorZM_1.Auditorium_Cost, sub_PriorZM_1.Millage_Fee, sub_PriorZM_1.Paid_By_PO, sub_PriorZM_1.Paid, sub_PriorZM_1.Pay_After, sub_PriorZM_1.Payment_Notes, sub_PriorZM_1.Canceled, sub_PriorZM_1.Unpaid_Contact_Date, sub_PriorZM_1.Unpaid_Contact_Notes, sub_PriorZM_1.Cost_Per_Person, sub_PriorZM_1.Cost_Per_Person_Other, sub_PriorZM_1.[W-9_Needed], sub_PriorZM_1.Insurance_Needed, sub_PriorZM_1.Multiple_On_Billing, sub_PriorZM_1.Open_to_Public_Market, sub_PriorZM_1.Pay_At_Event, sub_PriorZM_1.Unique_Mileage_Billing, sub_PriorZM_1.Invoice_Date, sub_PriorZM_1.Will_Pay_Direct_Deposit, sub_PriorZM_1.Client_Type_Discount, sub_PriorZM_1.Will_Reschedule
FROM sub_PriorZM_1 RIGHT JOIN sub_PriorZM_2 ON (sub_PriorZM_1.EventStart = sub_PriorZM_2.Prior_EventStart) AND (sub_PriorZM_1.Client_ID = sub_PriorZM_2.Client_ID);
So now the question is if it is possible to (1) add a criteria that the queries only display the information from the previous program that matches the current programs Client_Event field, (2) make this criteria ONLY apply to In House programs booked under the Zoo's client entry (the Zoo's Client_ID is 2).
Does anyone think that's possible? And if so, how?