event ticket management database

[kmacpher]

Hello,

I am a novice at Access and am trying to deisgn a database to record ticket
orders for a sporting event. I am wondering if it is possible to set up a
field where the seat numbers can be chosen from a drop down list but only be
chosen once so the same seat can not be sold twice.

 

[woodglass]

Hello,

I am a novice at Access and am trying to deisgn a database to record ticket
orders for a sporting event. I am wondering if it is possible to set up a
field where the seat numbers can be chosen from a drop down list but only be
chosen once so the same seat can not be sold twice.

yes, quite easily

 

[j_beverly]

Hello,

I am a novice at Access and am trying to deisgn a database to record ticket
orders for a sporting event. I am wondering if it is possible to set up a
field where the seat numbers can be chosen from a drop down list but only be
chosen once so the same seat can not be sold twice.

I'm not an expert but here's how I would do it:

I assume you have a table with a record for each of your seats. You
should have a field on the table that indicates whether the seat has
been sold: (SealSold yes/no).

If you use the Access Wizard to put a drop down list (combo box) on a
form, it will have a Row Source (under the "Data" tab) that populates
the list. For example if your table name is "Seats" and your field
name with the number is "SeatNumber", it will be something like:
SELECT Seats.SeatNumber FROM Seats;

You can change that to:
SELECT Seats.SeatNumber FROM Seats WHERE SeatSold=False;

This will list only the seats that have not been sold.
When you sell a seat, you will need to update the SeatSold field to
"yes" or "true" and then refresh the form data so that seat will no
longer show up in your combo box. (You can do all that automatically
with the click of a button or whatever action you do on your from when
you sell a seat.

Alternatively, you can still list all seats in the combo box, but add
the SealtSold column to the combo box that shows whether the seat has
been sold and give an error message if you attempt to sell the same
seat twice.


Jeff Beverly

 

[kmacpher]

How would you suggest doing this?
--
kathyg

yes, quite easily

 

[Larry Daugherty]

Hi Kathy,

If you post back, tell us what you already have. Our answers quite
often depend on knowing where you are in your development and what
you've done to get there. Another thing that would help in "newbie"
questions is your motivation. Do you simply want a quick and simple
solution to your present dilemma or are you committed to learning
Access and this is just a project along the way?

To your issue:

A separate table is required for the seats. Each record in that table
needs a primary key (I recommend Autonumber); SeatName, Text (even
though you see it as number); IsAssigned, Yes/No; Note, text

tblSeat will be the many side of a one-to-many relationship with your
~main table.

The most useful paradigm for dealing with those one-to-many
relationships is with a form/subform construct. Access Help and the
supplied wizards are very helpful in getting this done.

You need a means in your order form to show seats by: All, Unassigned,
Assigned. People might call in after an initial order and request
adjacent seat(s). To accommodate you might have to de-assign their
earlier assignment and find a block of unassigned seats that meet the
new requirement.

If you just want an immediate solution to your present issue then I
suggest googling on related terms to find candidates. Also google on
terms like "demo software". You'll get a lot of hits. I know that
www.simtel.com has a lot of demo and freebie stuff.

If you want to get into Access beyond "newbie"{ status then I suggest
that you continue to lurk these Access newsgroups. In particular:

microsoft.public.access.gettingstarted
microsoft.public.access..tablesdesign

Also, pay a visit to www.mvps.org/access and spend some time there.
Read what you can understand and pass by what you can't.

Welcome to the world of Access!

HTH

 

[kmacpher]

Hi Larry,

Thank you so much for your help! I am a beginner but really want to learn
Access. My employer is sending me to training but not until after the first
of the year and I need to get this database completed this week. The websites
you referred me to have been helpful. I have set this up based on an example
in the Access for Dummies book. I do have a few more questions relating to
this but I'll first try to explain what I have set up thus far.

My main table is my Orders table it contains these fields - Order #,
Customer#, OrderDate, TotalPrice, TicketRequests, PaymentMethod, CreditCard#,
CCExp.Date. My other tables are....
Customers including Cust#, FirstName, LastName and other general contact
info fields.
Order Details containing OrderDetailId, Order#, ProductName, Quantity, and
PriceEach.
Products containing ProductID, ProductName (Includes Ticket Book, Parking
Pass, Motorhome Pass, Program & Processing Fee), and ProductPrice.

I have now added the table Seat with fields SeatID, SeatNameMass,
SeatNameCan, IsAssigned, and Notes.

And the table Seat Details with fields SeatDetail#, Order#, and SeatID (a
combo box that shows the two seat name fields and IsAssigned).

OK, as you can see we are selling other products besides just the seats
(parking pass, program, etc.). The Ticket Book includes tickets to all 6
games of the event. The games are being played at two stadiums so for the
Mass stadium a ticket book would include a seat in Sect 3, Row G, Seat 6 and
at Can stadium seat Sect 12, Row G, Seat 6. That is why I have two seat name
fields in the seat table.

I have a form set up with the data source table Orders. I have a subform for
the table SeatDetails.

When I bring up the Seat I am selling (in a combo box) - how can I mark it
that it is sold? Should I make some sort of query so it only shows the seats
available and pull the subform from there? Also is there a way to program the
combo box to display all of the data instead of just that from the first
column for the record you have selected?

I also have a subform for the Order Details table where I can select the
quantity of the products order (i.e. 3 parking passes). Is there a way to
make it take the quantity I have entered and multiply it by the product price
to give me a total for each product?

I'm apologize for the length of this post. Any help you could give me would
be greatly appreciated!

Thanks again!