Help on design?

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

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.
--
Duane Hookom
Microsoft Access MVP


"Frank" wrote:

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

"Duane Hookom" wrote:

You can take the time to type the structure into a message like:
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

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



"Duane Hookom" wrote:

You can take the time to type the structure into a message like:
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

Frank, you're breaking some basic normalization rules, common errors among
newbies. overall, this looks like a pretty simple process you're attempting
to support, nothing weird or sticky. recommend you study up on relational
design principles; once you understand the basics, you should have no
trouble seeing where your tables need to be re-designed. for more
information, see
http://www.accessmvp.com/JConrad/acc...abaseDesign101

hth


"Frank" wrote in message
...
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

"Duane Hookom" wrote:

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

Thanks again

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

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!


"Frank" wrote in message
...
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