Database Tweeking

[Phoenyxsgirl]

I have some plain english questions for all you Access users. I'm still learning and getting more skilled but I'm not seeing how some things that I need done can happen.



So, I need to ask because they need to be done soon and I just ain't seeing it.

First off, I need to be able to create a questionnaire that can directly be transferred to creating a list of needs ... for example, each question could bring up 6 of 10 things needed and all be a different combination of things.

Second, I need this information to be directly updated in another couple tables.

To give you a better idea - I have a list of worksites that some will require different PPE (Personal Protective Equipment). I need to track what the worksites do. That tells me if the workers need PPE. I don't want to have to go in for each worksite and check these things off. I also need for my list of worksites to show whether the job there requires PPE. I want this to automatically fill in also.

So, what do you think?

[June7]

I think you need to get a better understanding of what relational database is and how to design one. Start with the 'sticky' thread tutorials here http://forums.aspfree.com/microsoft-access-help-18/

[ssanfu]

I don't want to have to go in for each worksite and check these things off. I also need for my list of worksites to show whether the job there requires PPE. I want this to automatically fill in also.

Somebody has to do the initial data entry... How do you envision the data getting entered?

I can't tell what your structure looks like. Something like the attachment??

On a form, I would have a couple of cascading combo boxes (or list boxes) to select the job site, select the job, then a multi-select list box to select the PPE's required for that job. Jobs (skills) might be like weldors, electricians, carpenters, masons,... each have different requirements for PPE.

The list box for PPE would have the row source query based on the PPE table.

After all of the selections are made, a button would run code that would loop through the PPE list box to make entries into the "RequiredPPE" table (with the job PK).

So, what do you think?

[Phoenyxsgirl]

I think I gave the wrong idea. Its a database. Of course someone has to do data entry.

I understand how a database works. I'm just not sure if I can make it do this or not.

For example.. if someone uses heavy equipment, they have to wear boots, jeans, hard hat, glasses, and have certification. If someone uses tools, they have to use glasses, gloves. If someone works around heavy equipment but doesnt operate it, they need boots, jeans, hard hat, vest.

What I want to do is set up something so that when I click this job involves X (say working around heavy equipment) then it triggers the other fields to populate saying yes to A B C D (the equipment that job needs).

The ultimate goal is to send a form out to the worksite and let the supervisor there fill out the form. When it gets back and the data is entered into the database, then the equipment needed is automatically filled in for that job and that job automatically gets listed as a job that either does or does not require PPE.

@ssanfu, so I have a chart that has across the top the types of ppe, along the left side is the things that would require ppe, and the chart is filled in with X's or check boxes so that the corresponding ppe and the job requirement match. Like, the line for "Uses heavy equipment?" would have checks under boots, hard hat, glasses, certification... That is a query right? And you are saying I could link my table field to that query so that it would pick up on what ppe were require if the job had to use heavy equipment?

[June7]

If what you mean by 'chart' is a table, that is not a normalized data structure and is not a query and possibly not reflective of ssanfu's suggestion. Your 'checkbox' structure makes data entry easy but can be frustrating for some query requirements. However, possibly it is suited to your situation.

The junction table RequiredPPE would have has many records for each job as required to identify the necessary PPE records. I think under ssanfu's suggestion would not be a 'checkbox' arrangement. Each record in PPE would be a single piece of equipment (hat, gloves, etc) and another field to identify the job category (heavy, operator only, etc).

With your checkbox structure the RequiredPPE junction table is not needed. Have a field in Jobs for the PPE_PK key. In fact, by using a checkbox setup, can probably combine the tables. Sounds like construction industry. You probably have a standard collection of job classes (Equip Operator, Flagger, Driver, etc). Consider this:

tblJobClasses
JobClassPK
JobClassName
equipment check box fields

tblJobSites
JobSitePK
JobSiteName
etc.

tblSitesClasses
JobSiteFK
JobClassFK

[Phoenyxsgirl]

That would SO work :P You have both been so helpful. I wish I could keep you while I finish building this database LOL

[ssanfu]

Sorry, no offense meant.

The reason I suggested using a list box instead of check boxes is that if there is a new job class that requires different PPE that is not in the chart, or a job class adds additional PPE that is not in the chart, you (might) have to redesign the tables, queries, forms and reports (if any). Also, the query would have the list of PPE in words; there is a chance that a PPE might get missed having to scan columns and rows.

That would SO work :P You have both been so helpful. I wish I could keep you while I finish building this database LOL

We are not going anywhere .... Keep posting when you have questions...

[June7]

The point we are making is that the checkbox structure, although seeming so simple and easy to use, is less flexible from a design perspective. All we can do is try to help you understand concepts but in the end, it is you who will decide what works for you.

[Phoenyxsgirl]

The only reason I was using checkboxes is because it was yes/no answers for the field. If checkboxes don't cooperate with the way it needs to work, then I won't use them.

The chart I'm trying to incorporate has the different types of PPE across the top and the different situations that would require PPE down the left side. There are check marks in the appropriate boxes for which PPE is required for that work situation.

I know that data entry has to happen, but the less times it has to happen, the less chance for errors. I really appreciate you both helping! I did post another question entirely LOL in the forms section