Adding Criteria To A Complex Query (1 Viewer)

[Reese]

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.

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"));

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.

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;

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.)

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);

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?

 

[Reese]

Also, I don't want to use any criteria in the query that involves naming the forms or controls in the forms. That's because there are 2 versions of the same form and I don't want to create 2 versions of this set of 3 queries.

Before anyone asks why 2 versions of the same form, here's why:

There is a one-to-many relationship between the Client and Event tables. So when creating a new event, the main form needs to be linked to the Client table with a subform linked to the Event table. But when opening an event that has already been created, it needs to be opened to the specific Event_ID, so the main form is linked to the Event table with a subform for the Client table.

 

[Simon_MT]

Firstly - multiple forms using the same field with a Where or filter Statement simplied code but the [dot] reprsents the current object (whatever Form is being used)

Dim Client_Event as Long (assumption it is a Long Integer)
     With CodeContextObject
            Client_Event = .[Client_Event]
            docmd ...
    End With

Secondly this really a matter of how you orgainised your data. Programs should have a Flag Inhouse. Programs could have Current/Previous/History if you want just the Program and not all activity.

Simon

 

[Reese]

Firstly - multiple forms using the same field with a Where or filter Statement simplied code but the [dot] reprsents the current object (whatever Form is being used)

Thank you for the suggestion, I'll take a look at it.

Secondly this really a matter of how you orgainised your data. Programs should have a Flag Inhouse.

One of the fields in the Event table is Cost_Category with options of Full Price, Discount, Donation, Swap or In House. But, because the entire database is structured with having a One to Many relationship between the Client table and the Event table, and each initial booking form has the main form linked to the Client table, I needed to have a client entry for the Zoo.

(Unless you could have a main form be blank, with a subform be filled with information, when every other event that ISN'T for the zoo has a client in the main form and event in the system, and not have that screw up the entire database.)

Programs could have Current/Previous/History if you want just the Program and not all activity.

I'm not sure what you mean by that. The Program information is all in the Event table, so it doesn't make sense to have it split into separate tables or have multiple fields in the same table (e.g. one field of Current_Client_Event, a second field Prior_Client_Event). That would make the table not normalized, wouldn't it?

And, if you are suggesting on having sub_PriorZM_1 pull less fields than it currently is (which, I admit, is a lot), that won't work. There is A LOT of information that needs to be used, compared and potentially copied from one event to the other. The event location has 9 fields just by itself (Event_Location, Event_Street, Event_City, Event_State, Event_Zip, Directions, Travel_Hours, Travel_Minutes, Distance). Let alone all of the information for date/time, age ranges, program type and notes, audience notes, payment type and notes, group size, animal notes, other comments and much more.

 

[Simon_MT]

There are two ways to do this - top down or bottom up:

Client and subform with Events. The Events are one the many side.

OR

Event Entry linked to the Client on the one side

I'm not suggesting that have separate tables just emulate Cost_Category with different values.

Simon

 

[Reese]

Hmmm... Well, the first option is how things are set up currently. Because one client can book multiple events.

Also, any change on that kind of level would be impossible. The database is too well established, with too many features to change such a building block to the entire database. I am not an IT person. I am actually in the Education Department and we needed this database and I have IT experience, so I created it over the span of 1 year, in between my normal duties, and have been slowly improving and building on it over 2 years. I can't spend the time to completely rebuild it from the ground up.

I'm not suggesting that have separate tables just emulate Cost_Category with different values.

I still don't understand what you mean. To what purpose would I emulate Cost_Category, how would I use it, and in what way would I emulate it?

 

[Simon_MT]

That no problem - processing multiple transactions is not unusual. All it needs is with each record have a dropdown of Events.

I not asking you to rebuild - but providing a single mechanism to distinguish between current and past Events. Personally, but putting the Clients Events is date descending order it is obvious the most current event.

A system I use puts through £15 million processing up to 25 transactions at a time.

Simon

 

[Reese]

All it needs is with each record have a dropdown of Events.

Each record in the Event table is it's own event. So I'm not sure what you mean by a dropdown of Events.

...but providing a single mechanism to distinguish between current and past Events.

Each record has a Date_of_Event field. To distinguish between current and past Events I've used queries I.e. "WHERE (Date_of_Event<Date())". In the case of this set of queries, the queries are sorted in descending order of Date_of_Event and sub_PriorZM_2 uses DMax to select the specific record prior to the current record.

 

[Simon_MT]

But u r associating an event with a client?

Got most of the second bit r u just showing the previous event?

simon

 

[Reese]

Yes, each event (or record) is associated with a client.

And the purpose of the 3 queries is to display the information from the event with a date and time that immediately precedes the current event.

The only change I wish to make is that if the client is the zoo (another way to think about it is if the event is In House), then the queries select the preceding event that has the same event name (e.g. Zoo Brew).

 

[Simon_MT]

Well then if you put in the criteria <> Event (and excluding In-House Events) on screen have the query as u do in descending order and then SELECT TOP 1 should pull the first (previous) Event.

It is just a matter of constructing the SQL query to met your needs. If may not work the firdt time but you know what u want it is just a matter of making it work.

Simon