Massive check-list database

[JFo]

yes that's what I mean. Every field must be validated so a user can only enter a number between 20-30, or a date or a combo box of possible options for every field (question/ comment) of each different check-list (and every check-list needs different validation) and they must also select, yes, no or n/a. Is this still the correct way to do this, can I still do this? This is why I originally had each checklist as a separate table as I thought it would be easier... apologies for confusion but this is essential for the idiot-proof aspect of data entry



Cheers

[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 thisCar then open this checklist Checklist1which would decide number of questions/ fields and if this checklist number is this meaning check-list questionthen the answer must default as this Viewedand be validated to be something like this 19/06/2011

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

Cheers

[JFo]

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

I may have been confused... again, validation only refers to the users input on each form - specifically for every field in check-list all forms/ fields data entry (comments in check-list) need to be validated and a lot will need a default value

[Rainlover]

I don't think you are validating.

You are simply choosing from a list of predefined answers. As per the Table and the criteria that matches the question. Nothing else shoud appear in that list.

You could if required check that something has been entered and that the box is not empty.

[JFo]

Cool think we may be talking about the same thing

However when a piece of equipment is selected for an inspection and therefore a certain check-list is chosen... if a particular question is answered can you default the value as something and then ensure only a single character is entered, or 7 numbers are entered or a date is entered or they can not leave it null

this is what I was meaning by validation


Let me know if I am still not 100% clear

Thanks

keep in mind that every check list is different so can not enforce validation rules on say every checklist question number 7

[Rainlover]

Yes you can check for null.

Yes you can set a default answer.

I think we are reading from the same hymn book.

[JFo]

sorry 1 more just to be clear;

validation available on every single question of every check-list

not just default or null

you can enforce a date is entered or "7 digits like a phone number" is entered, not pre-determined and can be different for each question of every check-list?

[Rainlover]

YES.

We can do anything.

As long as you have the time.

[JFo]

Nice one was getting worried for a sec


as always any more tips or direction always taken on board

Thanks

[TinaCa]

As for the right way of doing it.....The idea behind using a database over an excel spreadsheet is to not repeat information. i.e. you don't want to put the clients name, address, etc in each record of the checklist done for that client.

I used to tell my students, think of a database as a big filing cabinet. In your case you would have a file drawer for checklists. In the drawer you would have a folder for each different checklist. So...a table for Checklist, a row for the checklists' information. Then a drawer for the checklists that a user has done and in the folders the id of who did and the results of each checklist (row), a drawer for the clients, a drawer for the people doing the checking, etc

I learned MS Access from a really big book. I like the Que books. We can't "build" your db for you here. Get a really big book. It will have most of the answers you need and some stuff that makes little sense . You need to understand databases before you can start this massive project. Take two days to read the first few basic chapters, it will save you weeks of headaches and delays. Good Luck!

[JFo]

Hi TinaCa although I may be a novice I have read books and actually studied Relational Databases as well prior to 2005 (normalization, normal forms, etc), of course if you do not use something then you forget it. I still have my access books and am using them however they are dated and I actually find it much better to use google for my research and these forums to pose my questions in this day and age. I am simply looking for instruction on design and asking questions how to do things

I understand not repeating data and that is why I had started with separate tables for clients, sites, etc at the start. There is no repeated data I don't think in any of my tables and I certainly would not be entering client details at a check-list level.

Thanks to Rainlover for picking up that I may have repeated cities and states (and for everything else mind you) I failed to recognise that redundancy. Please if anyone can see anywhere now that the same data is repeated anywhere please let me know

Of course you cannot build my database and I am actually a bit insulted that this is what is inferred I know I will be doing the majority of the design and pretty much all of the forms/ coding/ etc if you look back through my threads you will realise I had already designed something myself and because this is the most complex database I have had to do I would like opinions from experts to let me know if I am doing anything incorrect. I have always struggled with many to many relationships even in my uni days and the complexity of the difference of check-lists had me trying to take the easy option of creating different tables for each check-list (I know this is incorrect but is easier for me to see how it works and how I can control it)

Thanks to everyone who has and is helping me I don't want a free-ride, I want to learn how to do this myself, hopefully now I have the design/ relationships in place I can do most things myself and am starting with creating all my forms for data entry with my new structure

the difficulty now comes with the coding as I do not understand how the check-lists are coded at all?

Again all advice is appreciated

[Rainlover]

Houston, we have a problem.

The Maximum number of Fields in an Access Database is 255.

I would think that because of the number of pieces of equipment and the multitude of questions for each then we are more than likely to exceed the maximum number of 255.

Then to make things worse what are you going to do when a new piece of equipment comes on board.

We need to look at this seriously.

[JFo]

OK I am trying to figure this out I would say you are correct... at the moment I currently have about 30 different types of equipment and therefore have about 30 different check-lists. The check-lists have questions ranging up to about 150 odd Q+As. However I don't think that they would ever have over 255

With this solution wouldn't we only need the maximum number of fields for the biggest check-list?

[Rainlover]

Originally I was thinking that we could use a different Field for each Question. But now I see that we can not.

We must use Records to list all the questions.

I need to rethink our approach.

[JFo]

I thought it was essential to use fields for all the validation for every question.

Is there a way to use some sort of for loop that determines the number of questions and as long as the number of questions is under 255 then it will work

Then you would have to switch the checklist answers into 2 tables as the comment or answer would have to be within different tables to the yes, no n/a selection

Does this make sense?