Looking for input from you

K

Kelly

I am creating a new database today. I am still new at creating so I need
some input.

Our school is having a raffle. I am assigning raffle tickets to staff to
sell. So, I am thinking that I want to make 3 tables. 1 for staff, 1 for
ticket numbers, and 1 for purchasers. I am stumped on relationships of
these. My staff table would have a staff number and my purchaser table could
have a purchaser number. (Is this a one-to-many? I don't think I've done
that before.)

This is how I want to use it in the end. I want to keep track of ticket
numbers that are assigned out staff and I want to update the ticket numbers
to who bought them.
 
T

Tom Ellison

Dear Kelly:

Since you seem to want to keep an identity for each ticket, I guess this is
what you need.

The logic would seem to be that each ticket is sold by a staff member and
purchased by a purchaser. This would imply there would be two one-to-many
relationships, one between staff (foreign) and ticket, the other between
purchaser (foreign) and ticket. That would give you what you want.

However, it may be that a certain staff member will sell, say, 100 tickets
to the same purchaser. It would be efficient to allow for a range of ticket
numbers in the record for tickets, rather than have one row for each ticket.
This would entail a couple of advanced challenges, so it may not be so easy
to implement.

- you'd have to keep the effective "identity" of each ticket. Each "range
of ticket numbers" would have to be mutually exclusive (no overlaps!) and
that would be fun to implement. Well, I can certainly be done. If you want
a simple solution, and the methods for doing that aren't already apparent to
you, maybe you should leave this alone.

You may still want to allow the system to record multiple sales in a simple
way. If Mary Staff sells tickets 1230 through 1247 to Jack Snell, wouldn't
it be nice to just enter the range on the screen one time, rather than 18
individual sales? The form could allow for a starting and ending ticket
number, and confirm the number of tickets (much like when purchasing
tickets, above.) It's a trade-off of programming time to data entry time.

My tentative proposal for having "ranges" of tickets rather than individual
tickets in the ticket table was for this reason. However, you would have
only one row to start, with tickets 1-9999. When you sell tickets 1230
through 1247, it would have to then have 3 ranges: 1 to 1229 (unsold), 1230
to 1247 (sold by Mary to Jack), and 1248 to 9999 (unsold). There's some
complexity to programming this.

So, I visualize this order of events:

1. Purchase tickets. A form allows you to enter beginning and ending
ticket numbers. Maybe also ask for the total number of tickets, and confirm
that the range contains exactly that number of tickets. It then puts the
rows in the ticket table. The Staff link and Purchaser link are left NULL,
or set to the identity of "not assigned" in both links, that being a "dummy"
row in both Staff and Purchaser tables. When selling tickets, you would
enter a ticket number that can be confirmed to be as yet "unsold". After
entering either Staff or Purchaser records, as needed, the ticket(s) is/are
assigned.

2. Staff members set up in advance as far as known.

3. Ticket sales and corresponding Purchaser records are recorded.

Deciding how the data will function is an important part of table design.
This tests the table design's adequacy. It is a largely mental process.
Don't leave home without it!

Tom Ellison
 
K

Kelly

Tom -
I absolutely love your idea, and I would be so proud of myself if I can do
this. I was thinking it would be easier than this, but I'm up for the
challenge. You're right about needing the range of tickets. So, would you
mind helping me through this process?

First - I have a staff and purchaser tables set up, but I'm stumped on
tickets. I'm thinking ticket number, date, Staff ID, and Purchaser ID. Am I
right?

Stafftable
ID(primary)
F name
L name
department

purchasertable
ID(primary)
F name
L name
address, etc.

So, now the form....hmm, I think my eye has started twitching just thinking
about this one. Can you get me started?
 
M

mnature

For years, I have worked with special tags that needed to be tracked for
auditing purposes. They were numbered consecutively. A very consistent
problem was finding gaps in the numbers. You may want to have some mechanism
in your database for tagging tickets that do not exist (just in case there
are gaps in the numbers).

Much as I like Access, you might be better off using an Excel spreadsheet
for this. Could copy and paste names, use autofill for the numbers, etc.
Might be less confusing for everyone.
 
T

Tom Ellison

Dear Kelly:

My available time to be able to post here varies with business needs. I may
not be available on too regular a basis.

Now, to implement the whole thing I expressed is a fair amount of work and
it is expert level stuff. Be sure you up for that. This may not be a good
"first project" approach if you're new to this. But, if you may want to
develop some "lifelong skills" then it may be a way to start.

How many staff and purchasers would you estimate for this database?

In terms of "address" I'm guessing there's no search requirments, just a way
to see, and maybe print addresses. Don't need to find those in specific zip
codes, or cities, or states. What I'm getting at is that the whole address
can be treated as just a "lump" of information not divisible for any likely
foreseeable purpose.

In laying out tables, keep every column name a single word. No spaces or
punctuation. Use notation like this:

