Confusion with table relations

[BusDriver3]

Hi,



I am currently trying to track general data for about 80 people in my Division and then make that data spit out reports. The problem that I am having is I do not know how to relate all of this data... I have about 5 or 6 tables...

I have tried putting all of the data in one big table but I don't think that would really allow me to take full advantage of Access. The idea is that each person in the Division would be tracked and I could provide a "Training Status Report" that would include: Name, Date of Training, Topic of Training, etc... data would be pulled from the different tables and BAM, I have a report!

I can do the Forms, Queries, and Reports... but relating all of these table is beyond me. Can anyone help me with this simple problem?

Please see attached file.

Thanks in advance

[Access_Blaster]

This may help.

http://allenbrowne.com/binary/Access...Chapter_03.pdf
http://allenbrowne.com/binary/Access...Chapter_04.pdf


Richard

[June7]

You have the links on the wrong field in 3 of the child tables. The primary key ContactID in Personnel should link to each of the ContactID foreign key fields in the child tables, not the ID primary key of those tables.

Tables with one-to-one relationship could be all one table, especially if every field will have data for every person. However, max of 255 fields in a table/query.

Multiple fields with similar names is indication data is not normalized (College Course 1, College Course 2, College Course 3). The first link by Access Blaster deals with this concept and has more references on normalization at the end. Your arrangement is easier for data entry but some data summation and querying might be more difficult. You have to decide how far you want to take normalization. I have tables with repetitive fields because it suits my needs for simplified data entry and report output. I don't search, sort, filter on this numeric data and seldom need to perform statistical analyses. I did have to find way to manipulate it in order to build graphs.

Recommend no spaces in names.

[BusDriver3]

Thanks for the links... and the advice... (I will make the changes to the tables when I repost the finished db)... I have read the links and I think that this revision will work... please see attached file.

[June7]

Still need fixing, links to EvalCheckList, DOR, and PRT tables are on the wrong field.

Just moving the repetitive fields to a child table is not normalization. A normalized structure for Training table would be more like:

ID
ContactID
CollegeName
CourseName
Term (why is this necessary?)
ClassNum
ClassDate

All those CB fields are another issue. They are Yes/No fields so does this mean not all will apply to every applicant? Can more than one be selected? Normalization could mean another table.
TraningCB (table name)
TrainingID (foreign key)
CBCode

Note that I do not use spaces or special characters. SSN(Last4) should be Last4SSN. Awards/Proof better as AwardsProof or Awards_Proof (underscore is acceptable exception but I don't use it). Gender (M/F) should be just Gender (everyone should know there are only two and you restrict their input options on the form).

[BusDriver3]

June7,

So today, I read and reread the links from Access Blaster and then marinated on your advice… I have a much better understanding now.

The posted file was proof of concept for me, just stabs in the dark and experiments to see if I could even get the relationships to work in the manner that I needed them to… and after doing some more research I found that I have to rework the tables quite a bit…

The DB is supposed to track personnel and most of the stuff that we need to track is just One-to-One (i.e. for each Personnel entry only one EvalCheckList, only one PRT, only one DOR is needed per person)… But sometimes I will need to track One-To-Many (i.e. Personnel could take more than one College Course)...

As far as the CB fields go, those will change… when I took this project a few months ago I had no idea what I was doing (I still don't, lol) so I used fields that I thought would work. After becoming part of this forum and reading posts I have learned a lot!

I cannot thank you enough for taking your time to provide valued advice and observations, I will post rev3 shortly. Thanks

[BusDriver3]

Okay, so I am not sure if this (my relationships) is correct but it seems to work... I made a couple of test forms and I was able to update my records, I added a delete button and was successful with a cascading delete ... again, not sure if it is correct

Please see attached file.

[June7]

The training tables are linked on the wrong field. These tables are not fully normalized but perhaps this suits your situation.

There is no form which is where I assume the delete button is so can't test behavior but sounds like you have it working.

[BusDriver3]

Ok, I made the suggested changes... and made a couple of forms with buttons (I used the wizard )... I want to do this correctly, so i dont mind doing more work to make it right.

Please see attached file.

[BusDriver3]

i see what you mean about normalizing data... lol... rev5 attached...

[June7]

tbl_Transferred (transfer in or out?) should have only these fields to not repeat data:
ContactId
DateTransfer
Notes

Since ContactID is primary key in all tables, the autonumber ID is not needed in child tables. Doesn't hurt to have unless it confuses you.

Still not sure you grasp the concept of data normalization. If every table will always have a record, and only one record, for every person, splitting into separate tables really serves no purpose. If tables were combined, I count 127 fields, well under the max 255 for a table/query. So unless you normalize the repetitive and other fields that don't always have data into subsidiary tables, one big table is probably all you need. One form for data entry, organized with tab control.

[BusDriver3]

June7, I think that you are correct, I don't really grasp the concept of normalization... part of the confusion is coming from the advice that I get from my coworkers. Initially I had everything in one big table and then I thought "Am I doing this correctly"... I took an entry level Access course and that is when I got schooled and stupid (lol)...

It all seems to work with my version at work, until I got in to the problem of College Courses (i.e. one person could take more than one class, meaning I would have to make a relationship One-To-Many)...

The "Transferred Table" is just "adipose tissue" really, someone in the Division might ask "Hey I know Smith checked out but do we have the date that they checked out?"... I would took in the database to see what was recorded...

I will repost a file later today, Thanks again for your support on this.

[BusDriver3]

So, I read a bunch of stuff about "normalization" and realized that you are correct June7... I don't have data that falls in to a model like this:

Makers > Models > Items Purchased
Ford > F150/F250 > Fenders/Tires/Mufflers

I have a bunch of "questions" not data that have come at me over time...
Example1: "When did Smith check out?"
Example2: "Who needs flu shots?"
Example3: "When will Smith take their PT Test and give me a date and time."

I will follow your advice and simply put all of my fields in to one "Mega-Table". Thanks for providing sound and simple advice... ahh, clarity...

But I still have no idea how to close this thread... LOL... Thanks again

Occam's Razor: "the simplest explanation is most likely the correct one."

[June7]

You can use the Thread Tools dropdown at top of thread to mark Solved, otherwise, not a concern.

Could consider the repetitive fields for child tables. As is now, limited to 3 college courses. What if someone has more? See post 5.