Query Advice

[mickx10]

Im hoping someone would be able to help me / offer any advice with a problem im having.

I have a db with 4 tables containing the following fields -

- tDocument
- dID (Autonumber)
- dNumber (text)
- dTitle (text)
- dSkill (Yes/No)

- tEmployee
- eID (AutoNumber)
- eName (Text)
- eSkill (yes/No)

- tRevision
- rID (AutoNumber)
- rDocNum (Text) - Linked to tDocument.dNumber
- rNumber (Text)
- rIsCurrent (Yes/No)

The above three tables are used to populate the below table
- tTraining
- tID (AutoNumber)
- tName (Text)
- tDocNumber (Text)
- tRevNumber (Text)
- tDate (Date/Time)

The purpose of the db is to track all the training documents we have along with revision numbers and which employees have been signed off (or trained) on the document.

Everytime we add a new document or update the revision number of a document i need to run a query to show which employees need to be signed off against it. This is where im running into problems. How would i go about doing this?

Also, the eSkill field links with the dSkill field. For example if my employee has a skill set of "a" the documents would only show where the skillset is "a". Not sure if that makes a difference.

 

[jasmith4]

First, for simplicity, ditch the auto-numbers, and make sure tDocument.dNumber, tEmployee.eName are unique, because those should be your primary keys. In tRevision make rDocNum and rNumber your primary key, and in tTraining make the primary key tName, tDocNumber and tRevNumber. tTraining.tName links to tEmployee.eName, and no one can be trained on the same revision of the same document twice, right?

I'm not sure what you're doing with the skills, because they're yes/no's in your definitions, but you're referring to skill set "a". Can an employee have or get more than one skill? If so, you need another table of skills (e.g., tSkill with sSkill=text) and another table tracking what employees have what skills (e.g., tEmployeeSkills with eName linked to tEmployee.eName and sSkill linking to tSkill.sSkill).

Please clarify re the skills and put up some sample data.

 

[HiTechCoach]

See if this helps: Database diagram

Boyd Trimmell aka HiTechCoach
Microsoft MVP - Access Export