Massive check-list database

[JFo]

Ok I am trying to follow both of your instructions do these relationships look OK as per screen shot



Attachment 4471

I have attempted to take your advice Rainlover and have added the city, state and changed the contact table and it seems to work. I also linked city to site (is this correct)

Furthermore I tried to add tables as per TinaCa's advice are they correct with the relationships, I still can't conceptually see how it is going to work but I have decided to try it and hopefully I can figure it out once I start trying to add data/ forms and create queries

You will notice I have left in some other tables for now and I know you are opposed to at least the separate check-list tables. But additionally I am not sure about the inspection/ equipment ones either

meanwhile I have managed to make queries work with your table design Rainlover and have made a number of forms that appear to be working as well

Getting there slowly I hope

[Rainlover]

A quick read about Lookups in Tables. (Noting to do with Lookups in Forms.)

http://access.mvps.org/access/lookupfields.htm

Attached is my fix for Inspections.

[Rainlover]

Helpful hints. (Hopefully)

Put all your Foregin Keys at the top of your table. It makes it easier to view especially when you do a snapshot.

It is your Client that is attached to the site not the Inspection. Suggest you delete that Relationship.

Use the same name for Primary Keys as you do for Foregin Keys. Put ID at the end or as I prefer PK for Primary Key and FK for Foreging key. Up to you which one to use.

Your Relationship between tblChecklist and tblChecklistAnswer is back to front. Put ChecklistID in tblChecklistAnswer and delete ChecklistAnswer from tblChecklist.

Finally you will need another table tblResults. This is where you will store the Number of the question (Which comes from the PK of tblChecklist), and the Number of the Answer (Which comes from the PK of tblChecklistAnswer), as well as a comment if required.

Once you have done this then we can start work on Storing the results of the Inspection via a Form.

[JFo]

Yes helpful for sure

How is this?

Attachment 4474

Amongst other things I am still very worried about the importance we are putting on comment

(this to me is the most important thing as it is the answer to every point on the check-list and therefore needs to have a default value for every field in every check-list and needs to be validated on every field as well)

I am also worried about searching, reporting and data entry, however I could not understand the relationship of contacts until after I had done it and it seems to work well with querying

So now our relationships are fairly similar I am eager to see how this design works out

Lastly for now is the lookup for tables a good thing? the link you sent me didn't exactly exude confidence??

Thanks again

[Rainlover]

Lastly for now is the lookup for tables a good thing? the link you sent me didn't exactly exude confidence??

Don't get confused between Lookups in Forms and Lookups in Tables.

The first is good and should be used. The latter is wrong.

But in your situation, I am suggesting creating Lookups in tables as a temporary measure. This way when you create a form that has a Field from a Table that has a Lookup then that lookup will be automatically created for you. But once you have finished creating the Forms then delete the lookup in the Table. The Lookup in the form will remain.

[JFo]

OK sounds like a good idea, thx

[Rainlover]

In tblArea. Is AreaTypeID necessary?

[JFo]

Not for now, I have deleted it, sorry forgot about that one... I think the only time I will be using area will be at the start of each check-list to detail where equipment is but can just be on the form I think

[Rainlover]

Our Table designs almost agree. I have attached my latest.

The Questions and Answers are not Normalised. But don't worry about that because if you did Normalise them then the design of the Forms and Reports would be even more difficult than what the are going to be.

tblResults will have to be populated by code. Hope you are up to the task. It will be a big learning curve.

[JFo]

Ready as I will ever be

[JFo]

I did notice in the answer table the Question 1, 2, etc so the fields for the maximum number of questions on check-list I assume regardless of number

And yes my tables are looking similar, thanks for the tips

[Rainlover]

I got the Answer Table wrong.

There is only need for One Answer. Get rid of the multiple Fields.

Thanks for pointing that out.

[Rainlover]

I found a development Database that I Designed a while ago.

Check out "Opening Times"

The code behind this is similar to what you will be needing.

[JFo]

No worries will check it out asap

Just a quick scenario to make sure I understand from a novice perspective am I correct in the notion that the way I would display the correct check-list for a certain piece of equipment in an inspection and therefore present a default answer (comment) and validate to ensure specific data is entered for each field I would have to code it with something like
for any inspection if equipment type is this then open this checklist which would decide number of questions/ fields and if this checklist number is this then the answer must default as this and be validated to be something like this

Very vague I know but is that sort of the situation?

Cheers

[Rainlover]

If I understand correctly. then the answer is NO.

There is no need to Validate as only those matches as per the Relationships will show.

The use of the word Validate to me usually means that the answer falls into a particular, e.g. Between 20 and 30.

Is this the way you are using it?