How can I Make These Calculations?

[jcardullo]

I have a table that contains component replacement on heavy equipment. I need to run either a query or report or combination of both to calculate hours on the component. Here's the trick. The hours will be calculated based on the hour meter entry of the new component with the newest component hour life calculated from a daily hour entry. Example:
Date/installed Hrs/installed Hrs/now Comp/Hrs
7-2-02 - Engine 2000 2500 = 500
7-2-01 - Engine 1000 = 1000
7-2-00 - Engine 500 = 500

See how the newest component relies on the current hour meter while the past components are calculated by hour meter of the next Hrs/Installed.

Is this a possible function in Access?
Sorry about the long post, but this is a difficult one to explain.
Thanks for any help.

 

[Ken Grubb]

Could you post the columns of data in your table? I'm having trouble visualizing this one.

 

[jcardullo]

Here are my columns


[Equip#] [Comp.] [HourInstall] [CompHours] [DateInstall]
2580 HCYL 53805 6-20-02
2580 HCYL 50956 12-10-01
2580 HCYL 50081 9-13-01
2580 HCYL 45702 7-25-00


I couldn't get my columns to line up. The column under [CompHours] is blank right now. It is what I want to calculate. To get life on components I need to subtract the [HourInstall] from the one that is installed after it based upon the DateInstall. To calculate the CompHours of the newest one, I need to subtract from the current HourMeter field of the Equip# in a different table.
Man, this is getting confusing.

Is this even possible? Or am I going about this all wrong?

I would like to be able to do this in a query.
Thanks for any help.

 

[Ken Grubb]

I don't see a way to do this in a query -- at least not one query. However, you can write a Code Module to do this. I can see the solution with some clarity, so I know it's going to be difficult to explain. Here goes.

You'll need to build two recordsets of your data, walk those recordsets, and update your table.

Assuming Equip# and HourInstall are unique in your Table, if not the Primary Key, build the first recordset of DISTINCT Equip# values.
(SELECT DISTINCT [Equip#] FROM [tblEquipment])

Walk the first recordset, and for each Equip#, build a second recordset.
(SELECT [HourInstall] FROM [tblEquipment] WHERE [Equip#] = <value passed from the first recordset> ORDER BY [HourInstall] ASC)

Walk the second recordset using this code to get your CompHours.

dim iLastHour as integer
dim iCompHours as integer
With rs
.MoveFirst
iLastHour = !HourInstall
.MoveNext
Do Until .EOF
iCompHours = !HourInstall - iLastHour
iLastHour = !HourInstall
.MoveNext
Loop
End With

I sincerely do HTH.

 

[jcardullo]

Please excuse my ignorance, but what do you mean by "walk"?

By the way, thank you so much. This was driving me crazy.

 

[Ken Grubb]

The block of code I included is one such example of what I mean by walking a recordset. [I'm not really sure where and when the term "walk" imprinted on my brain in this context. Probably too many years writing COBOL on a Unisys Mainframe.] If you've not worked with recordsets before this will build and walk the first recordset.

Dim db As Database
Dim rs2 As Recordset

Set db = CurrentDb
Set rs2 = db.OpenRecordset("SELECT DISTINCT [Equip#] FROM [tblEquipment]")

With rs2
.MoveFirst
Do Until .EOF
Msgbox !Equip#
.MoveNext
Loop
End With

Set rs2 = Nothing
Set db = Nothing

 

[jcardullo]

Once again, thanks

I'll give this a try.

Happy 4th for all the yanks out there

 

[Ken Grubb]

I had heard the Brits didn't have a Fourth of July. But it's right there between the 3rd and the 5th.