Inspecting devices and combination of devices at multiple properties

[engnrguy]

Hello,

We are a service company that inspects approximately 13 different types of devices at about 1,000 different properties. Each property has at least one of the thirteen devices, but it may have as many as all thirteen. Each device has about 5 specific questions that our inspector has to answer (yes, no, n/a).

I am looking to build a database that we can use to store the property location and list the devices that each property has. Based on the stored data, we will print forms that our field technicians fill-out by hand. We are not looking (at least not at this point) to store the results of the inspection in Access.

Currently, I have a property table that has the name and address as well as check boxes where the user can select all the devices that are present at each property. I have a second table that is called questions. Within questions, I have typed in all the questions that we'll use and each one is designated as device1, device2, etc...

How can I build a report that will print out with the name and address and list the devices present at the top of the page, but I also need it to print out the questions that applicable based on the device list?

Currently, we have a database that does what we are looking for above, but we just have a really long report with all the questions for all devices... The inspector just crosses out what he doesn't need. I would like to have reports print that only have applicable questions on it.

I have built a few different programs that our company uses so I do have some experience, but its mostly come from trial-and-error. I do not use VBA, but if its needed I could take sometime to try and learn it.

Thanks in advance for any help anyone can provide. I have used this forum to get answers on other questions, but I have never posted before. If I am in the wrong area of the board with my question let me know.

Thanks,

Engr

[June7]

The use of multiple Yes/No fields for same type of data (the devices) is not normalized structure. Consider:

tblProperties
PropertyID

tblDevices
DeviceID

tblPropertiesDevices
PropertyID
DeviceID

tblQuestions
QuestionID
DeviceID
Question

This assumes a question can be associated with only one device. If a question can be applied to multiple devices, then remove DeviceID from tblQuestions and use:

tblQuestionsDevices
QuestionID
DeviceID

[engnrguy]

Your assumption is correct. I will try your idea tonight. I will give you an update tomorrow.

Thanks!

[engnrguy]

Moderator,

I tried your solution and I got it to work. Its still a little rough around the edges, but the major concept that I was looking for was achieved.

For tblPropertiesDevices, I made propertyID and deviceID Primary Keys. Why is this necessary? As I said before, most of my success with this program is trial-and-error so some of the concepts I am not always 100% on.

[engnrguy]

Forgot to say thank you.. I really appreciate the help.

[June7]

By making both fields primary keys (a 'compound' key) duplication of pairs will not be allowed.