Re: Primary Key & Data Entry Forms

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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sue Wilkes" <SueWilkes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:99FC48E3-3F3B-4EFB-BC9A-22AC1D459CBB@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Primary Key & Data Entry Forms
    ... Sue, you will need at least these tables (as well as the lookups for Sex, Title, AnimalType, FeeType, etc): ... Class table (one record for each class you can enter a dog into) ... Entry table (one record for each exhibitor who shows at an event) ... You will have a main form bound to the Entry table. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Primary Key & Data Entry Forms
    ... 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. ... Breed table (one record for each breed of dog), ... 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. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Crystal Reports question (using VB)
    ... You need a "one-to-many" design on your tables. ... > I have a dataset that contains entry information for dog shows. ... > When I create the Crystal Report, I have the groups that work however I need ...
    (microsoft.public.dotnet.languages.vb)
  • Crystal Reports question (using VB)
    ... This is the next step of a bigger project that I am working on and I have ... I have a dataset that contains entry information for dog shows. ... When I create the Crystal Report, I have the groups that work however I need ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Robin and Cala - online video
    ... A lot of folks do teach the a-frame and dogwalk that way, ... I think because you move fairly quickly to having the dog do the whole ... we're doing the full teeter ... is of the full length and not the side entry, that will not be a problem. ...
    (rec.pets.dogs.behavior)
Loading