Primary Key & Data Entry Forms

[Sue Wilkes]

I am trying to create a database for entries for a dog show the following is
how I have set it up so far
TABLE - DOG_DETAILS - Fields - Registered Name, Breed, Sex, Date of Birth,
Breeder, Sire, Dam, Class_1, Class_2, Class_3, Class_4, Class_5
TABLE - CLASSES - Fields - Class_No, Class_Name (this is used as a lookup in
Dog_Details)
TABLE - SEX - Fields - Male, Female (this is used as a lookup in Dog_Details)
TABLE - CONTACT_DETAILS - Fields - Date of Entry, Title_1, First_Name1,
Surname1, Title_2, First_Name2, Surname2, House_No_Name, Address_1,
Address_2, Town_City, County, Postcode, Telephone_No, Email, Catalogue_Entry
TABLE - TITLE - Fields - Mr, Mrs, Miss, Ms, Other (this is used as a lookup
in Contact_Details
TABLE - REVENUE - Fields - First_Entry (this is always £2.00 per dog, there
can be multiple entries of dogs per exhibitor), Subsequent_Entries (with the
same dog, if selected always 50p per additional entry), Not_For_Competition
(if selected always £1.00), Membership (if selected could be Single £1.00,
Joint £1.50, Family £2.50), Prepaid_Catalogue (tick box if selected the value
which is always £1.50 should appear in field PrePaid_Catalogue_Value),
Total_Revenue
TABLE - Membership - Fields - Membership_Name, Membership_Value (this is
used as a lookup for Table Revenue.

The only constant per entry would be the Registered Dog Name would it be a
good idea to use this as the primary key and link to the other tables using
foreing keys? If not should I use Alan Browne auto number routine? My aim
is to enable data entry into a form (made from the many tables, which I think
I will need a query to build the form?) to produce a database which contains
all the data which can be queried and reported from. Do I build a complete
table in the database which contains all the entered data or let is reside in
the individual tables as setup? I am not very good at linking tables with
relationships.

Any help would be appreciated - Many thanks from this beginner

 

[Allen Browne]

Sue, you will need at least these tables (as well as the lookups for Sex,
Title, AnimalType, FeeType, etc):

Exhibitor table (one record for each dog owner)
ExhibitorID AutoNumber primary key
ExhibitorSurname Text
Address Text
...

Animal table (one record for each animal to be exhibited)
AnimalID AutoNumber primary key
AnimalTypeID Text "dog", "sheep", ...

Event table (one record for each show where dogs can be entered)
EventID AutoNumber primary key
EventDate Date/Time
LocationID where this show is

Class table (one record for each class you can enter a dog into)
ClassID AutoNumber primary key
EventID Number which show this class is at
ClassName Text
AnimalTypeID Text what kind of animal this class is for

Entry table (one record for each exhibitor who shows at an event)
EntryID AutoNumber primary key
ExhibitorID Number who is exhibiting
DateRecd Date when you received this entry.

EntryAnimal (one record for each animal entered in a show)
EntryAnimalID AutoNumber primary key
EntryID Number which entry (defines exhibitor & show)
AnimalID Number which animal
ClassID Number which class the animal is entered in.
EntryFee Currency how much the exhibitor show pay to enter this
animal in this class.

You will have a main form bound to the Entry table. It will have combos for
selecting who is exhibiting, and a subform for that person's entries in the
show. The subform will be in Continuous Form view, so it has multiple rows.
In each row, you choose the animal being entered, and the class they are
entered into. You might also want a FeeType combo box in the subform, so you
can choose the type of fee charged on this line.

 

[Sue Wilkes]

Allen, Many thanks for your assistance but I need to clarify some items.
I will be using a new database for each show, we only run two a year so I
wont need the event table. There is only one type of animal at the show -
dogs - but could I use the animal table for the following.

In dog showing there are 7 groups toy, utility, working, terrier, hound,
pastoral and gundog. Each group has its own breed list i.e. in toys there
are poms, chihuahuas etc in utility there are kleins, keeshond etc. how could
I get the tables setup so that on the data entry form the user selects a
group which then provides a list of the breeds for the user to select from.

The manual entry forms we receive will have one exhibitor but could have
many dogs entered for that exhibitor. Also on the revenue side I would like
to be able to do the following on the entry form
First Entry Per Dog - default to £2.00 but allow user to overwrite if more
than one dog is entered.
Subsequent Entries always 50p each class - default 50p but again allow user
to overwrite if more than one additional class is selected.
NFC - this could be a yes/no and if selected it would always be £1.00 I
would like the value to default to this if selected.
Membership - I have setup a table for membership containing two columns -
membershipname and membershipvalue - single is £1.00, joint £1.50 and family
£2.50 how could I get the default value for each name to appear?
Pre-Paid Catalogue - this again could be a yes/no if selected I would like
£1.50 to appear automatically.

Finally I would like a Total Revenue Column where all the above values if
pertinent to that exhibitior are then addedup this should then match the
cheque amount I have rececived, but if it doesn't I would like to alter the
amount and add a comment to the record.

Many thanks your assistance is invaluable - Sue Wilkes

 

[Allen Browne]

Comments in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Many thanks for your assistance but I need to clarify some items.
I will be using a new database for each show, we only run two a year so I
wont need the event table. There is only one type of animal at the show -
dogs - but could I use the animal table for the following.

Okay, you know your data. I would expect that it would at least be worth
having next year's show in the same database, as it would save typing many
of the exhibitor's details again. Therefore there could be many shows in the
database over the years, even if it's for one location only.

In dog showing there are 7 groups toy, utility, working, terrier, hound,
pastoral and gundog. Each group has its own breed list i.e. in toys there
are poms, chihuahuas etc in utility there are kleins, keeshond etc. how
could
I get the tables setup so that on the data entry form the user selects a
group which then provides a list of the breeds for the user to select
from.

Assuming that any breed belongs to one group only, these tables:
BreedGroup table (one record for each group), with BreedGroupID primary key.

Breed table (one record for each breed of dog), fields:
BreedID primary key
BreedGroupID relates to BreedGroup.BreedGroupID

The manual entry forms we receive will have one exhibitor but could have
many dogs entered for that exhibitor.

If you don't have an exhibitor entering in multiple shows, you can skip this
and use a single table for the exhibitor info and the entry in the show. You
still need the EntryAnimal table (or something like it), to handle the fact
that one exhibitor can enter many animals.

Also on the revenue side I would like
to be able to do the following on the entry form
First Entry Per Dog - default to £2.00 but allow user to overwrite if more
than one dog is entered.
Subsequent Entries always 50p each class - default 50p but again allow
user
to overwrite if more than one additional class is selected.
NFC - this could be a yes/no and if selected it would always be £1.00 I
would like the value to default to this if selected.
Membership - I have setup a table for membership containing two columns -
membershipname and membershipvalue - single is £1.00, joint £1.50 and
family
£2.50 how could I get the default value for each name to appear?
Pre-Paid Catalogue - this again could be a yes/no if selected I would like
£1.50 to appear automatically.

I do think it would be better to have a FeeType rather than the check boxes.

In any case, the actual fees could change in the future, so you want these
fees in a table, instead of hard-coding them in VBA code or macros.

Finally I would like a Total Revenue Column where all the above values if
pertinent to that exhibitior are then addedup this should then match the
cheque amount I have rececived, but if it doesn't I would like to alter
the
amount and add a comment to the record.

You don't have a Total column in the table.
You sum the Fee column in the subform.
Just add a text box to the Form Footer of the subform, and set these
properties:
Control Source Sum([Fee])
Format Currency