Finding the average of certain fields in a table 
Author Message
 Finding the average of certain fields in a table

I am using Access 2000:

I need to be able to take the average of certain fields of
a table when I am working in a Form.  I have tried the SQL
function AVG() but for some strange reason my database
doesn't like it and freezes up on me when I use it.

Does anybody have any ideas for me? If so can you step me
through the process or show me an example?

Thanks in Advance!!

-Andrew



Sun, 28 Mar 2004 01:54:26 GMT  
 Finding the average of certain fields in a table

Hi Andrew,

AVG() won't work directly in a form to summarise data from
a number of records, because the form can only 'see' the
record that it's currently showing. You should create a
query that uses AVG() to summarise the column, and find
some way of bringing it through to the form.

e.g. say I've got a form that shows the salary for each
staff member in my company, and I also want to show the
average salary for an employee in his department. I'd
first get the average salaries:

--Definition of qryDepartmentAvgSalary
SELECT DepartmentName, AVG(Salary) AS AvgSalary
FROM tblStaff
GROUP BY DepartmentName;

I'd then link this to my Employee data by department:

--Definition of qrySalaryWithDepartmentAvgSalary
SELECT tblStaff.FullName, tblStaff.Salary,
tblStaff.DepartmentName, qryDepartmentAvgSalary.AvgSalary
FROM tblStaff INNER JOIN qryDepartmentAvgSalary ON
tblStaff.DepartmentName =
qryDepartmentAvgSalary.DepartmentName;

I'd then create my form based on
qrySalaryWithDepartmentAvgSalary

Best wishes,
mike

Quote:
>-----Original Message-----
>I am using Access 2000:

>I need to be able to take the average of certain fields
of
>a table when I am working in a Form.  I have tried the
SQL
>function AVG() but for some strange reason my database
>doesn't like it and freezes up on me when I use it.

>Does anybody have any ideas for me? If so can you step me
>through the process or show me an example?

>Thanks in Advance!!

>-Andrew
>.



Sun, 28 Mar 2004 07:33:59 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. procedure to find certain files from a certain date on harddisk

2. way to find max var char field lengths of fields in SQL Server table

3. Select Field List for RowSource Type but only certain fields

4. Finding highest average....(911!!!)

5. Calculating Average of Grade Fields

6. Create new fields in a table based off of fields in another table

7. Create new field in existing table exactly like field in second table

8. updating fields in table from field in another table

9. Fill field in one table from field in another table

10. How to find all queries that use a specific field in a table

11. How to find Field Names for any table?

12. Finding table field names in VB

 

 
Powered by phpBB® Forum Software