Design of ticket sales database

[access_novice]

I am a novice where Microsoft Access is concerned. I am retired (age
63) and have been asked to design a ticket sales database tracking
customers, peformances/events, tickets sold, reserved seating and
associated reports. This project is for a non-profit community theater
and I am attempting this project as a volunteer - no salary or
compensation of any kind.

I have designed the base tables (customers; seating; performaces;
pricing; etc.) and they work.

My problem stems from attempting to design a functioning specific
assigned seating (row and column format) and having the sold seats
decrement from the master list by row and seat.

The data entry forms work well, i.e., I can assign seat numbers and
rows and link them to customers - but I'm unable to find a method
through which to increment/decrement assigned seats from the master
seat listing. This is a critical component of the design, as the
operator of the database will need to know which seats are still
available (unsold) for the next customer.

It is quite possible that this forum is not the place to ask these
questions - and if so, please accept my apology for injecting my
questions to the group.

I would be most appreciative if anyone can direct me to a forum or
other locale which might afford me some insight and assistance in
completing this project.

Thank you for any assistance you may be able to provide.
Sincerely,
Access_Novice

[rkc]

access_novice wrote:

I am a novice where Microsoft Access is concerned. I am retired (age
63) and have been asked to design a ticket sales database tracking
customers, peformances/events, tickets sold, reserved seating and
associated reports. This project is for a non-profit community theater
and I am attempting this project as a volunteer - no salary or
compensation of any kind.

I have designed the base tables (customers; seating; performaces;
pricing; etc.) and they work.

My problem stems from attempting to design a functioning specific
assigned seating (row and column format) and having the sold seats
decrement from the master list by row and seat.

<snip>

The solution is to base your input forms on queries that don't
show what isn't available. Noone can give you specifics on how
to implement that without you giving more information about
the structure of your tables.

[Tony Harvey]

Presumably you have a "sold" column somewhere that you can tie in to the
customer and his seat allocation. Run a query that can show seat row and
column numbers as well as the sold field and just filter it by typing
into the the criteria of the field Is Null. So long as you leave this
sold field empty until sold the query will only show available
combinations. I agree with last answer also though, I am only guessing
at the structure and putting some ideas forward. Good luck

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Albert D. Kallal]

You have the master seat listing, and that never changes, nor does it need
to be modified in anyway

(unless the building is being renovated!!!).

So, assuming you assigned some seats to some people for a PARTICULAR event,
then
you an simply query the system to get all seats assigned to those people for
the particular event.

So, if seat 34 is taken, then you might have (this is air code that probably
could actually run, but
is only for instruction here).
dim strWhatSeat as string
dim rstRecs as dao.recordset

' lets assume you have alerady selected what event/show

lngEventId = 123 ' this is the event that was selected

' now, what seat?
strWhatSeat = inputbox("what seat")

strSqlWhere = "EventID = " & lngEventId & " and SeatNum = " & strWhatSeat

if dcount("*',"tblBooking",strSqlWhere) > 0 then
msgbox "sorry, that seat is booked"
end if

So, the idea, or logic here is to simply search/look at existing bookings
for the particular event, and check if the seats have been assigned.

This means your design approach never have to actually modify, or change
anything in the seats listing table. You only look at the particular event,
and check if those seats are taken in that event. This design approach means
you don't have to create "slots", or a list of records with the seat list,
but only have a master seating list that gets used over and over for each
event. Each time you book people into a particular event, you assign a seat
value, but always check/ensure that the seat is not yet sold...

It is not at clear what your table designs are now, but I think the above
ideas and concepts will help you.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal