Re: Primary Key & Data Entry Forms
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Mon, 25 Feb 2008 22:38:33 +0900
Comments in-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:EB47C08C-EBB2-4E20-A844-2DD11FAE664D@xxxxxxxxxxxxxxxx
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
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.
"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.
.
- References:
- Primary Key & Data Entry Forms
- From: Sue Wilkes
- Re: Primary Key & Data Entry Forms
- From: Allen Browne
- Re: Primary Key & Data Entry Forms
- From: Sue Wilkes
- Primary Key & Data Entry Forms
- Prev by Date: Re: Primary Key & Data Entry Forms
- Next by Date: cascading delete
- Previous by thread: Re: Primary Key & Data Entry Forms
- Next by thread: Re: Access Autonumber NOT Unique
- Index(es):
Relevant Pages
|
