• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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?

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

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

Is this possible???
0
alesha711
Asked:
alesha711
  • 14
  • 8
1 Solution
 
PatHartmanCommented:
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
 
alesha711Author Commented:
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];
0
 
alesha711Author Commented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DultonCommented:
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;
0
 
alesha711Author Commented:
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

???
0
 
PatHartmanCommented:
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.
0
 
alesha711Author Commented:
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.
0
 
alesha711Author Commented:
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
0
 
alesha711Author Commented:
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:

result
0
 
alesha711Author Commented:
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
0
 
PatHartmanCommented:
Open the query in the QBE.
Select the two additional columns.

Access will assume "group by" which is what you want.
0
 
alesha711Author Commented:
I'm sorry how do I open in QBE?
0
 
alesha711Author Commented:
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
0
 
PatHartmanCommented:
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];
0
 
alesha711Author Commented:
That didn't do anything but list only the amounts?
0
 
PatHartmanCommented:
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.
0
 
alesha711Author Commented:
i figured out how to seperate the Words and Number column finally.. Will this make it easier?

seperate columns
0
 
alesha711Author Commented:
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...
0
 
PatHartmanCommented:
OK.  Let us know how that works.
0
 
alesha711Author Commented:
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??
0
 
PatHartmanCommented:
That is what Access does when you have a typo in a column name.  The name it is using will give you a clue where the typo is.
0
 
alesha711Author Commented:
Thats what is was Great! had I Numbers in stead of Number.. it worked Great thanks!!!
0
 
PatHartmanCommented:
Glad it worked for you.  Have a great weekend.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 14
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now