Database Help, Forgotten what to do

[JPRENG]

I studied Access back in 2005 and have sadly forgotten alot of what I learnt. I have got 2 tables with the following fields;

Employee
Employee ID (Primary Key)
Employee Name
Gas Safe (Check Box)

Jobs
Job Number (Primary Key)
Job Name
Gas Safe (Check Box)
Employee Name (Look up Box)

What I want to happen is, fill in the employee record and then fill in the Jobs record and when I tick the gas safe box I would like all the employee's who are gas safe to be displayed in the Employee Name (Look up Box) I would then like to select one of the employees for the job. And then the Job Number would be displayed in the employee table so I know what job the employee is on.

I think I need another table to make this happen? But as I said i've forgotten what i was taught all them years ago. Any help would be greatly appreciated. Thanks.

 

[PaulO]

All simple enough, and I think through time you'll want several additional tables depending on how much of a Job System you want (e.g. Materials assigned to job; time taken; etc). I once built some databases for a Central Heating installer so I think I know where you're headed generally with this project.

So, you have your Employees table (again you might want a few extra details to be logged here e.g. Gas Safe Registration Number, contact details) and, critically, if they're Gas Safe registered then the Yes/No field will be ticked.

Your Jobs table needs to be serviced by a Jobs Form. The Employee dropdown field in this form will be fed by a select Query that looks at the Employees table with Query Criteria on Gas Safe field in the Table taken from the Gas Safe field in the Jobs Form. This will limit the Employees list to just thise with Gas Safe.

 

[JPRENG]

Thank you for the reply. How do I create the query you mention? Thanks.

 

[PaulO]

OK ...

If you can zip and upload the Database (in Access 2003, preferably) I can take a quick look and add the required query and/or form for you.

 

[jdraw]

To help re-familiarize you with Access (database) and table design, I recommend you work through this tutorial.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

I recommend you get your tables set up to match your business needs before getting into to forms and the nuts and bolts of MS Access.

 

[JPRENG]

OK, so what's needed has changed slightly. I need one table storing all the employee's and their qualifications (Gas safe, asbestos etc) and another table storing all the jobs and if these jobs need any specilists on them (gas safe, asbestos etc) Then I think I will need a third table where I can select a job, then based on the criteria of the job (gas safe, asbestos etc) filter all the employee's so I am just left with a list of employees who meet the criteria which I can then select from and "assign" to each job.

If anyone could give me any pointers on how to go about this, that would be greatly appreciated. Thank You.

 

[PaulO]

I suspect that your specification will grow rapidly, what's the final objective for your project?

You need, as best you can, to think of the end-to-end solution that you're looking for and this will help flesh out the data tables (and content) you will need at the outset. I suspect you're looking for a full Job Management and Allocation, Costing, and Invoicing solution which will of course take time to develop. Great project, but you do need to sit back for a few moments and plan your requirements.

 

[jdraw]

As PaulO suggests,I also think you may be looking for something larger in the future. However, for the short term, I think you will need to revise your table structure a little.

I think you should have tables as follows: (my best guess based on your comments)

Employees
Qualifications
EmployeeHasQualifications
Jobs
JobRelatedCriteria


I was helping someone a few months ago with a similar (conceptually) issue. The poster had Employees and those EmployeesHadTraining. To match Employees to JobPositions, which also had TrainingRequirements, we set up a data model (read that as table structure) as shown in the attached jpg.

Hope it's helpful to you.