Help on design?

F

Frank

I am very new to Access database and as a pensioner are studying this on my
own. I have already design a database which works 75% but give problems after
I deleted some records from different tables.
Is there perhaps a way that I can send a diagram of my table structure to
somebody that can look at it and give me some pointers on the design. I can
send the diagram as a jpg file or Microsoft Publisher file or any file which
is suitable.
I would really appreciate this
 
D

Duane Hookom

You can take the time to type the structure into a message like:

tblEmployees
===============
empEmpID autonumber primary key
empFirstName
empLastName
emp ...

tblProjects
================
prjPrjID autonumber primary key
prjTitle text
prjStartDate
prjDescription

tblEmployeeProjects
=================
eprEPrID autonumber primary key
eprEmpID foreign key to tblEmployees.empEmpID
eprPrjID foreign key to tblProjects.prjPrjID
eprRole

The above took me about a minute and should accurately describe my
significant tables, fields, and relationships.
 
F

Frank

Duane Hookom said:
You can take the time to type the structure into a message like:
Click to expand...
Table 1
SWIIM MEET
SwimeetingID
[Primary key Autonumber]
Type [ex club or school] ((text)
[Lookup table H]
Venue (text)
Start time [like 14H00] (text)
Date of meeting (date/time)
Year [calculated from above] (number)
Age date [to determine age of swimmer]
(date/time - formatted to 00/00/0000)
Memo [for relevant information] (memo)
OrganizerID [number]
TeamID [number]
EventID [number]

Table 2
ORGANIZER
OrganizerID [Primary key Autonumber]
Institution (text)
Contact title (text) [Lookup table F]
Contact name (text)
Contact surname (text)
Contact home phone (text)
formatted as 000 000 0000
Contact work phone (text)
formatted as 000 000 0000
Contact cell phone (text)
formatted as 000 000 0000
Contact fax number (text)
formatted as 000 000 0000
Contact personal e-mail (text)
Organization e-mail (text)
Address 1 (text)
Address 2 (text)
City/Town (text)
Code (number)
PO Box (text)
PO Box Code (number)
Head of institution (text)
Memo (memo)
for relevant information

Table 3
TEAMS
TeamID [Primary key Autonumber]
SwimmerID (number)
Institution (text)
Team name - full name] (text)
Team abbreviation (text) [ex. VRED]
Teem title (text) [Lookup table F]
Teem name (text)
Teem surname (text)
Teem home phone (text)
formatted as 000 000 0000
Teem work phone (text)
formatted as 000 000 0000
Teem cell phone (text)
formatted as 000 000 0000
Teem fax number (text)
formatted as 000 000 0000
Teem personal e-mail (text)
Organization e-mail (text)
Address 1 (text)
Address 2 (text)
City/Town (text)
Code (number)
PO Box (text)
PO Box Code (number)
Head of institution (text)
Team payments (currency)
Memo (memo)
for relevant information