LastName
FirstName

As you get more advanced in writing queries, this will be so much more
convenient. There's no drawback to using this kind of convention, and some
advantage.

For a modest size database, I'd recommend dropping the IDs, and put the
whole name in one column, not splitting first and last. Is there any
function where you expect to need to separate them?

When you're assigning a ticket to a purchaser, what do you envision on the
screen? I would thing it would be a combo box of names. This implies every
name must be unique. Otherwise, how will a user pick the right one? If
there are two of them that say John Doe, how would a user pick the right
one? So, the convention needs to be that, when a ticket is sold, and the
name is common, get a middle initial. If the guy is John A Doe and there is
a John A Doe Jr. in the same area, he would know it. Ask! Otherwise, you
end up with persons you cannot distinguish in the database.

If you didn't have a computer, and there were 2 persons named John A Doe,
you would find a way to distinguish one from the other in normal
conversation. People have found such conventions since before there were
computers, and the same conventions work with computers, too.

Now, you can use an ID for them. But how would that help? Do you want John
A Doe # 765 or John A Doe # 814? You can't ask John. How would he know
what number the computer has assigned to him? No, there has to be a
"natural" way of doing it. I cannot tell you what that would be, for sure.
If the 2 John A Doe fellows are not related, but live in the same area, they
may have faced this difficulty before. Maybe they can tell you a "natural"
way to tell which is which. One is bald, the other not. So, one is John A
Doe, Bald, the other Hairy. I don't know. But I do know this. If you're
tryiing to record which bought ticket 2381, just having a combo box list
with 2 John A Doe names doesn't work. Maybe you'll also show the next line
of the address in the combo box. This may help. However, I would recommend
you get something into the single PName (purchaser name) column that
guarantees uniqueness and at least permits and, hopefully, facilitates
proper identification.

For the smaller (I expect) Staff table, the same considerations apply, but
as there are fewer persons there (again, I expect) it's not so difficult.

The above, I'll warn you, is not a "majority opinion" among experts. Maybe
someone will give you another opinion.

For a few thousand tickets, with perhaps an average of several tickets sold
together to one purchaser by the same staff member, you aren't going to have
a huge number of rows of data. I do recommend this will work well without
an autonumber in the tables. Likely, that's not something you've seen
before. We can discuss this further, and cover any reluctance you have.

Maybe enough for now. Let's see how we are communicating so far. Ask me to
repeat anything, or clarify what I have said. Feel free to guide the
conversation into areas that are troubling you most.

Tom Ellison
 
T

Tom Ellison

Dear mn:

I believe I agree. I learned to create my databases in Excel before
learning Access. No more than 64K rows per table, but it works. Can't
write queries, but you can code around that. If you're already expert in
Excel, and the application fits, go for it. However, if you don't know
Excel or Access, and you want to learn database techniques that are
applicable to much larger projects, it would be more profitable to learn
Access.

Very much opinion!

Tom Ellison
 
K

Kelly

Tom -
Thank you for your thourough explanations. You are a great teacher. Your
second warning of how hard this will be for a novice had me thinking all
night. I have 100 things to do on my desk, one of which is this raffle. How
can I spend the time I will need to learn Access while I need to be doing
other things. So, I need to simplify this for now. I do keep my on the
community college for Access classes, hopefully some day I can take an
evening class. So, here's what I'm thinking....

We may only sell 1000 tickets. This raffle is for a car that was donated to
us and the tickets are $100 each. We did this last year and not too many
people bought more than one, of the ones that bought more than one, most
bought two, and a handful of people bought many. So, as much as I hate
duplicates, I'm thinking to make this easy and quick on myself, I can
duplicate the purchasers instead of having a range. I could later do a query
to remove duplicates later if, say need to make a contact sheet, couldn't I?

Our staff will be given 5 tickets each to sell. Some will come back for
more, some will return unsold tickets. So, I was thinking my first table
could be staff with a staffid as key. I could connect it to a ticket table,
that would have info on the ticket and the purchaser. So, yes, there would
be duplicate purchasers, but I would have time to finish my other work.

Do you see any flaws in this? I'm going to start playing around with it,
(yes, I will plan it on paper first.) I have another database I made similar
to this that I might be able to duplicate.
 
T

Tom Ellison

Dear Kelly:

Given the scale of the raffle, being 1000 tickets tops, and your lack of
comfort with handling the complexities, I agree thoroughly. I was thinking
of something more like a $1 ticket and tens of thousands of them. It is not
possible that a staff member could sell more than 5 to any one purchaser.
That pretty much makes my "high efficiency" ideas inapplicable.

So, you'll be recording them one ticket at a time. That's simple and
straight forward. You should be able to keep a little bit of what I
discussed, such as creating all the tickets even before they're sold. That
part is still good.

Enjoy. Let me know how it goes!

Tom Ellison
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top