best way of doing a double query?

[sheepchild]

i have a query which pulls the information for specific event, and pulls all the other information into one form to send to client to confirm. which all works fine.

But then I came across a problem where some events can run over 2 or more days, but dont want to give them multiple sheets for each day. What is there best way to do a query and then pull the data from day1 and then day2?

i cant think around how to do it? or what the best way of doing it would be etc... any thoughts?

[PhilOfWalton]

Tad short of information, but haven you considered grouping your report by date?

Phil

[sheepchild]

Currently the query for the report goes from which event is open in the form.

SELECT tbl_Bookings.*, tbl_technical.*, tbl_Bookings.EventID
FROM tbl_Bookings LEFT JOIN tbl_technical ON tbl_Bookings.EventID = tbl_technical.[Event ID]
WHERE (((tbl_Bookings.EventID)=[Forms]![frmEventDetails]![EventID]));

[PhilOfWalton]

Assuming the event contains a FromDate and a ToDate then try grouping on the ToDate

Phil

[sheepchild]

have grouped on the date but, i think its the initial way i have set it up. as its only for one eventID, only pulls one record through.

just thinking aloud is there a way of, having a dynamic query where you can select which ones you want going through? so i could pick from a date range and then choose the ones from that which i want to do the report from?

Or say if the from the eventID say look for same event name including "day 2"?

sorry for the real vagueness on this kind of at a road block in this and dont know where to go to the next stage or to over come the problem.

[PhilOfWalton]

I am unclear.
Does one event have different details for the two different days? If so, to my mind it's 2 events. Those 2 events could be places with a single booking though.

Need to see some table structures.

Phil

[sheepchild]

event name, will be the same but it will have a different ID as catering and other items could change day to day.

layout is attached

[PhilOfWalton]

I think you've answered your own problem.

There should be a "Master Event" table and the TblBookings table should have an additional field MasterEventID linked to the MasterEvent table

TblMasterEvent
     MasterEventID     Auto
     MasterEventName   Text
 

The EventTitle would probably no longer be required.

Your forms & reports would be based on the MasterEvent table, and the Events(Bookings) would be subforms and subreports.

Phil

[sheepchild]

not quite sure how that would integrate into the current calendar of events that i have, which has been altered from another Db.

[PhilOfWalton]

The other alternative is to use the EventTitle as the filter on which you base your forms & reports, but this is strongly discouraged as you
a) have to ensure the event title is identical for the same event run on 2 consecutive days and
b) That event title is NEVER used again.

Without seeing your database, it is difficult to see what the ramifications are, but you are saying in practice, you have 2 separate events, and the only thing in common is that they were booked for the same person and that they have the same name. I would be very surprised (and disappointed) if the same menu was served up both days.

I know it means a radical re-think... sorry

Phil

[zmbd]

Phil, based on Sheepchild's other threads, and the image in post#9, I suspect the root database is
home > topics > microsoft access / vba > insights > ms access calendar - overview

This is ADezii's baby and he may be the best person to help here if this is the case. :)

@Sheepchild
You really need to point to Adezii's database if you need help, even with this mod, so that we have a reference point. The quality of your information you provide is going to dictate the quality of the answers we can provide and the length of time it takes for us to help you resolve the situation.

[sheepchild]

apologies i keep forgetting.

I think i have come up with a solution which works and helps for other things as well. I have got a query that now searches on date and title, so i can do it for a specific course, even if it spans 2 weeks. Also allowing me to do the same for the year etc.. as the data would be quite useful.