How to determine/set a column value

[rich]

Hello,

Part of the functionality of my application is to track progress through a
(similar to a hiring) process. The process has 5 steps. When each step
complete, the date that is was completed on is updated in table. Table looks
like this:

Column Date Type
--------- ------------
Index (Tracking number) (PK)
EmpID
Employee start date Date
Paperwork_submitted_date Date
Computer_Account_created_date Date
Email_account _created_date Date
Access_to_applications_date Date
Process_Complete Yes/No

If all the date fields have a date entered, the Process_Complete column
needs to be set to "Yes", otherwise, it remains the default value of "No".
The user should not have to update this field.

Can anyone help me figure out how to accomplish this?

TIA,
Rich

 

[Allen Browne]

Remove the Process_Complete field from your table.

Instead, create a query, and type this expression. (It's all one line):
ProcessComplete: ([Employee start date] Is Not Null)
AND ([Paperwork_submitted_date] Is Not Null)
AND ([Computer_Account_created_date] Is Not Null)
AND ([Email_account _created_date] Is Not Null)
AND ([Access_to_applications_date] Is Not Null)

Then use the query where ever you would have used the table (e.g. form,
report, ...) You never have to worry about the value being wrong, because
Access calculates it as needed.

 

[rich]

Hi Allen,

As always, thanks for the quick reply.

I understand the query, however, shouldn't the result of the query be stored
in the table?

Also, I'm less clear on the syntax of the query. Should it be:
"Select Process Complete: ([Employee Startdate] Is Not Null........

Should I enter it exactly as (to be) typed in the SQL View?

Thanks,

Rich

Remove the Process_Complete field from your table.

Instead, create a query, and type this expression. (It's all one line):
ProcessComplete: ([Employee start date] Is Not Null)
AND ([Paperwork_submitted_date] Is Not Null)
AND ([Computer_Account_created_date] Is Not Null)
AND ([Email_account _created_date] Is Not Null)
AND ([Access_to_applications_date] Is Not Null)

Then use the query where ever you would have used the table (e.g. form,
report, ...) You never have to worry about the value being wrong, because
Access calculates it as needed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Part of the functionality of my application is to track progress through a
(similar to a hiring) process. The process has 5 steps. When each step
complete, the date that is was completed on is updated in table. Table
looks
like this:

Column Date Type
--------- ------------
Index (Tracking number) (PK)
EmpID
Employee start date Date
Paperwork_submitted_date Date
Computer_Account_created_date Date
Email_account _created_date Date
Access_to_applications_date Date
Process_Complete Yes/No

If all the date fields have a date entered, the Process_Complete column
needs to be set to "Yes", otherwise, it remains the default value of "No".
The user should not have to update this field.

Can anyone help me figure out how to accomplish this?

TIA,
Rich

 

[John W. Vinson]

I understand the query, however, shouldn't the result of the query be stored
in the table?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

 

[rich]

Hi John,

Thanks for the reply and the explanation.

Can you kindly follow-up on my last question to Allen about the query. What
kind of query should it be?

I don't understand the SQL syntax of this type of logical comparison.

Thanks,
Rich

 

[Allen Browne]

Just type the expression - as it is - into the Field row of your query (all
on the one line.)

John answered your question about storing a calcuated result. Here's more
info about that:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

As always, thanks for the quick reply.

I understand the query, however, shouldn't the result of the query be
stored
in the table?

Also, I'm less clear on the syntax of the query. Should it be:
"Select Process Complete: ([Employee Startdate] Is Not Null........

Should I enter it exactly as (to be) typed in the SQL View?

Thanks,

Rich

Remove the Process_Complete field from your table.

Instead, create a query, and type this expression. (It's all one line):
ProcessComplete: ([Employee start date] Is Not Null)
AND ([Paperwork_submitted_date] Is Not Null)
AND ([Computer_Account_created_date] Is Not Null)
AND ([Email_account _created_date] Is Not Null)
AND ([Access_to_applications_date] Is Not Null)

Then use the query where ever you would have used the table (e.g. form,
report, ...) You never have to worry about the value being wrong, because
Access calculates it as needed.

Part of the functionality of my application is to track progress
through a
(similar to a hiring) process. The process has 5 steps. When each
step
complete, the date that is was completed on is updated in table. Table
looks
like this:

Column Date Type
--------- ------------
Index (Tracking number) (PK)
EmpID
Employee start date Date
Paperwork_submitted_date Date
Computer_Account_created_date Date
Email_account _created_date Date
Access_to_applications_date Date
Process_Complete Yes/No

If all the date fields have a date entered, the Process_Complete column
needs to be set to "Yes", otherwise, it remains the default value of
"No".
The user should not have to update this field.

 

[John W. Vinson]

Can you kindly follow-up on my last question to Allen about the query. What
kind of query should it be?

Just a simple select query. You can use it as the recordsource for a Report or
a Form.

I don't understand the SQL syntax of this type of logical comparison.

Simply put that expression in a vacant field cell.

John W. Vinson [MVP]