Calculations - Access 97

[Heather]

-I have created my table, form, and report in Access97.

-I want to create queries for my calculations.

-How do I create a query for the total number of Scheduled Safety
Audits, Total number of Completed Safety Audits, and then a query for
the percent of Safety Audits complete, by month?

-For the safety audits, each shift completes a form which creates a
record.

Thank you in advance. Any help is very much appreciated.

 

[Heather]

-An idea came into my brain.
-To show the report by month, I can tell the report to use the safety
audit date field, and a parameter values box comes up for the user to
type the dates they want to show on the report.
-I cant remember exactly how to do this though.

 

[John Vinson]

-I have created my table, form, and report in Access97.

-I want to create queries for my calculations.

-How do I create a query for the total number of Scheduled Safety
Audits, Total number of Completed Safety Audits, and then a query for
the percent of Safety Audits complete, by month?

Since you did not post any description of your table structures, nor
where these calculations should derive their data, it's more than a
little bit difficult to answer your question. You can see your tables;
we cannot.

Can you give us a bit of help here? All I can say right now is that
you should be able to use a calculated field in a Query,
Month([SomeDateField]), and create a Totals query grouping by it (and
probably also grouping by a Year([somedatefield]) calculated field as
well).

John W. Vinson[MVP]

 

[Heather]

My table is:
TSafetyAuditMetric

The field names I want to add are:
SRVItems + OSDItems + ENVItems

Then I want to add the field names:
SRVItemsClosed + OSDItemsClosed + ENVItemsClosed

These all have the data type Number.

Then I want to calculate the percent of the two totals.

I want to show this by month. The Parameter Values Box where the user
puts in the dates they want to see.

 

[John Vinson]

My table is:
TSafetyAuditMetric

The field names I want to add are:
SRVItems + OSDItems + ENVItems

Then I want to add the field names:
SRVItemsClosed + OSDItemsClosed + ENVItemsClosed

These all have the data type Number.

Then I want to calculate the percent of the two totals.

I want to show this by month. The Parameter Values Box where the user
puts in the dates they want to see.

I'm sorry, Heather, this doesn't help much at all.

I have NO idea how the date field relates to these fields, whether
these are independent stored fields or calculated fields, what
constitutes a "closed" item...

Again: you're familiar with your data and your table structures. I'm
not, and my telepathy seems to be on the blink today!

John W. Vinson[MVP]

 

[Heather]

The name of My table is:

TSafetyAuditMetric

The field names, within the TSafetyAuditMetric table, which are the individual field names within the TSafetyAuditMetric table, I want to add together are:
SRVItems OSDItems ENVItems

The field names, within the TSafetyAuditMetric Table which are the individual field names within the TSafetyAuditMetric table I want to add together are:
SRVItemsClosed + OSDItemsClosed + ENVItemsClosed

The field names have a data type of "Number".

"closed" is just part of the individual field name. I have safety
audit items and then safety audit items that have been closed.
I would normally do this in Excel. but this will be a multiuser
environment that shows a monthly trend.

I can email you screen shots if you want. I hope this helps a little.

 

[John Vinson]

I hope this helps a little.

Heather, what would help is if you would answer my clearly phrased
question.

John W. Vinson[MVP]

 

[Heather]

My Query looks like this:

Field: SRVItems OSDItems
ENVItems
Table: TSafetyAuditMetric TSafetyAuditMetric
TSafetyAuditMetric

These are Number Fields.

I want to know what the SUM function is to add these 3 fields together.
I can add them individually with the Group By function.

I hope this is asking my question better. Forget about all the other
notes. Thank you so much for your help.

 

[John Vinson]

My Query looks like this:

Field: SRVItems OSDItems
ENVItems
Table: TSafetyAuditMetric TSafetyAuditMetric
TSafetyAuditMetric

These are Number Fields.

I want to know what the SUM function is to add these 3 fields together.
I can add them individually with the Group By function.

I hope this is asking my question better. Forget about all the other
notes. Thank you so much for your help.

In a vacant Field cell type:

MySum: NZ([SRVItems]) + NZ([OSDItems]) + NZ([ENVItems])

Group By and Sum work across records, not across fields - the above
expression will calculate a new temporary field by adding the three
existing fields. The NZ() function will ensure that you get a sum even
if one of the fields is blank (NULL).

John W. Vinson[MVP]

 

[Heather]

Thank You! Thank You! Thank You!

Is there any good books or websites you would recommend for more of
these expressions?