Solved

Average Data in Access2013

Posted on 2014-04-16
23
284 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
  • 14
  • 8
23 Comments
 
LVL 34

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
 
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 34

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 34

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 34

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 34

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 34

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 34

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 34

Expert Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now