Solved

Average Data in Access2013

Posted on 2014-04-16
23
294 Views
Last Modified: 2014-04-18
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
Comment
Question by:alesha711
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 8
23 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 40004761
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
 

Author Comment

by:alesha711
ID: 40004779
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
 

Author Comment

by:alesha711
ID: 40004782
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 6

Expert Comment

by:Dulton
ID: 40004798
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
 

Author Comment

by:alesha711
ID: 40004826
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40004838
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
 

Author Comment

by:alesha711
ID: 40004845
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
 

Author Comment

by:alesha711
ID: 40004868
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
 

Author Comment

by:alesha711
ID: 40004887
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
 

Author Comment

by:alesha711
ID: 40004908
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40005065
Open the query in the QBE.
Select the two additional columns.

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

Author Comment

by:alesha711
ID: 40005107
I'm sorry how do I open in QBE?
0
 

Author Comment

by:alesha711
ID: 40005169
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40005360
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
 

Author Comment

by:alesha711
ID: 40008994
That didn't do anything but list only the amounts?
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40009178
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
 

Author Comment

by:alesha711
ID: 40009186
i figured out how to seperate the Words and Number column finally.. Will this make it easier?

seperate columns
0
 

Author Comment

by:alesha711
ID: 40009201
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40009213
OK.  Let us know how that works.
0
 

Author Comment

by:alesha711
ID: 40009323
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
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40009364
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
 

Author Comment

by:alesha711
ID: 40009583
Thats what is was Great! had I Numbers in stead of Number.. it worked Great thanks!!!
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40009613
Glad it worked for you.  Have a great weekend.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question