Average Data in Access2013

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.

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.

Here is my Union Query if that will help:

SELECT Snf10_2012_ALPHA.[Nursing Home], Snf10_2012_ALPHA.Worksheet, Snf10_2012_ALPHA.[Line Number], Snf10_2012_ALPHA.Column, Snf10_2012_ALPHA.Words
FROM [Copy Of TB KY ONLY] INNER JOIN Snf10_2012_ALPHA ON [Copy Of TB KY ONLY].[Nursing Home] = Snf10_2012_ALPHA.[Nursing Home]

UNION SELECT Snf10_2012_NMRC.[Nursing Home], Snf10_2012_NMRC.Worksheet, Snf10_2012_NMRC.[Line Number], Snf10_2012_NMRC.Column, Snf10_2012_NMRC.Number
FROM [Copy Of TB KY ONLY] INNER JOIN Snf10_2012_NMRC ON [Copy Of TB KY ONLY].[Nursing Home] = Snf10_2012_NMRC.[Nursing Home];

also to do any kind of query with this data I have to convert it into a table... any idea how to turn a Union Query into a table?

use an access SQL view and to convert it to a table:


select t1.* INTO <MyNewTableName> FROM
(<paste your union query minus the semi-colon here>) AS t1;

I am sorry I am not good with SQL at all... So it would be like this:

SELECT (Insert union query like above^^^ comments).*INTO<NEW TABLE NAME>FROM

???

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.

select t1.*INTO<COMBINE ALL KY DATA TO AVERAGE> FROM (<SELECT Snf10_2012_ALPHA.[Nursing Home], Snf10_2012_ALPHA.Worksheet, Snf10_2012_ALPHA.[Line Number], Snf10_2012_ALPHA.Column, Snf10_2012_ALPHA.Words
FROM [Copy Of TB KY ONLY] INNER JOIN Snf10_2012_ALPHA ON [Copy Of TB KY ONLY].[Nursing Home] = Snf10_2012_ALPHA.[Nursing Home]

UNION

SELECT Snf10_2012_NMRC.[Nursing Home], Snf10_2012_NMRC.Worksheet, Snf10_2012_NMRC.[Line Number], Snf10_2012_NMRC.Column, Snf10_2012_NMRC.Number
FROM [Copy Of TB KY ONLY] INNER JOIN Snf10_2012_NMRC ON [Copy Of TB KY ONLY].[Nursing Home] = Snf10_2012_NMRC.[Nursing Home]>) AS t1;


This is what I entered and it comes up with an error----the SELECT statement includes a reserved word or an argumnet name that is misspelled or missing, or the punctuation is incorrect.

oh ok sorry... Well this is what I entered in SQL of new query:

SELECT [Nursing Home], [Worksheet], Avg(val([Words])) As AvgAmt
From ALL KY COMBINE
Where[Column]>0
Group By {Nursing Home], [Worksheet];

I recieved a----Syntax error in FROM clause

ok sorry I fixed it I needed to put [  ] around ALL KY COMBINE... But this still is not what I want... I want the Line Numbers and Column in there if possible... this is my result:

I see what it did... sorry you miss understood... I want a big average of all the specific rows... for example:

Nursing Home.....Worksheet.....Line Number.....Column.....Words
1012587              A00000              100                 200           16709
1012587              A00000              100                 600           15677
1017014              A00000              100                 200           497686
1017014              A00000              100                 600           164763

AVERAGE TABLE=====
Nursing Home......Worksheet.....Line Number.....Column.....Words
ALL                      A00000              100                 200           257197.50
ALL                      A00000              100                 600           90220.00

Open the query in the QBE.
Select the two additional columns.

Access will assume "group by" which is what you want.

I'm sorry how do I open in QBE?

Ok I see now... I cannot open in QBE because it is a Union Query and the only options is SQL view and Data sheet view

All you have to do is to add the additional columns to the select clause and the group by clause.

SELECT [Nursing Home], [Worksheet], [Line Number], [Column], Avg(val([Words])) As AvgAmt
From [ALL KY COMBINE]
Where[Column]>0
Group By [Nursing Home], [Worksheet],[Line Number], [Column];

That didn't do anything but list only the amounts?

Did you add the columns to the Group By clause as well?  

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.

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

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.

In Summary ignore that the Nursing Home column is there...

OK.  Let us know how that works.

SELECT [Worksheet], [Line Number], [Column], Avg(val([Numbers])) As AvgAmt
From [ALL KY COMBINE]
Where[Column]>0
Group By [Worksheet],[Line Number], [Column];

I enter this and it keeps saying Enter Parameter Value??

Thats what is was Great! had I Numbers in stead of Number.. it worked Great thanks!!!

Glad it worked for you.  Have a great weekend.