Table 4
SWIMMER
SwimmerID [Primary key Autonumber]
TeamID (number)
Swimmer name (text)
Swimmer surname (text)
Birth date (text)
Age (text) [datediff [Birth date].[Age date]
Entry time (text) [formatted as 00:00,00]
Event time (text) [formatted as 00:00,00
Heat (text)
Lane (text)
Position (text) [determine by query]
Points (text) [determine by query]
Language (text) [lookup table E]
Comment (text) [lookup table I]
[Absent or Disqualified]
EventID (number)
[from lookup G and three different query's]

Table 5
EVENT
EventID [Primary key Autonumber]
Event number((number)
Distance [lookup table A]
Swim stroke [lookup table B[
Gender [lookup table C[
Age group [lookup table D]
RecordID (number)

Table 6
RECORD
RecordID [Primary key Autonumber]
EventID (number)
[lookup event - it will be the same]
Record number((number)
Record holder name (text)
Record holder surname (text)
Time (text) [formatted as 00:00,00]
Team name (text)
Team abbreviation (text)
Record year (text)

Releationships:
Table1 OrganizerID to Table2 OrganizerID
Table1 TeamID to Table3 TeamID
Table1 EventID to Table5 EventID
Table3 SwimmerID to Table4 SwimmerID
Table4 EventID to Table 5 EventID
Table 5 Event ID to Tabell RecordID

At the end of the meet the database would usually be saved and used the next
year. Sometimes the teachers want to change events and this must then be
necessary. With the start of a new event, example a club meet and not a
school meet then I want to be able to delete the records in the following
tables: Teams, Swimmer, Events, Records as to start a new meet with other
events, records, teams and swimmers, This happens quit a lot because they
organize more than one different meet each year. The points of each team are
calculated by a query from the table Points [lookup G] which contain the
following three fields: Swim meet, Placed [first second ect} and, Points
[which are allocated to each place before the meeting start]

Thanks Duane, but I told your this is a lot to read
I really appreciate your help and effort and trouble reading through this

Kind regards
 
D

Duane Hookom

It looks like you are storing values that can be calculated. There is no
reason to store a year if you have the date. Also, I wouldn't store swimmer
participation information in the swimmer table. All fields from about the 6th
field and after in the SWIMMER table should be in a SwimmerEvent table. I
would expect a single swimmer could participate in multiple events.

--
Duane Hookom
Microsoft Access MVP


Frank said:
Duane Hookom said:
You can take the time to type the structure into a message like:
Click to expand...
Table 1
SWIIM MEET
SwimeetingID
[Primary key Autonumber]
Type [ex club or school] ((text)
[Lookup table H]
Venue (text)
Start time [like 14H00] (text)
Date of meeting (date/time)
Year [calculated from above] (number)
Age date [to determine age of swimmer]
(date/time - formatted to 00/00/0000)
Memo [for relevant information] (memo)
OrganizerID [number]
TeamID [number]
EventID [number]

Table 2
ORGANIZER
OrganizerID [Primary key Autonumber]
Institution (text)
Contact title (text) [Lookup table F]
Contact name (text)
Contact surname (text)
Contact home phone (text)
formatted as 000 000 0000
Contact work phone (text)
formatted as 000 000 0000
Contact cell phone (text)
formatted as 000 000 0000
Contact fax number (text)
formatted as 000 000 0000
Contact personal e-mail (text)
Organization e-mail (text)
Address 1 (text)
Address 2 (text)
City/Town (text)
Code (number)
PO Box (text)
PO Box Code (number)
Head of institution (text)
Memo (memo)
for relevant information

Table 3
TEAMS
TeamID [Primary key Autonumber]
SwimmerID (number)
Institution (text)
Team name - full name] (text)
Team abbreviation (text) [ex. VRED]
Teem title (text) [Lookup table F]
Teem name (text)
Teem surname (text)
Teem home phone (text)
formatted as 000 000 0000
Teem work phone (text)
formatted as 000 000 0000
Teem cell phone (text)
formatted as 000 000 0000
Teem fax number (text)
formatted as 000 000 0000
Teem personal e-mail (text)
Organization e-mail (text)
Address 1 (text)
Address 2 (text)
City/Town (text)
Code (number)
PO Box (text)
PO Box Code (number)
Head of institution (text)
Team payments (currency)
Memo (memo)
for relevant information

Table 4
SWIMMER
SwimmerID [Primary key Autonumber]
TeamID (number)
Swimmer name (text)
Swimmer surname (text)
Birth date (text)
Age (text) [datediff [Birth date].[Age date]
Entry time (text) [formatted as 00:00,00]
Event time (text) [formatted as 00:00,00
Heat (text)
Lane (text)
Position (text) [determine by query]
Points (text) [determine by query]
Language (text) [lookup table E]
Comment (text) [lookup table I]
[Absent or Disqualified]
EventID (number)
[from lookup G and three different query's]

Table 5
EVENT
EventID [Primary key Autonumber]
Event number((number)
Distance [lookup table A]
Swim stroke [lookup table B[
Gender [lookup table C[
Age group [lookup table D]
RecordID (number)

Table 6
RECORD
RecordID [Primary key Autonumber]
EventID (number)
[lookup event - it will be the same]
Record number((number)
Record holder name (text)
Record holder surname (text)
Time (text) [formatted as 00:00,00]
Team name (text)
Team abbreviation (text)
Record year (text)

Releationships:
Table1 OrganizerID to Table2 OrganizerID
Table1 TeamID to Table3 TeamID
Table1 EventID to Table5 EventID
Table3 SwimmerID to Table4 SwimmerID
Table4 EventID to Table 5 EventID
Table 5 Event ID to Tabell RecordID

At the end of the meet the database would usually be saved and used the next
year. Sometimes the teachers want to change events and this must then be
necessary. With the start of a new event, example a club meet and not a
school meet then I want to be able to delete the records in the following
tables: Teams, Swimmer, Events, Records as to start a new meet with other
events, records, teams and swimmers, This happens quit a lot because they
organize more than one different meet each year. The points of each team are
calculated by a query from the table Points [lookup G] which contain the
following three fields: Swim meet, Placed [first second ect} and, Points
[which are allocated to each place before the meeting start]

Thanks Duane, but I told your this is a lot to read
I really appreciate your help and effort and trouble reading through this

Kind regards
Click to expand...
 
F

Frank

:

Thanks for the reply and effort. I look into it and read more abou basic
relationships and table structure as Tina suggested.

Thanks again
 
F

Frank

Tina

Thanks for your reply and the link to CSD I will definitely read and use the
information to normalize the table structure. I have been using the Access
2007 "The Missing Manual" - Matthew MacDonald as a reference which helped a
lot but CSD will certainly help more.

Thanks for your effort

Kind regards

Age does not help brain function but persistence and dedication does
 
T

tina

you're very welcome, Frank; i'm confident that you'll make a good job of it.
becoming proficient in building relational databases does require that
dedication and persistence - but the good news is that the better you get at
it, the more fun it is! :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Images are pixelated when saving to PDF 0
"MSysCompactError", "System resource exceeded" & "You do not have the necessary permissions to use the...." 0
Adding data to MS Access from csv file 0
Excel - Wich Function should I use? 1
Linking text to photos 0
Table Design Question 3
one to many design 7
Diagram Access DB Process with Visio 3

Top