Solved

Calculate Weekly Usage

Posted on 2012-03-20
9
345 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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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 51

Assisted Solution

by:HainKurt
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_"
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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