Question Locked

MS Access Database

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm VERY new to using MS Access. Am trying to design a simple ? database for a local Dog Trial Club to administer their dog trial events. They need to enter competitor details, then event entry details including name of each dog (each competitor can have multiple dogs). The events are under two categories, HEADING and HUNTAWAY. E.g. If entering a Heading dog, then can choose to enter either/both of Event 1 & Event 2. Under each event they then select a class e.g. Open, Intermediate, Maiden or combination of these classes.

Likewise a competitor with several Huntaway dogs, can enter either Event 3 or Event 4 or both. Same classes apply.

I'm having trouble getting to a point were I can then print a report (entry summary) listed by competitor each dog he entered and the events entered ? Need to show Heading and Huntaway events separately on the same report (grouped ?).

Could anyone outline the table structure and relationships so above report can be produced. I would be very grateful.

Many thanx
Russell

5 replies

by tony hopkinson

But I'd have

Competitors

Dogs Linked to competitors

EventTypes

Events linked to EventTypes

Entries which links logs and events

So then the query for the report would be
something like
Select CompetitorName,DogName,EventName,EventTypeName
From Competitors,Dogs,Events,EventTypes,Entries
Where
Dogs.CompetitorID = Competitors.CompetitorID
and
Events.EventType = EventTypes.EventType
and
Entries.DogID = Dogs.DogID
and
Entries.EventID = Events.EventID

HTHs

Leave the Heading and Huntaway split to the dataentry logic.
If the event data is radically different, try putting the common stuff in events and then having two sub tables which you'll only need when you know what sort of event has been picked.

It's a scheme I like but works best whre you never need to get data specific to a class of event at the same time.

by russ.julie

Tony, excuse my simplicity but I'm really a novice at this.

To clarify fields in the following tables:
tbl Events
EventsID
EventName - (only 6 possibilties - 3 UNDER EACH EventType)
EventTypesID (FK)- (only 2 types, Heading & Huntaway)
tblu Entries (junction?)
EntriesID
DogID (FK)
EventID (FK)

by russ.julie

Tony, I think this structure if I have understood you correctly ?! requires me to input dogs name 2x if it is entering Event1, Event2.

What I really want is to have an entry form which requires dog name entered once and individual fields for Event1 and Event2 and if entering either or both the lookup in these fields allows user to see drop down list showing the applicable classes, ie Open, Intermediate, Maiden, NotEntered etc.

So the entry shows dog name and Event1, Open Event2, Intermediate.

Is this wrong, ultimately after all entries in we then need to list who is entered in say Event1,Open and their scores ranked best to worst.

Await your views if happy to guide me.

Thanx

Russell
PS, Where id North,GB ?

by tony hopkinson

I was thinking
Add Owner Name, Address ...
Add Dog To owner Dog name , breed, ...
Add Events ClassID levelID time date name, ...
Add entry dogid eventid, score ...

If you always have three levels for each class of event, then you could put level in the entry record.

So one the entry form

Owner Fred
Drop down to select dog
Drop down to select type of event
Drop down to select upcoming events of that type
Choose a level to enter that dog at.

Something like that anyway.

Scunthorpe as in up North, work in Manchester though.

Won't be entering my dog, he misinterpreted the label on his first tin of food.
Dog Meat :D
Should have called him Hannibal

This discussion is closed to new replies.