Calculate 28 days of mileage

[foshizzle]

Also the data isn't adding and the vehilce IDs are listed more than once. So its pretty much just listing everything in the table

 

[plog]

Yeah, that's perfect. Just to be clear you are calculating the prior 28 day total for every record. Pick a record and manually test it (add up the values for 28 days prior to the date of the record you chose).

Now for every record you know the 28 prior day total. What was the 28 day prior total to 3/1/2012? You have that. How about 4/14/2010? Got that too. You also have today's value no matter what date "today" becomes.

You can now use this query as the basis for another query. What exactly do you want to do with this information? Report, form?

 

[foshizzle]

I see I see.
Its going to go on a report. The user has to send off the total miles for each vehicle (and I assume total of all vehciles) used every 28 days.

 

[RainLover]

I see I see.
Its going to go on a report. The user has to send off the total miles for each vehicle (and I assume total of all vehciles) used every 28 days.

You are saying here that you simply want the Total miles for each vehicle for the last 28 Days.

Do you no longer want a running total.

What about a 28 day period. eg The first 28 days in July.

Your Quote at the top of this post says that you are unsure. Do you think you should ask someone exactly what is required.

 

[foshizzle]

Sorry I guess that came out wrong.. I know what I'm trying to say, lol...

I meant what you wrote.
The running total for 28 days for each vehicle. So, if the odometer now reads 1000, and the person only drives 50 one day and 50 the next day in car A, then I want to show 100miles. Not 1100. Then I want to total all cars running total together at the end..

I just want the user to be able to pick the start/end date to run the query against. I assume we would use the MileageDate field as the only other fields here are VehicleNum and Mileage

 

[RainLover]

I am going to make a couple of suggestions. If you don't like them then simply ignore. Here we go.

Because you are using the number "28" in your code for the query you are actually doing what is known as "Hard Coding." This type of coding cannot be changed except by the developer. You also have the query set to show all vehicles every time. Again this is hard coding.

I would suggest that you create an unbound Form that contains the criteria that the User will need.

First of all you could select from a Combo Box the Vehicle ID or if left blank it would show all Vehicles.

The next text box could have a Calendar control so that you could select the start date. Another text box could contain the end date. It could be set to automatically add 28 days or you could select any other period that you wish.

Doing this gives the user total control over the result of the query. The query is no longer hard coded.

The criteria in the query would equal the controls on the form. Namely StartDate, EndDate and VehicleID.

From this newly created Form you would have a Command Button that opens another Form or Report that uses this query as its Record Source.

If you go with my suggestions you will need to change the criteria in the query, however the basic principal would still apply.

 

[RainLover]

I didn't use your data, I created my own.

The attached Database might be of some help.

Hope this helps.

 

[foshizzle]

Awesome. Yea that sounds a lot better. And the query you made looks a lot easier. The only difference would be I would have multiple vehicles.

 

[RainLover]

Awesome. Yea that sounds a lot better. And the query you made looks a lot easier. The only difference would be I would have multiple vehicles.

What is the significance of multiple vehicles.

You can select just one vehicle from your Combo if you want just one. You can sort your query by Vehicle then by Date.

One word of warning. This type of query can be slow if it becomes too big.

 

[RainLover]

This link will help you to refer to a Form's Control to use as criteria. Please keep this link as you will find it useful in the future.

Refer to Form and Subform properties and controls

http://access.mvps.org/access/forms/frm0031.htm

 

[RainLover]

Awesome. Yea that sounds a lot better. And the query you made looks a lot easier. The only difference would be I would have multiple vehicles.

What is happening?

Is the problem solved?

It is courteous to get back to the people who helped or next time you may not get treated so well.

Up to you.

 

[foshizzle]

I apologize. I only work Wednesdays through Saturdays so today was my first day back at work. I just got pulled off doing other things. I promise I hadn't forgotten it's an ongoing problem. I'll try and give an update tomorrow

 

[foshizzle]

Thank you for your patience..

Well,
I just wrote a bunch of stuff but this thing logged me out and I lost it.. Short and sweet,

I get a Data type mismatch in criteria expression.(FYI, I renamed Mileage to Odometer like you did - it made more sense.) This is what my query looks like.

Column1
FIELD: VehicleAlias: VehicleNum
TABLE: TBL-Mileage

Column2
FIELD: Odometer
TABLE: TBL-Mileage

Column3
FIELD: Previous: Nz(DLast("Odometer","TBL-Mileage","[VehicleNum] < " & [VehicleAlias]),0)

Column4
FIELD: Difference: [Odometer]-[Previous]

Column5
FIELD: RunningTotal: Nz(DSum("Odometer","TBL-Mileage","[VehicleNum] <= " & [VehicleAlias]),0)

I did not enter anything in the criteria sections..

The output looks as follows:
Vehicle, CurrentMileage, Previous, Difference, RunningTotal
149, 11125, #Error, #Error, #Error
185, 2, #Error, #Error, #Error
185, 2, #Error, #Error, #Error
185, 9999, #Error, #Error, #Error

I think it may have something to do with my PK. I have Access using an autonumber and ID as the PK. Otherwise it wouldnt let me enter multiple dates using the same vehicle into the table. So I had another table called TBL-Vehicle and I am using the VehicleNum as a lookup field to that. Also this way the user cannot miskey the vehcile number. Is this logical?

 

[Cronk]

Re column 3, "odometer" is not in the table and it's the previous date not the vehicle.

Try

Column3
FIELD: Previous: (select top 1 Mileage from table1 as tmp where vehiclenum = table1.vehiclenum and readdate < table1.readdate order by readdate desc)

 

[RainLover]

Your table design could be improved.

You don’t really have a need for the word Column. Use Field instead.

Column is used in code.

You should have a Table that stores Vehicles

Another Table that stores the mileage that each Vehicles has done.

Neither of these Tables should be storing calculated values.

VehicleAlias This is a calculated field in your query and does not belong in any table.

It would be good if you put these three items, 2 Tables and a query into a blank database and then post that database here online.

Please use Access 2003 as many of us do not have later versions.

Some more reading is attached.

 

[RainLover]

Document did not attach on previous attempt.

 

[foshizzle]

Ok.
I exported these to a new blank database.
Please find the attached. https://www.dropbox.com/s/0qgdhnarbuxsqvx/Mileage.accdb

 

[foshizzle]

Actually, hold that thought. From what I am trying to do with my form, I think I may need to have a separate table for each vehicle to track their mileage. I wouldn't want a user to have to choose from a drop down 3 times in one form when there are only 3 vehicle numbers and all static. This may make it easier to obtain the last 28 days or so of mileage based on user input..

 

[David R]

I think I may need to have a separate table for each vehicle to track their mileage

No, no, NO! Normalization says you store the mileage and VehicleID in one table, the LIST of vehicles in another. When you need to limit it down for a particular user, you can use a query or a WHERE clause to limit what they see/report on.

 

[foshizzle]

lol. Ok. Good bc I cant get it to work this way either..