Massive check-list database

[Rainlover]

Also I thought you would need another junction table in between inspections and equipment as many equipment for inspections and equipment can also be in multiple inspections (another many to many relationship)

I don't agree. Each Inspection happens on a different piece of Equipment. I would assume that the Equipment gets inspected at different time intervals. e.g. 100 Hours



So each Inspection would be a new record.

[Rainlover]

I understand it is the anti-normalization however there are many to many relationships everywhere

Many to Many is not Anti Normalisation. Feel free to use them if the circumstance requires it.

I also don't see the point in having tables for cities and states besides if you were trying to teach me normalization
The Cities and States is Normalisation. The reason is that both Cities and States are repeated in many places.

It also improves data entry. Rather than having WA W.A. and Western Australia you only have one choice.

This helps immensely when searching for Clients in a Particular State.

Also the Postcode only needs to be enter once. So if a user enters a City then the postcode automatically appears and is the same for each instance of that City.

I have not touched upon the Items inspected. Let's get this part working first and do the Items later.

[JFo]

thanks for all your points I may have been looking at it from the wrong perspective, yes everything is joined I just thought it would be similar to the way that the data is entered so logically I would enter clients and then sites and then inspections and then equipment so I thought they should be joined in a similar way

Also I thought if there was ever a many to many relationship then they needed to be joined with a junction table (my mistake primary compound not dual primary). I did study this years ago however mostly I am just learning myself from google and forums

Also does the way that you want to extract the information effect the way that you should store it as well?

I am indeed confused as I would have also thought the equipment belongs to the inspection as they are the components that make up the inspection

I will try and get my head around your design and see if I can make the forms I need for the entry and retrieval and then the reports

Thanks Rainlover

[JFo]

Also I thought you would need another junction table in between inspections and equipment as many equipment for inspections and equipment can also be in multiple inspections (another many to many relationship)

I don't agree. Each Inspection happens on a different piece of Equipment. I would assume that the Equipment gets inspected at different time intervals. e.g. 100 Hours

So each Inspection would be a new record.
__________________
Regards

Rain


With this point just checking we are on same page all the different pieces of equipment inspected makes up one inspection, there is not one inspection for each piece of equipment

u r right that there is a time frame

[Rainlover]

Also does the way that you want to extract the information effect the way that you should store it as well?

Yes, Yes and Yes. This is the most important part of your project. Without a proper Data structure you will have no end of problems getting the Forms and Reports that you need.

With this point just checking we are on same page all the different pieces of equipment inspected makes up one inspection, there is not one inspection for each piece of equipment

I got this wrong. I thought there was a separate Inspection for each piece of equipment. I will need to make some Mods.

That’s all for me for tonight. Will try and get back to you tomorrow but I do have a few things on.

[Rainlover]

I need some clarification.

Are you saying that if you inspect 20 pieces of Equipment in the One Day for the same Client at the same Site then they all have the same Inspection number.

[JFo]

yes that is correct one inspection has many pieces of equipment inspected in one day. That all get compiled together for one inspection report (one inspection Number). Hence I was saying that I thought the equipment belonged to the inspection however the same equipment could also be in different inspections.

There will also be a need to query what equipment items of an inspection are non-compliant, etc

[JFo]

Sorry missed these ones

I can't see why you would join the equipment to client table?

Who owns the equipment? The Client or the Site. If the site owns the equipment then my structure is incorrect. But if the Client owns the Equipment the the equipment must be attached to the Client which is what I have.

Note that the Client is attached to a Site so indirectly the Equipment is attached to the Site through the Client.

Yes the equipment more or less belongs to the client rather then the site. Sometimes the equipment is transferred from site to site and less frequently it is sold to a different client, however overall it usually belongs to the client

[JFo]

Also I can't see how the inspections are linked to the sites

Same answer as before.

The Equipment is attached to the Client which is attached to the Site.

This may be covered differently now as per previously detailing that there is multiple equipment per inspection

[JFo]

Also does the way that you want to extract the information effect the way that you should store it as well?

