Design for Raffle Ticket Database

[Kelly]

Hi, I'm am trying to creat a database for a raffle our school has every year.
I made one last year, but I wasn't happy with it, so I'm trying to start
over this year. I need some help, so I'm going to tell you what I need, what
what I think I need to do, and any responses would be very appreciated. I am
a beginner, and don't do this often enough to remember what I did last time.

I want to make 3 tables.
The first one will be staff names and personal info. I need this table
because I need to keep track of which staff members sell tickets to help with
the incentive program we have to go along with this. I will call this staff.
Primary key will be staffnumber.

The second table will be called purchasers. This table will hold
information of who purchased the ticket. The primary id will be a generated
purchasernumber.

The third table will be called tickets. This is a table with ticket numbers
and date sold. I have 1000 ticket numbers (07-0001 thru 07-1000) I want to
keep the 07 so that I can keep the same table from year to year and just
change to 08, 09, etc. This number is actually being entered in text form,
just like a phone number, and I have them pre-filled, since I know what they
are.

I am having problems creating relationships. Can anyone tell just by
looking at what I have why this isn't working. I have tried so many
different versions of relations and none of them work. The ticket may or may
not be connected to a staff member, but will definitely be connected to a
purchaser. I have to start by assigning each staff member 5 tickets so that
I can pass out tickets and keep track of who has what numbers. Then, the
staff member will return what they have sold and I will enter who purchased
them.

I know this is a long post, but I want to make sure I don't leave any
details out so that you can help me. So, where have I gone wrong?

 

[Amy Blankenship]

Hi, I'm am trying to creat a database for a raffle our school has every
year.
I made one last year, but I wasn't happy with it, so I'm trying to start
over this year. I need some help, so I'm going to tell you what I need,
what
what I think I need to do, and any responses would be very appreciated. I
am
a beginner, and don't do this often enough to remember what I did last
time.

I want to make 3 tables.
The first one will be staff names and personal info. I need this table
because I need to keep track of which staff members sell tickets to help
with
the incentive program we have to go along with this. I will call this
staff.
Primary key will be staffnumber.

The second table will be called purchasers. This table will hold
information of who purchased the ticket. The primary id will be a
generated
purchasernumber.

The third table will be called tickets. This is a table with ticket
numbers
and date sold. I have 1000 ticket numbers (07-0001 thru 07-1000) I want
to
keep the 07 so that I can keep the same table from year to year and just
change to 08, 09, etc. This number is actually being entered in text
form,
just like a phone number, and I have them pre-filled, since I know what
they
are.

I am having problems creating relationships. Can anyone tell just by
looking at what I have why this isn't working. I have tried so many
different versions of relations and none of them work. The ticket may or
may
not be connected to a staff member, but will definitely be connected to a
purchaser. I have to start by assigning each staff member 5 tickets so
that
I can pass out tickets and keep track of who has what numbers. Then, the
staff member will return what they have sold and I will enter who
purchased
them.

Try this:

tblPeople
PersonID
FirstName
LastName
CanSell
PersonEtc.

tblTicketYears
TicketYearID
TicketYear
TicketPrice

tblTickets
TicketID
TicketNum
TicketYearID

tblTransactions
SellerID
BuyerID
TicketID

Now, TicketYearID is the Primary key in tblTicketYears and the FK in
tblTickets. TicketID is the Primary Key in tblTickets and the FK in
tblTransactions.

PersonID is the PK in tblPeople, and joins to tblTransactions twice, first
as the SellerID and then as the BuyerID. When designing your forms, you'll
want to use something like a combobox on tblPeople limited to people who
CanSell. Anyone can buy, so you don't want to restrict the list there.
However, if you want to presume that sellers won't also buy, you could
restrict it that way.

HTH;

Amy