Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Average Data in Access2013

Posted on 2014-04-16
23
Medium Priority
?
303 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 39

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 39

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 39

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 39

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 39

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 39

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 39

Accepted Solution

by:
PatHartman earned 2000 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 39

Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

610 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