Database design in access

[nick2price]

Hi everyone, new here and a complete sql newbie.
I have been working on this now for four days, and i am so close to having a nervous breakdown, lol. My scenario is for a swimming meet, this is what i came up with.

A meet will have one or more events (e.g., 50 m freestyle, 4x100 freestyle relay, etc.).

An event will have one or more heats.

A heat will have one or more individuals entered.

Each individual will have a time associated with their heat.

So i created my Tables as so:

Meet(meetID(PK), meetName)
Event(eventID(PK), meetID(FK), eventName)
Round(roundID(PK), eventID(FK), roundNumber)
Competitor(CompetitorID(PK), name)
RoundCompetitor(roundID(fk), competitorID(fk), timeSet)

I have been unable to put an Enforce referential Integrity on any of the relationships as when it through java with jdbc, it complains. Hope this doesnt matter. You can see my relationships underneath. All my primary Keys besides CompetitorID, are autonumber. The problem is that when i try to create a query on two or more tables, i am being returned no data at all. For instance, i want to retrieve the 3 fastest times(timeSet), for Round_1 (roundNumber), for the 100M Frontcrawl (eventName). Is all my tables correct, and all their relationships correct? And what would the query i want to perform look like in access?
Cheers for any help you can offer and thanks for having me on this site.

 

[Pat Hartman]

I would make a lookup table to define event types. That way you can use combos rather than having to type the name and you will ensure consistancy should you want to do any reporting.

Event(eventID(PK), meetID(FK), EventTypeID)
EventType(EventTypeID(PK), eventName)

You need to figure out what is wrong with your queries in Java. Referential integrity is important.

 

[nick2price]

Is it possible to set two fields as a primary key in the same table in access?
Or is it possible to see the sql code of my created tables?
Cos i need to create a table simular to this and i have no idea how to do it in access, How can i set two primary keys and how do i set foriegn keys and references?
create table if not exists results
(
competitor_id int not null,
event_id int not null,
result double not null,
primary key(competitor_id, event_id),
foreign key(competitor_id) references competitors(id) on delete cascade,
foreign key(event_id) references events(id) on delete cascade
);

 

[neileg]

You can't have two primary keys, but you can use two fields to create a compound PK.

Tables don't have SQL code so I don't know what you mean.

I've been building Access applications for 10 years and I have never used a make table query. I always worry about proper design when they get mentioned. I do use temporary tables by which I mean the tables are permenant but data is held there temporarilly and then deleted.

 

[nick2price]

k, get you. just one last question. When i run this in java, i get return the error,
sun.jdbc.odbc.JdbcOdbcBatchUpdateException: [Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'tblResults'.

This is to do with me needing to tell it where to get the value for the foriegn key. If i was doing this in mySql, i would do somthing like
insert into events(name, meet_id) select '100 yard dash', id from meets where name = 'melrose games';
where i am telling it where to get meet_id from. How is this done in access?