Formula Field or Query Field

[Guest]

I work for a company that owns a bunch of equipment. Things like compactors,
dozers, excavators, graders, loaders, scrapers, etc. Each of these pieces of
equipment has an hour meter installed it. An hour meter is similar to an
odometer, except instead of tracking miles, an hour meter tracks the number
of hours a piece of equipment has been turned on.

At the end of each month, I get an hour meter register from the field. It
lists the unit number of each piece of equipment on it and the reading of the
hour meter. These are the readings for January 31.

A01 - 3572
A03 - 238
A04 - 50
C06 - 13287
C07 - 8946

I need to create a formula field or a query field that will tell me how many
hours the hour meter changed from the end of the prior month to the end of
this month.

This month when the hour meter register is turned in, the new data will look
like this for February 28:

A01 - 3593
A03 - 245
A04 - 50
C06 - 13350
C07 - 8986

The formula field or query field needs to tell me the difference in the two
readings for each piece of equipment.

A01 - 21
A03 - 12
A04 - 0
C06 - 63
C07 - 40

I assume that the sort order of my database will be unit number, date, hour
meter reading. Using the two months of data above the table would like this:

A01 - 01/31/06 - 3572
A01 - 02/28/06 - 3593
A03 - 01/31/06 - 238
A03 - 02/28/06 - 245
A04 - 01/31/06 - 50
A04 - 02/28/06 - 50
C06 - 01/31/06 - 13287
C06 - 02/28/06 - 13350
C07 - 01/31/06 - 8946
C07 - 02/28/06 - 8986

At the end of the March, the table will expand to look like this:

A01 - 01/31/06 - 3572
A01 - 02/28/06 - 3593
A01 - 03/31/06 - 4060
A03 - 01/31/06 - 238
A03 - 02/28/06 - 245
A03 - 03/31/06 - 267
A04 - 01/31/06 - 50
A04 - 02/28/06 - 50
A04 - 03/31/06 - 55
C06 - 01/31/06 - 13287
C06 - 02/28/06 - 13350
C06 - 03/31-06 - 13402
C07 - 01/31/06 - 8946
C07 - 02/28/06 - 8986
C07 - 03/31/06 - 9070

Keep in mind that the field which gives me the diffrence between the prior
record and this record needs to establish that the unit number is the same.
(I don't want the table telling me that the difference between C06 - 03/31-06
- 13402 and C07 - 01/31/06 - 8946 is negative 4456. They are different unit
numbers and the change in the hour meter reading from the prior record should
not be calculated.)

The only other trick to this situation is that sometimes hour meters stop
working and need to be replaced. In the example above, let us say that the
hour meter for C06 died and was replaced. On the first of the month the hour
meter reading was 13287, on the 15th of the month it stopped working and it
read 13301. We replaced the hour meter with a new one and the 16th and on
the end of the month the new hour meter read 43 hours. The change in the
month is 57 hours. (13301 - 13287 + 43).

It is acceptable for you to respond with an answer that says it cannot be
done through a formula field in the table itself, nor with a query formula
field either, but if that is the case, then I need the solution to obtained
in Seagate Crystal and if you have used that program, could you post a
solution by using that program?

I don't want much, huh? I thank you in advance for any solution you give me.

Christina

 

[Guest]

You can do this in Access, but it isn't pretty. You can do it very easily in
Excel. Is there any particular reason for trying to do it in Access?

 

[Guest]

this is information that is going to end up being placed onto a
Crystal-formatted Report.

I have been doing this on a Lotus 1-2-3 spreadsheet since 1988, but there
are too many pieces of equipment and too much history and the reports ask for
too many bits of information to allow me to have confidence the sheets are
100% correct anymore.

You are right, it was a snap in a spreadsheet, but there comes a time when
spreadsheet information needs to be transferred to a database, because there
is just too darn much of it and this happens to be one of those times.

BTW, I did get an answer over in the Crystal forum for calculating a prior
record difference and so I may have it solved through Crystal, but I surely
would like to know how to do it in Access as well.

 

[Guest]

Tracking the information would be much better in Access. You might consider
doing the calculations every month in Excel/Lotus, and then importing the
massaged data into Access. Appending information onto a table is quite
straightforward. Doing the calculations in Access was very ugly (I actually
set up a sample database to try it), and is more work than it is worth.

 

[Guest]

Actually I was thinking along the same lines as you were. I was thinking
that I should simply create a table that shows the change in the hour meter
during the period and build my report on that table.

So, thank you very much for that suggestion. I am getting close to deciding
that is how I am going to go.

Tofer