?
Solved

Calculate Weekly Usage

Posted on 2012-03-20
9
Medium Priority
?
353 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
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 61

Assisted Solution

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

Assisted Solution

by:als315
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
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

864 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