OK on the dates, so specifically, what do you want to see on the subform?
OK on the dates, so specifically, what do you want to see on the subform?
I need to see Total Usage (what's already there as field "Usage"), Usage Date (also already there), Weekly Usage, Monthly Usage, and Monthly Iron Days.
Weekly Usage = Usage - Previous Usage
Monthly Usage = Usage - 4 Weeks(entries) Ago Usage
Monthly Iron Days = Usage - 4 Weeks(entries) Ago Usage /(Divided by) 8
So you're saying I can get all of this to display on the current Usage subform, are you talking now about creating another subform to hold this data?
The kicker is I want this subform to be in the Usage History tab next to the current usage subform and only display the usage pertaining to the forms currently displayed Asset.
Wow I just thought about something, probably something you may have already mentioned... Couldn't I just add some new unbound controls to the current subform with the calculations in them???
Is the attached what you are after? I only did the previous week value since the other queries have to be set up for your other calculations.
That is EXACTLY what I was after... Dang dude, thank you! I am going to examine what you did and see if I can't tackle the other ones myself so I can learn something.
Where should I be modifying to change it to be searching 3 records before current? I am looking in Query 1 and I'm thinking it's in either MonthEndUsage or MonthEnding (I have copied the query and made it MonthEndUsage / MonthEnding). The select strings are above my skill level though, I am uncertain what some of the references do such as "top 1".
Also what does the # signify in the text box?
You need to create a new query that uses one of the queries I created. If you modify the ones I provided, it will mess other things up.
Previous 3 records? You mean you want to get the previous month's data correct? You will need to use some of the date manipulation functions built into Access. You might need dateserial(), dateadd() to name a few.
The # signs are used for dates. The dates have to be enclosed between the # signs. Text values have to be enclosed within single or double quotes depending on what you are doing. Number fields do not need to be enclosed in anything.
Yeah I copied one of your queries yesterday to try and begin modifying it to work for monthly usage. I will do some research on the functions you listed to see how I can get them to work for me. Thanks for the explanation on the # signs, it makes more sense now looking at the control source for the week usage box on the subform.You need to create a new query that uses one of the queries I created. If you modify the ones I provided, it will mess other things up.
Previous 3 records? You mean you want to get the previous month's data correct? You will need to use some of the date manipulation functions built into Access. You might need dateserial(), dateadd() to name a few.
The # signs are used for dates. The dates have to be enclosed between the # signs. Text values have to be enclosed within single or double quotes depending on what you are doing. Number fields do not need to be enclosed in anything.
I'll report back with what happens when I try to get the monthly usage to work.
EDIT: dateserial() looks like it should work for this, but I have a question about it that you probably know the answer to. Is there a way to control only one part of the date? What if I don't want to specify a year or day (otherwise I will have to update it yearly). Is there an operator that I can use for "any"? Something like dateserial(#,#-3,#) or something to that effect?
You can use variables within the function. For example, in Query3 I provided you have the PeriodMonth and PeriodYear, you can use those variables in the dateserial() function to construct a date.
dateserial(PeriodYear,PeriodMonth,1) would yield the first of the (variable) month/year
I don't know why this is so confusing to me. These queries are just hard for me to make sense of.
I show query1 below with the 2 subqueries in red and blue
The query shown in black is your base query; I've used an alias for the Usage field so that I can distinguish starting usage from the next usage entry.Code:SELECT AssetUsage.AssetID, AssetUsage.Usage AS WeekStartUsage, (Select top 1 Q1.usage from AssetUsage as Q1 WHERE Q1.AssetID=AssetUsage.AssetID and Q1.UsageDate>AssetUsage.UsageDate Order by Q1.AssetID, Q1.Usagedate) AS WeekEndUsage, (Select top 1 Q2.usagedate from AssetUsage as Q2 WHERE Q2.AssetID=AssetUsage.AssetID and Q2.UsageDate>AssetUsage.UsageDate Order by Q2.AssetID, Q2.Usagedate) AS WeekEnding, WeekEndUsage-WeekStartUsage AS UsageAmount FROM AssetUsage ORDER BY assetusage.AssetID, assetusage.usagedate;
The query shown in red finds the usage for the asset that has a date greater than the StartUsageDate and has the same asset number as the base query (shown in black). The TOP 1 predicate shown in the red subquery just takes the first record that meets the criteria. Note that all the queries are ordered (sorted) by assetID and usage date. Given that the default order is always ascending, you want to find the usage that is next in line for the asset that is also greater than the current record's usage date. A subquery can only return 1 field from 1 record so the Top 1 limits it to the first record that meets our criteria. Since the subquery can only return 1 record from 1 field, I had to add the second subquery (in blue) to also return the date that corresponds to the usage from the red subquery. Basically, the blue subquery is the same as the red but I just brought the date in rather than the usage value. A subquery must not have the same name as the base query therefore I named them Q1 and Q2 (red and blue respectively).
When I use dateserial() how do I reference the PeriodYear/Month as they are in a different query. I tried using QueryX.PeriodYear and Month (X=whatever the number of the query it is that it needs to point to) and it's giving me the enter parameter value box.You can use variables within the function. For example, in Query3 I provided you have the PeriodMonth and PeriodYear, you can use those variables in the dateserial() function to construct a date.
dateserial(PeriodYear,PeriodMonth,1) would yield the first of the (variable) month/year
Edit: Nevermind, needed to add the query to the main FROM statement.
Should it look something like this? (i have renamed all of the queries that you originally made so that they were more descriptive and knew what was what)
Edit: Ok I think I have something backwards, I think I added the dateserial() part on the wrong side of the equation.Code:SELECT AssetUsage.AssetID, AssetUsage.Usage AS MonthStartUsage, (Select top 1 Q1.usage from AssetUsage as Q1 WHERE Q1.AssetID=AssetUsage.AssetID and Q1.UsageDate>dateserial(qryMonthlyUsageCalc2.PeriodYear,qryMonthlyUsageCalc2.PeriodMonth,1-30) Order by Q1.AssetID, Q1.Usagedate) AS MonthEndUsage, (Select top 1 Q2.usagedate from AssetUsage as Q2 WHERE Q2.AssetID=AssetUsage.AssetID and Q2.UsageDate>AssetUsage.UsageDate Order by Q2.AssetID, Q2.Usagedate) AS MonthEnding, MonthEndUsage-MonthStartUsage AS UsageAmount, AssetUsage.UsageDate AS UsageStartDate FROM AssetUsage, qryMonthlyUsageCalc2 ORDER BY AssetUsage.AssetID, AssetUsage.UsageDate;
I'm not sure. If the query returns the correct data, then it is correct. In other words, you have do some manual calcs to verify that the query is returning the values you expect.Should it look something like this? (i have renamed all of the queries that you originally made so that they were more descriptive and knew what was what)
Ok so it looks like I have it looking back 30 days now, but to me at least it seems as if the monthstart/end dates are reversed? Also, some of the query results show negative, some show positive, and some of the source information looks likes it randomly coming up in reverse order? ie. all of my usage entries are increasing in value, but in the query some show up in reverse, others do not?
It's qryMonthlyUsageCalc1 that I'm trying to work with. Thx.
The FROM clause in your query is a problem
FROM AssetUsage, qryMonthlyUsageCalc2
This will return a Cartesian Product of the assetusage table and qryMonthlyUsageCalc2 which is not what you want. I missed that earlier, sorry.
On your form you have the control labeled as follows: Previous Months Usage
Is it just the sum of the usages from all previous months prior to the current month/year?
Yes from what I think you are thinking. The equation should pretty much be exactly the same as it is for getting previous weeks usage, except it should span a month long period opposed to a week long period.
I will have to research a Cartesian Product, you lost me on that one!
Edit: Forgot to mention, it CAN be a rolling month, shown at every week. It doesn't have to be only on the weeks that make a new month.