Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Codewalkers ForumsOther TechnologiesDatabase Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
Old August 12th, 2013, 03:13 PM
francorona francorona is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Aug 2013
Posts: 3 francorona User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 45 sec
Reputation Power: 0
Access/Excel - Averaging Problem

I have a access dadabase with a query that totals two columns for a models column (turn in miles - issue miles). I need to know how to get another column to give me the average of the totals column but only for one particular model, if this is possible.

Reply With Quote
Old August 14th, 2013, 09:45 PM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Novice (500 - 999 posts)
 
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 605 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 Days 22 h 32 m 27 sec
Reputation Power: 10
I'll help you if you give me an example of how the tables look currently and what you would expect the average to be, given your example table. Also, please give me the existing query that you're using to get the total you mentioned.

For instance, give me something like this:
Models table:
Code:
id     miles
1         10
2         20
3         60
4         15


The average miles in the above table would be 26.25.

The query would look something like this:
Code:
SELECT AVG(miles) avgMiles FROM Models;
__________________
Sir, a desire of knowledge is the natural feeling of mankind; and every human being, whose mind is not debauched, will be willing to give all that he has to get knowledge.

Reply With Quote
Old August 15th, 2013, 09:15 AM
francorona francorona is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Aug 2013
Posts: 3 francorona User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 45 sec
Reputation Power: 0
Quote:
Originally Posted by jamestrowbridge
I'll help you if you give me an example of how the tables look currently and what you would expect the average to be, given your example table. Also, please give me the existing query that you're using to get the total you mentioned.

For instance, give me something like this:
Models table:
Code:
id     miles
1         10
2         20
3         60
4         15


The average miles in the above table would be 26.25.

The query would look something like this:
Code:
SELECT AVG(miles) avgMiles FROM Models;


Ok. Here is the SQL for the query:
SELECT [Rotational Grid].[Grid Model], [Rotational Grid].ADMIN, [Rotational Grid].[Issue Mile], [Rotational Grid].[Turn In Miles], Sum([Turn In Miles]-[Issue Mile]) AS [Total Miles], [Rotational Grid].[Issue Hours], [Rotational Grid].[Turn In Hours], Sum([Turn In Hours]-[Issue Hours]) AS [Total Hours]
FROM [Rotational Grid]
GROUP BY [Rotational Grid].[Grid Model], [Rotational Grid].ADMIN, [Rotational Grid].[Issue Mile], [Rotational Grid].[Turn In Miles], [Rotational Grid].[Issue Hours], [Rotational Grid].[Turn In Hours]
HAVING ((([Rotational Grid].[Grid Model]) Is Not Null) AND (([Rotational Grid].ADMIN) Is Not Null) AND ((Sum([Turn In Miles]-[Issue Mile])) Is Not Null)) OR (((Sum([Turn In Hours]-[Issue Hours])) Is Not Null));

In the report I need two more columns that would show the average for each total column but by model type. For example: say the unit drew 5 ea M1151 and 8 ea M998, I will need the average for the totals for M1151 if there was no miles and/or hours recorded for that vehicle. Then also for the M998 if the miles and hours wasn't recorded.

See, this is what happens: when a piece of equipment (Model) is issued, they are supposed to record the miles and hours on the hand receipt, but sometines does not. So that is why they created the average column to capture the average miles/hours for that type of Model.

I really appreciate your help with this, I am not even sure it is possible to do.....currently we are just exporting the report into excel to get the averages but they would like the report to automatically calculate.

Thanks again! Fran

Reply With Quote
Old August 15th, 2013, 11:36 AM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Novice (500 - 999 posts)
 
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 605 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 Days 22 h 32 m 27 sec
Reputation Power: 10
It sounds like the average columns couldn't simply be added as another column to the same query. There would be two averages (one for miles and one for hours) that average the existing query's total columns, and a third column with their related model.

So you'd have something like:
Code:
Model     AvgTotalMiles    AvgTotalHours
ModelA                  15                      2
ModelB                  60                      3


I think this query will give you that, so you'll have to use both query's. One for totals and one for average total.

