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


Calculate Weekly Usage

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

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.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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.

LVL 60

Assisted Solution

HainKurt earned 750 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 40

Assisted Solution

als315 earned 750 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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…

618 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