Finding Clashes (1 Viewer)

[Fiona]

I have a small database that acts as an events diary for our arts venue. I have fields for event name (text), date, and one for each of our spaces (with a check box). I have a query that can tell me if there is more than one event on any one day - but what I would really like is to be able to look at all dates that have more than one event and that have a duplicate in any one of the spaces.
I've tried fiddling around with the wizards and trying this and that but to no avail.
If anyone could point me in the right direction I'd be very grateful.
Many thanks,

Fiona

PS I hope the question makes sense

 

[David R]

Hmmmm, are we talking about scheduling the same event (i.e. the Roush recital) for two different times on the same day? Or trying to schedule two separate events for the same time slot?

 

[Fiona]

Hello,

We have a series of Victorian railway arches that we use for clubs, theatre, music gigs, exhibitions etc. It is possible (and likely) that there will be more than one event one one day and each event can take up a different combination of spaces.
Due to the nature of our venue I include timings for general information only. They change all the time and are often not fixed until a couple of hours beforehand.
So I am looking for a query that will tell me all instances where there is more than one event on the same day but only when they both have a 'yes' in the check box against one or more of the same spaces.
My fields go along these lines:
Event Name
Date
Arch 2 (check box)
Arch 3 (check box)
Arch 4 (check box)...

I used check boxes so I can arrange them on a map - it makes data entry a lot simpler for my users.

So if on the 14th February I have a performance art piece in Arch 2 & 3 and a show in Arch 4 that is fine and I don't need to know about it.
However - if I have a club in 3 & 4 and a meeting in Arch 2 & 3 then there is potentially a problem.
That is the basic idea - except there are a lot more spaces and a whole lot of other fields for information - contact names, ticket prices etc.

I hope I am explaining this OK.

Anyway, any help would be much appreciated. I have no formal training in this (obviously) but I do know a bit of code and I'm happy to learn.

Many thanks,

Fiona

PS Hope everyone got heaps of cards from the postie this morning!

 

[David R]

Hmmm, one solution occurs to me, but it may require some restructuring of your data:

Here's how my sample example came about. Fill in your other fields as necessary:

tableEvents
-----------
PK: Autonumber
EventName: Text

tableArches
-----------
PK: Autonumber
EventID: Number, Long Integer
Arch: Text, 1 space, Validate as appropriate.
DateField: Date/Time, Format as Short Date

In Tools>Relationships, join tableEvents' PK to tabelArches' EventID, One-to-Many, enforce RI and Cascade Update/Delete.

Now go back to tableArches in Design View. Click on Indexes on the tool bar (it's right next to the Key symbol). Create a new index, name it whatever you like. You're going to add two fields to it: Arch and DateField, but you only need to 'name' it on the first line. Check the 'Unique' box, and you've made a multi-field index. You can trap the error (it's #3022 I believe) and display a customized message instead of the verbose default Access message.
Look up 'error 3022' in the archives to find something that works for you.

Hope that helps,
David R

 

[KKilfoil]

Are you trying to PREVENT double-booking an arch, or trying to get a report of when the double-booking has taken place?

 

[KKilfoil]

If you are looking for a report of double-bookings...

Firstly, your structure is non-normalized, which is usually a no-no in db design, but I see how separate check-boxes on a 'map' can be useful to users, and I do not see how to achieve this in a normalized structure.

Create a summary query for your table, grouped by [EventDate]. I'd suggest NOT using 'Date' for a fieldname because it is also a function name and could lead to problems.

Include each arch field in the summary query, and select Count as your summary option.

Whenever the results of any arch field in this query is other than '0' or '1', you are double booked.

You can reduce the listing to only those that are a possible problem by creating a SECOND query (a select query this time) using the first query as the recordsource. Put '>1' (without the quotes) as criteria for each Count field, but in DIFFERENT rows, to achieve an 'Or' grouping. If you put multiple criteria on the same row, you get 'And' grouping, which you do not want here.

{edited for clarity}

[This message has been edited by KKilfoil (edited 02-15-2002).]