Design review

A

Allen

I've never worked with access. Is there a way to get somebody to help review
my first database?

If so how should I provide the information?

Thank you.
 
J

John W. Vinson

I've never worked with access. Is there a way to get somebody to help review
my first database?

If so how should I provide the information?

Thank you.
Click to expand...

Well, we're all unpaid volunteers here. You should be able to hire someone to
do so; downloading and evaluating a database would be a pretty substantial
investment of time. How's your budget?

Or... if you want free support... are you having specific problems? or would
you be willing to post (in text form) some details that you're uncertain
about?
 
J

Jeff Boyce

Allen

The newsgroups are great on offering specific support on specific questions.
Your's sounds more like a general/overall question.

If you aren't familiar with the terms "normalization" and "relational",
there's a pretty good chance your table structure will not let you use
Access' relationally-oriented features/functions without considerable
struggle.

How did you decide on the structure you have?

Here's one way you could depict the table structure you have (this is only
an example):

tblPerson
PersonID
FirstName
LastName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelRegistration
RegistrationID
PersonID
ClassID
RegistrationDate


Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

Allen

Jeff, Thank you for the input. How's this look. I think I understand
normalization. I'm a little confused about linking (trlJob). I thought I'd
make it simple just to get my feet wet.

tblJob
fldJobID
fldDiscription
fldDateReceived
fldDateOpened (todays date and time)
fldStatus (Open/close)
fldOwner
fldCommItOwner
fldReviewers

tblStatus
fldStatus (an on-going status of the job, new entries will have the
current date and maintai all previous entries).

tblOwner
fldJobID
fldFirstName
fldLastName
fldPhoneNumber
fldEmail
fldUnit

tblCommItOwner
fldJobID
fldFirstName
fldLastName
fldPhoneNumber
fldEmail
fldUnit

tblReviewers
fldJobID
fldFirstName
fldLastName
fldPhoneNumber
fldEmail
fldUnit

trelJob
fldStatus
fldJobID
 
J

Jeff Boyce

Next step ...

You've listed a "pile" of tables. Now, how are they related, one to
another?

For example, in the example I provided, one person can be registered for
many classes, and one class can be registered for by many persons.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

Allen

I attempted to communicate the realationships. I changed some of the field
names so I pasted all the tables into this email.

Am I on the right track realted to the relationships?


tblJob
fldJobID
fldDiscription
fldDateReceived
fldDateOpened (todays date and time)
fldStatus (Open/close)
fldCustomer
fldCommItOwner
fldReviewers

tblStatus
fldStatus (an on-going status of the job, new entries will have the
current date and maintai all previous entries).

tblCustomer
fldJobID
fldFirstName
fldLastName
fldPhoneNumber
fldEmail
fldUnit

tblCommItOwner
fldJobID
fldFirstName
fldLastName
fldPhoneNumber
fldEmail
fldUnit

tblReviewers
fldJobID
fldFirstName
fldLastName
fldPhoneNumber
fldEmail
fldUnit

trelJob
fldStatus
fldJobID


A job will have many status's.

tblJob tblStatus
fldStatus (one to many) fldStatus

A job will have one Customer, but one customer can have many jobs.

tblJob tblOwner
fldOwner (one to many) fldJobID

A job will have one Comm IT Owner at a time. It will be able to be changed
to another owner, while maintaing the original owner. A Comm IT owner will
have many jobs.

tblJob tblCommItOwner
fldCommItOwner (one to many) fldJobID

A job will have multiple reviewers.
tblJob tblReviewers
fldReviewers (one to many) fldJobID
 
J

John W. Vinson

I attempted to communicate the realationships. I changed some of the field
names so I pasted all the tables into this email.

Am I on the right track realted to the relationships?
Click to expand...

pmfji but no, you have them all BACKWARDS.

You must have a Primary Key in the "one" side table related to a Foreign Key
in the "many" side table. You are trying to put the foreign key in the one
side table. For example you say

A job will have one Customer, but one customer can have many jobs.

tblJob tblOwner
fldOwner (one to many) fldJobID


If a customer can have many jobs, then you cannot put fldJobID into tblOwner;
fldJobID can have ONE AND ONLY ONE value, and you're saying you need many
jobs!
 
Top