Text File Summaries

JeepsR4Mud

Registered User.
Local time
Today, 00:50
Joined
Sep 23, 2002
Messages
70
Is there a way to calculate summaries for text fields?

I am designing a database for work. There are 33 sites. I have a table that records the new employee injuries by week and site.

I can do a count and determine the total number of injuries per site, but I'd like to know the average per week, and since the database in ongoing, the min and max/week.

I tried doing subtotals of each week, and calling that field weektotal. But, evidently the avg, min, and max fields won't work with unbound fields. I also tried copying the count command: AVG(COUNT([weektotal])) but you cant do two functions (AVG and COUNT in the same field.

Thanks.

Gayle Ann
 
I don't believe you can do the calculations based on text fields (at least I haven't figured out how), just fields from tables or queries. I suggest you use the DCount, DSum, DAvg, ..... functions to do these calculations. They will allow you to do calculations on the fields in a table based on certain criteria. Have a look in the Help files for the proper syntax and some examples.

Another way might be to create queries based on selected criteria and base the calculations on the fields in the queries.

Paul
 
Hello,

Can the AVG, MIN, MX functions be calculated from unbound fields?

I tried looking at the help topics, and really didn't receive much insight.

I can total the number of records per site per week, but can't seem to do anything with those figures.

Thanks.

Gayle Ann
 
No, I don't think you can use them on unbound fields.

I use the DSUM and other D functions with the same queries that I use to generate my report to do such calculations.

DSum("[AMOUNT]","qry_comp_amt_sub","[COMP_ID] = '" & [COMP_ID] & "' AND [client_id] = " & [client_id])

The DSUM function will grab the data from the recordset instead of the unbound field.

Once you place this value in a field you can use it for calculations.

Similarly, you could use the DAVG function:

DAvg("[Freight]", "Orders", "[ShipCountry] = 'UK' AND [ShippedDate] >= #1-1-02#")

You may also consider building your calculations into your query so the results become part of the recordset.

Hope this helps.

Paul
 
I don't understand the D* functions, and can't seem to find reference to them in help.

In a query can I total the number of records, and then find the MIN, MAX, and AVG of the number of records?

Thanks.

Gayle Ann
 
To do averages over groups, either create a break for the group if you are doing the average in a report or use the group by if you are calculating the average in a query, you may have to summarize the original data before you can calculate the average. Summarize the data using query1. Then average it in query2.

Query1:
Select Week(YourDate) As WeekDate, Count(*) As CountForWeek
From YourTable
Group By Week(YourDate);

Query2:
Select Avg(CountForWeek) As AvgForWeek
From Query1;
 
Can I make a suggestion? Why dont you use the date fileds and build a query to run a date range. Use this query as the control source for a copy of the form you use. By doing so you can not only get your averages by the week, but for any week, month, quarter, or whatever time period you select. I use something similar to run time reports on based on starting date and ending date. Once you have the query built you can set your "injury" and "site" fileds to do totals and this will give you the totals for the time period you ran with the query.

Marty
 

Users who are viewing this thread

Back
Top Bottom