Yes, Yes and Yes. This is the most important part of your project. Without a proper Data structure you will have no end of problems getting the Forms and Reports that you need.

I suppose this is the bit I find hardest to understand. For example the structure of relationships at the moment with no link between site and contact, considering there may be multiple clients at any one site I have populated the tables a bit and am trying to work out how you would query what the client contact from client b was at site a and consequentially add to the respective inspection report with client details, site details, contact details, inspection details and all equipment involved in inspection details (including results)?

[Rainlover]

I suppose this is the bit I find hardest to understand. For example the structure of relationships at the moment with no link between site and contact, considering there may be multiple clients at any one site I have populated the tables a bit and am trying to work out how you would query what the client contact from client b was at site a and consequentially add to the respective inspection report with client details, site details, contact details, inspection details and all equipment involved in inspection details (including results)?

A Query can contain more than One Table. I have seen a Dozen or more Tables in a Query.

Also You can Mix and Match with Other queries to end up with the result that you want.

It looks like you are starting to understand much better.

Can't help much today. A bit busy.

[JFo]

Ok I will keep trying, thanks

[TinaCa]

What about using a table designed like the old Access switchboards.

You would have one table with the checklists that included:
checklistId (1,- 30), checklistnumber (Q1, Q2, Q3, etc), checklistQuestion, etc.

Then another table for the answers.

You would only have to repeat the unique Id for the row in the checklist table (the one I indicate in design for above).

When you need to show the information the report you would use the unique id from the checklist table to get the text and number for the question and the responses from the "answers table". (you only need to record what they select 1, 2 or 3 not all the possible answers and the one they selected). The answers table would also include a unique id from the "person checking the equipment" table so each row in the answer table would only repeat the unique id for the question and the unique id for the person.
You could even make it a drop down during the table design using the lookup feature and preset the answers for the questions as 1 - Yes, 2 - No, 3 - N/A. so you don't need option boxes on the forms. When you create the forms, this would automatically come up as combo boxes.

Lastly, don't recreate each form. Get one the way you like it and copy it. Then set the control source or filter for the checklist you want displayed on the form. Lots of ways to do this part.

[JFo]

Thanks for your feedback TinaCa.

So If I had one table for all the check-lists sorry I don't follow 100%

ChecklistID (is this primary key or checklist number say we have 30 check-lists)
Checklistnumber (is this the checklist number as above or is this the question number say we have 150 questions on largest and 20 questions in smallest check-list)
Checklistquestion (is this the actual question as we would have to write out 150 questions for largest as they are entirely different from the 20 questions of smallest)

Also responses are not just yes, no or maybe but also must include a unique answer or comment. (this unique answer is very important that it can have a default value inserted dependant on question and can be validated)

Additionally for each specific inspection there would be multiple check-lists

Cheers

[Rainlover]

Try This.

ChecklistID (is this primary key or checklist number say we have 30 check-lists)
Checklistquestion (is this the actual question as we would have to write out 150 questions for largest as they are entirely different from the 20 questions of smallest)
EquipmentTypeFK(This will distinguish which questions are applicable)
CheckListAnswerFK ( to lookup a different Table Called CheckListAnswers Table.

TinaCa

Thanks for jumping in.

When you need to show the information the report you would use the unique id from the checklist table to get the text and number for the question and the responses from the "answers table". (you only need to record what they select 1, 2 or 3 not all the possible answers and the one they selected). The answers table would also include a unique id from the "person checking the equipment" table so each row in the answer table would only repeat the unique id for the question and the unique id for the person.
You could even make it a drop down during the table design using the lookup feature and preset the answers for the questions as 1 - Yes, 2 - No, 3 - N/A. so you don't need option boxes on the forms. When you create the forms, this would automatically come up as combo boxes.

This is mostly correct, except most would advise against having Lookup in Tables.
But for simplicity just do it and Remove the Table lookups when you have finished creating the Forms.

TinaCa, perhaps you may be willing to help with the code that changes the Recordset.