Average Data in Access2013

I have a large Data Base with 815,075 rows. Each row has a Nursing Home #, Worksheet, Line Number, Column, and either a text answer or a $$$. I want to average all these amounts, is there and easy way?

After you scroll through the data base this is what then Next Nursing Home would look like

Where it has A00000000......100......200..... and then the number I would like to average these numbers.

Select [Nursing Home #], [Worksheet], Avg(val([Words])) As AvgAmt
From YourTable
Where [Column] > 0
Group By [Nursing Home #], [Worksheet];

Having numeric values in text fields is always problematic. I'm hoping that this isn't your data but it is what you are getting from some other application. If it is your data, the table needs to be changed to include a unique identifier and to have separate columns for the text and numeric values. Also, if you are redoing the structure, get rid of the special characters and embedded spaces in the column names.

0

alesha711AssocaiteAuthor Commented: 2014-04-16

I am getting the data from somewhere else. What happened was I had 1 table with the Numbers and 1 table with the Text answers. I needed to join them together so I could average them accurately and the only way I could figure out how to do this was with a Union Query, I wanted them in seperate columns but I could not figure out how.

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

You do NOT have to create a table to use the query I posted. In Access, Select queries (of which Union is a type) are interchangeable with tables for most purposes. Simply use the name of the union query in place of the table name.

If you are getting too much detail, you need to remove columns from the select and Group by clauses to increase the summarization.

As an example, if you wanted to get an average of salary for a department you would use:

Select Dept, Avg(Salary)
From YourTable
Group By Dept;

If you added Employee name:
Select Dept, EmployeeName, Avg(Salary)
From YourTable
Group By Dept:
you would end up with a record for each individual employee. The more columns you include in the query, the less summarization you have.

0

alesha711AssocaiteAuthor Commented: 2014-04-18

i figured out how to seperate the Words and Number column finally.. Will this make it easier?

0

alesha711AssocaiteAuthor Commented: 2014-04-18

Also I have realized why it only give me amount, I want to leave out the Nursing Homes Column when I Average. If I have it average with the Nursing Home ID in the Query then it will only have 1 amount for each where if I leave out the Nursing Home it will have multiple Worksheet, Line Number, and Column amounts to average up.

Featured Post

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.