Calculate Weekly Usage

Posted on 2012-03-20
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

Question by:JazCat
  • 4
  • 2
  • 2
  • +1
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

LVL 39

Expert Comment

ID: 37743905
You are using this values as field names (... AS Current_Gas). In this case add table name to your original fields:

Author Comment

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 51

Assisted Solution

HainKurt 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_"
LVL 39

Assisted Solution

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

Author Comment

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

Accepted Solution

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.

Author Closing Comment

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.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

929 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

10 Experts available now in Live!

Get 1:1 Help Now