Toughy: must track 35 licenses, status, for each employee 2

[AceJupiter]

Hi guys,

This is a bit of an x-post - I didn't see this group yeaterday when I posted to "Access - Other." I have tried to refine my question a bit.

I have a table that contains information about my company's members - tblMembers. It's a cargo handling/logistics group and guys can hold any of a collection of about 35 licenses.

I have all of the license information in tblLicenses, and I am agonizing over its design.

Picture this:

For each license, which has a license ID, we record the following about EACH of the 25 licenses fo EACH guy:

Completed? Tracking System Reported? Documented? Schedule Date(s) Comments Tracking System Update Date Field

I am trying to replace a spreadsheet.

The design of my tblLicenses has a row for each license like this:

LicenseID | License | Completed? | Reported?| Documented?|
1 DOZER y/n y/n y/n

How can I record, for each guy, where he stands on each of the 35 licenses and the assorted additional data for each license?

Thanks,

Ace

 

[AceJupiter]

Oh! I should have menched:

The primary key of tblMembers is MemberID.

Each license has an autonumber unique ID called (you guessed it) LicenseID.

Thanks.

 

[lespaul]

The answer in your other post is the correct answer. You need three tables:

tblPeople
tblLicense
tblPeopleLicenses


tblPeople
PK - PeopleID

tblLicense
PK - LicenseID

tblPeopleLicense
PeopleID
/
PK
LicenseID

the primary key of tblPeopleLicense is a composite key made up of both the personid and the license id.

hth

leslie

 

[AceJupiter]

Thanks leslie. I thought it might be...just needed to confirm (my head was spinning a little bit from the "relationships are an Access thing" post.)

I'm excited. This will be my first m:m relationship in Access!

Have a nice day.

Ace

 

[JeremyNYC]

Ace,

I haven't seen the other thread, so this may be redundant, but you might want to check out Paul Litwin's "Fundamentals of Relational Database Design" article on my website. It's got a lot of the reasoning for this laid out very clearly.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[AceJupiter]

Thanks Jeremy,

I'll take a look. Pretty sure I have a fairly keep grasp of the fundamentals, but that sounds like a good reference.

I'll let everyone know how it comes out.

Ace

 

[AceJupiter]

Leslie,

In the third, composite table, I have the LicenseID keyed to my tblLicenses and the MemberID relating the composite table back to tbl Member. I thus have a nice table storing which guy has what license.

tblMemberLicense
MemberID : LicenseID

1 3
1 4

Say, Member 1 is Bob Smith, so this table stores the fact that Smith has a Dozer license and a 25 ton backloader license.

Do I build the additional fields, such as "documented" right into that composite table? Would seem to make sense.

tblMemberLicense
MemberID : LicenseID : Dccumented?

1 3 Y
1 4 N

Is the only thing left in tblLicense LicenseName and LicenseID?

Many thanks.

--ACE

 

[lespaul]

Yes, the only thing that should be in tblLicense is generic License Data, if you want to store something like the regulating agency for that license, that would be something to store in tblLicense.

Anything that has to do with the person's license should be in the composite table: (i just made these up, but they seem like they would be possible fields for the table if you needed them)

Documented
ValidTo
IssueDate
CertificateNumber

Did you read the paper Jeremy recommended? It really is a great tool for understanding how to put tables together and the relationships between the tables.

HTH

Leslie

 

[AceJupiter]

Leslie,

thank you. And I will take a look at the reference material the gentleman suggested, since it becomes clear than I am not as frosty on m:m as I should be!

Have a great day!

-- ACE