Code:
SELECT
	totalsView.[Grid Model],
	AVG(totalsView.[Total Miles]) AS AvgTotalMiles,
	AVG(totalsView.[Total Hours]) AS AvgTotalHours
	FROM (
		SELECT
			[Rotational Grid].[Grid Model],
			[Rotational Grid].ADMIN,
			[Rotational Grid].[Issue Mile],
			[Rotational Grid].[Turn In Miles],
			Sum([Turn In Miles]-[Issue Mile]) AS [Total Miles],
			[Rotational Grid].[Issue Hours],
			[Rotational Grid].[Turn In Hours],
			Sum([Turn In Hours]-[Issue Hours]) AS [Total Hours]
		FROM
			[Rotational Grid]
		GROUP BY
			[Rotational Grid].[Grid Model],
			[Rotational Grid].ADMIN,
			[Rotational Grid].[Issue Mile],
			[Rotational Grid].[Turn In Miles],
			[Rotational Grid].[Issue Hours],
			[Rotational Grid].[Turn In Hours]
		HAVING (
			(
				(
					[Rotational Grid].[Grid Model]
				) Is Not Null
			) AND (
				(
					[Rotational Grid].ADMIN
				) Is Not Null
			) AND (
				(
					Sum(
						[Turn In Miles]-[Issue Mile]
						)
				) Is Not Null
			)
		) OR (
			(
				(
					Sum(
						[Turn In Hours]-[Issue Hours]
						)
				) Is Not Null
			)
		)
	) totalsView
		GROUP BY totalsView.[Grid Model];

Reply With Quote
Old August 15th, 2013, 01:36 PM
francorona francorona is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Aug 2013
Posts: 3 francorona User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 45 sec
Reputation Power: 0
Quote:
Originally Posted by jamestrowbridge
It sounds like the average columns couldn't simply be added as another column to the same query. There would be two averages (one for miles and one for hours) that average the existing query's total columns, and a third column with their related model.

So you'd have something like:
Code:
Model     AvgTotalMiles    AvgTotalHours
ModelA                  15                      2
ModelB                  60                      3


I think this query will give you that, so you'll have to use both query's. One for totals and one for average total.

Code:
SELECT
	totalsView.[Grid Model],
	AVG(totalsView.[Total Miles]) AS AvgTotalMiles,
	AVG(totalsView.[Total Hours]) AS AvgTotalHours
	FROM (
		SELECT
			[Rotational Grid].[Grid Model],
			[Rotational Grid].ADMIN,
			[Rotational Grid].[Issue Mile],
			[Rotational Grid].[Turn In Miles],
			Sum([Turn In Miles]-[Issue Mile]) AS [Total Miles],
			[Rotational Grid].[Issue Hours],
			[Rotational Grid].[Turn In Hours],
			Sum([Turn In Hours]-[Issue Hours]) AS [Total Hours]
		FROM
			[Rotational Grid]
		GROUP BY
			[Rotational Grid].[Grid Model],
			[Rotational Grid].ADMIN,
			[Rotational Grid].[Issue Mile],
			[Rotational Grid].[Turn In Miles],
			[Rotational Grid].[Issue Hours],
			[Rotational Grid].[Turn In Hours]
		HAVING (
			(
				(
					[Rotational Grid].[Grid Model]
				) Is Not Null
			) AND (
				(
					[Rotational Grid].ADMIN
				) Is Not Null
			) AND (
				(
					Sum(
						[Turn In Miles]-[Issue Mile]
						)
				) Is Not Null
			)
		) OR (
			(
				(
					Sum(
						[Turn In Hours]-[Issue Hours]
						)
				) Is Not Null
			)
		)
	) totalsView
		GROUP BY totalsView.[Grid Model];


Thank you so much! It worked for a seperate report.....I'm not sure of how I would create one with both sets of data....but we will work with both for now.

Reply With Quote
Old May 26th, 2017, 05:32 AM
robinsonalex760 robinsonalex760 is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: May 2017
Posts: 2 robinsonalex760 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 34 m 35 sec
Reputation Power: 0
Smile


Thank you, I had almost the same problem, and I could managed to solve that problem with your help

Reply With Quote
Reply

Viewing: Codewalkers ForumsOther TechnologiesDatabase Help > Access/Excel - Averaging Problem


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap