Query only certain rows?

[jzwp11]

Are you basically wanting a running sum of the usage?


See if the attached is what you want (subform in your asset detail form). Since qryMonthlyUsageCalc2 already calculates the monthly usage values, we just need to add them up for all months prior to the current record's date and assetID. Are we on the same page?

[10 Gauge]

That would work but it needs to cut off after it reaches 4 weeks (or records) worth of usage. I added a few more entries and it just keeps totaling. I'm not sure if you have it setup to read by date or by entry number though, because I suppose it could be done either way.

The current way would work if it's done by entries instead of dates (where it would just sum the 4 most current Previous Weeks Usage calculations). As a test I have just been adding in usage monthly instead of weekly which is how it will actually be entered.

[10 Gauge]

I have changed the structure of the usage entries to match how they will be used in practice.

Now for example, 12/28's monthly usage should be 6000 ((12/28Wkly: 1000) + (12/21Wkly: 2000) + (12/14Wkly: 1000) + (12/7Wkly: 2000)), 12/21's monthly usage should be 7000, 12/14's monthly usage should be 6000, etc. etc.

Not sure if that makes sense or not.

[jzwp11]

The query groups by month and year so it would sum all entries within a month. Now if you do it by week that is a little more problematic. Access does have a function that returns the week number in which a particular date occurs; we might be able to use that.

You say the following:

... instead of weekly which is how it will actually be entered.

Does each asset get its usage recorded on the same day? What day of the week? What happens if on the day the usage is to be recorded, there is no one in the office (holiday for example)? What is your reference point when you say "weekly"--Monday? Friday? etc?

Will this practice ever change?

What would happen if someone decides that they want the usage recorded after each use?

The problem I see is that if we group & sum weekly, we will lose traceability to the actual date which would mean that a week might include data from 2 months if the first of the month falls in the middle of the week. I you always reference everything by week rather than month it probably would be OK, but then I'm not sure what we would do with "monthly iron days amount". If monthly iron days is really based on the previous 4 weeks and not a calendar month then it would not be a problem

[10 Gauge]

The query groups by month and year so it would sum all entries within a month. Now if you do it by week that is a little more problematic. Access does have a function that returns the week number in which a particular date occurs; we might be able to use that.

You say the following:



Does each asset get its usage recorded on the same day? What day of the week? What happens if on the day the usage is to be recorded, there is no one in the office (holiday for example)? What is your reference point when you say "weekly"--Monday? Friday? etc?

Will this practice ever change?

What would happen if someone decides that they want the usage recorded after each use?

The problem I see is that if we group & sum weekly, we will lose traceability to the actual date which would mean that a week might include data from 2 months if the first of the month falls in the middle of the week. I you always reference everything by week rather than month it probably would be OK, but then I'm not sure what we would do with "monthly iron days amount". If monthly iron days is really based on the previous 4 weeks and not a calendar month then it would not be a problem

Typically all pieces of equipment are recorded on the same day of each week (friday). But it's not hard science, if there's a holiday or something then it will either get recorded thursday or the upcoming monday. If a few extra days are totaled in, it's not a huge deal. This isn't so critical that it has to be that exact.

Being that it's a rolling 4 week total, it's ok if it has data from 2 months because that's actually how it's supposed to be.

Monthly iron days would also be a rolling 4 week total just divided by 8, the exact same query used for monthly usage should be able to used just with an extra division in the output equation.

[jzwp11]

OK, it is starting to make more sense. Now I just have to figure out a way to group by week. I'll try to come up with something over the weekend time permitting.

[10 Gauge]

OK, it is starting to make more sense. Now I just have to figure out a way to group by week. I'll try to come up with something over the weekend time permitting.

Awesome, take your time. I'm not exactly pressed to get this out the door even though I was questioned today when I would have this ready. I told the boss "when it's done, i'll let you know" lol.

[jzwp11]

I think I got it; please see the attached DB

[10 Gauge]

Holy moly jzwp11 it works like a dream! I did get an error when I loaded the DB though, but it didn't seem to effect the 4 week total working properly.

Attached is a SS of the error I got.



Edit: I only got the error on the first load, once I closed it and re-opened it the error did not come back up.

[jzwp11]

I had initially created a custom function (by the name indicated in the error), but then figured out I could accomplish the same thing with the DLast() function.

[10 Gauge]

I had initially created a custom function (by the name indicated in the error), but then figured out I could accomplish the same thing with the DLast() function.

No worries at all!

I have started to add some data for our equipment and I have been noticing a few strange'ish things about the calculation for the previous 4 week usage column.

It seems to only start after 5-7 entries, even after there is more than a months worth of data. Then I found something peculiar when a piece of equipment is not used for a period of time (I think it has to do with the weekly usage #'s being the same). It's hard to explain I feel the best way is to show you what happens.

I have attached a copy of the DB with some information in it. Please look @ the totals it displays for Unit # 401 as the best reference. TIA jzw.

Disregard the Monthly Iron Days column, I added that as a placeholder.

[jzwp11]

In the expression for the control, I used the following:

=IIf([usage]-DLast("usage","qryAssetUsageSorted","assetID=" & [assetID] & " and abs(datediff('d', usagedate,#" & [UsageDate] & "#)) >=28")<0,Null,[usage]-DLast("usage","qryAssetUsageSorted","assetID=" & [assetID] & " and abs(datediff('d', usagedate,#" & [UsageDate] & "#)) >=28"))

As you can see, I used >=28 for the number of days difference between the two dates. This was an attempt to capture the next most recent date if the value was not recorded on the exact same day of the week. This did not work well for the 401 asset. The IIF() function was also causing some issues. So simplifying to just the following seems to take care of those issues

=[usage]-DLast("usage","qryAssetUsageSorted","assetID=" & [assetID] & " and datediff('d',usagedate, #" & [UsageDate] & "#) =28")

I also changed the sorting on the usage date field in the query called qryAssetUsageSorted from ascending to descending so it corresponded with how you display your records in the subform.

The modified DB is attached.

I also noticed another issue with your database. If I try to run a query like the following

SELECT AssetUsage.AssetID, AssetUsage.UsageDate, datediff("d",usagedate, date()) as differenceofdays
FROM AssetUsage;

I get an error message saying that there is an undefined function "date" in the expression. This leads me to believe that you have used the word date somewhere in your database which has killed the date() function. This may lead to problems later on, so you may want to find out where you call something by the word date. The word Date is a reserved word in Access.

You can remove the greater than sign but then when you don't have a date exactly 28 days prior then you will not get a result.

[10 Gauge]

Thanks jzw, the simplified expression seems to work much better across the records. Thank you for pointing out about using the word Date as well. I am sure I have used it in one of the tables so I am going to run through them all right now and find out where I've used it and change it up. Thanks again for all of the amazing help and hard work.

[jzwp11]

You're welcome. Good luck with the project.

[10 Gauge]

I looked through my tables and could not find anywhere that I have used the word "Date". Is there anywhere else besides tables I could have used it that would have given you that error message?