Solved

Calculate Weekly Usage

Posted on 2012-03-20
9
347 Views
Last Modified: 2012-06-09
I have a table, updated weekly since the beginning of the year, which contains the fields Equipment_ID, FiscalWeek and Gas_Level. I need a query that will calculate the weekly usage, by Equipment ID, for each site.

I am close but, when I run the query, I get a popup asking me to enter Prior and Current Readings. If I just click enter it runs fine but I would like to get rid of the popup.

SELECT tbl_YTD_Weekly_Readings.[Equipment_ID], "2" AS FiscalWeek, Format([Prior_Gas]-[Current_Gas],"00.0") AS Weekly_Usage, Sum(IIf([tbl_YTD_Weekly_Readings].[FW]=122,[tbl_YTD_Weekly_Readings].[He Level],0)) AS Current_Gas, Sum(IIf([tbl_YTD_Weekly_Readings].[FW]=121,[tbl_YTD_Weekly_Readings].[He Level],0)) AS Prior_Gas
FROM tbl_YTD_Weekly_Readings
GROUP BY tbl_YTD_Weekly_Readings.[Equipment_ID], "2", Format([Prior_Gas]-[Current_Gas],"00.0");

Open in new window

0
Comment
Question by:JazCat
[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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743902
Not sure without a sample dB to examine...

Basically a Parameter prompt will occur when Access does not recognize the field or control.
...Or the Field/Control is not available when the code is run.
(The form that supplies these values is closed0

So obviously "Prior and Current Readings" fall under one of these scenarios.
So start there with your troubleshooting

JeffCoachman
0
 
LVL 40

Expert Comment

by:als315
ID: 37743905
You are using this values as field names (... AS Current_Gas). In this case add table name to your original fields:
Format([Prior_Gas]-[Current_Gas],"00.0")
to
Format(tbl_YTD_Weekly_Readings.[Prior_Gas]-tbl_YTD_Weekly_Readings.[Current_Gas],"00.0")
0
 

Author Comment

by:JazCat
ID: 37743950
als315, the Prior and Current Gas values are calculated within the same query. For example, Current_Gas is the reading if, in the sample snippet, the Fiscal Week = 122. 122, in case anyone cares, is just the way the database assigns for Year 12 FiscalWeek 2.

Jeff, could that be because the Current and Prior Readings are calculated? Would it be better to create the Calculated fields in one query then, using that query, create the differences field?  I can do that.
0
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!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37744103
<Jeff, could that be because the Current and Prior Readings are calculated? Would it be better to create the Calculated fields in one query then, using that query, create the differences field?>
Again, not sure with no sample DB to examine...

Expert als315 is phenomenal at these types of questions.
To avoid confusion, I will let you continue on with him.

Jeff
0
 
LVL 55

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 total points
ID: 37744458
try this

SELECT [Equipment_ID], "2" AS FiscalWeek, Format([Prior_Gas]-[Current_Gas],"00.0") AS Weekly_Usage, Sum(IIf([FW]=122,[He Level],0)) AS Sum_Current_Gas, Sum(IIf([FW]=121,[He Level],0)) AS Sum_Prior_Gas
FROM tbl_YTD_Weekly_Readings
GROUP BY [Equipment_ID], "2", Format([Prior_Gas]-[Current_Gas],"00.0");

do not use same alias for your columns, I prefixed them with "sum_"
0
 
LVL 40

Assisted Solution

by:als315
als315 earned 250 total points
ID: 37745774
@Jeff: Two heads is always better then one :)

@ JazCat:
Your question (could that be because the Current and Prior Readings are calculated?) is absolutely correct. There are two possible solutions:
1. Do any calculations with these values in next query
2. Don't use calculated field in same query, use source data (you will not be able to use format function in this case):
SELECT tbl_YTD_Weekly_Readings.[Equipment_ID], "2" AS FiscalWeek, Sum(IIf([tbl_YTD_Weekly_Readings].[FW]=121,[tbl_YTD_Weekly_Readings].[He Level],0)-IIf([tbl_YTD_Weekly_Readings].[FW]=122,[tbl_YTD_Weekly_Readings].[He Level],0)) AS Weekly_Usage 
FROM tbl_YTD_Weekly_Readings
GROUP BY tbl_YTD_Weekly_Readings.[Equipment_ID], "2";

Open in new window


I can agree with Jeff - sample database is very helpful
0
 

Author Comment

by:JazCat
ID: 37755816
Sorry, got pulled off onto another more urgent project. Will see if I can post a sample database tomorrow.
0
 

Accepted Solution

by:
JazCat earned 0 total points
ID: 38045826
I apologize, totally forgot this was open. What I ended up doing was creating two tables, tbl_prior_readings and tbl_current_readings, with exactly the same data. Then I used them in a query where I set the criteria for the tbl_prior_reading![prior_FW] to tbl_current_readings![current_FW]-1. That worked and let me make my calculations with no problems.
0
 

Author Closing Comment

by:JazCat
ID: 38065245
I tried the other suggestions but they didn't work totally. Reading them did give me more ideas which ultimately ended up fixing the problem.
0

Featured Post

Industry Leaders: 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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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