Update Field based on Multiple Fields

[matthewnsarah07]

I have a databse containing frmCH. On this there are 13 fields which are
[Q1] to [Q12] and [Status]

When the form is closed each time I want some code that changes the [Status] to "Complete" if all other fields are anything other than Null

If any still contains Null then [Status] = "In Progress"

How can i best do this??

 

[boblarson]

How can i best do this??

The BEST way is to NOT do it. This should just be displayed with a query and not stored (violates Normalization principles - you should not store data which is dependent upon any other field for that record).

Use a query when you need it.

Also, it sounds like you also have normalization problems as you have fields like [Q1], [Q2], etc.

 

[matthewnsarah07]

On the subject of normalising:

This database will have is storing the answers to questions from a set group of staff.

There are around 70 questions over 7 subject areas - am I better off having a table per question or per subject with 10 question fields in each

Thanks for your help

 

[boblarson]

On the subject of normalising:

This database will have is storing the answers to questions from a set group of staff.

There are around 70 questions over 7 subject areas - am I better off having a table per question or per subject with 10 question fields in each

Thanks for your help

No, you use RECORDS. You can have a table for responses:

tblResponses
ResponseID - Autonumber (PK)
StaffID - Long Integer (FK)
QuestionID - Long Integer (FK) (from questions table)
QuestionResponse - Text


tblQuestions
QuestionID - Autonumber (PK)
QuestionCategoryID (FK)
QuestionDescription - Text

tblQuestionCategories
QuestionCategoryID - Autonumber (PK)
QuestionCategoryDescription

With tables like this you can have as many questions as necessary AND you can do all of the number crunching you need.

 

[matthewnsarah07]

Is it easy enough to make a form with say 10 questions on it using this table method

How do you link each question to the form whilst sending the reponses elsewhere?

 

[boblarson]

Is it easy enough to make a form with say 10 questions on it using this table method

How do you link each question to the form whilst sending the reponses elsewhere?

Give me a few and I'll post a sample.

 

[matthewnsarah07]

Many thanks for your help on this one

I have attached 5 sample questions from one category and all with Yes/No answers

 

[matthewnsarah07]

Any luck with an example as yet?

 

[boblarson]

Any luck with an example as yet?

I've been working on it as I can in between my real work. Sorry for the delay.

 

[matthewnsarah07]

I do appreciate your help on this one.

Also this database hold questions that check staff skill levels each month so every month each staff member of staff is tested on all 70 questions.

Should I append a record for each question for each member of staff to the responses table at the beginning of each month or is there are better way - I suppose either way you end up with the same numbr of records

 

[boblarson]

Here's a basic sample. Of course it won't fit your situation exactly, but hopefully you can "tweak" it to make it work for you. I have categories but don't have them on the form, and I put a button on to add questions for an inputted date but I didn't modify the form to only show that date's questions. So, hopefully you'll be able to use this though.

 

[matthewnsarah07]

Many thanks for all your help - I'm going to get to grips with the project now. I think I have got to grips with working with normallity!!