Record calculation issue

[Guest]

I've search the boards and cannot find an answer to this one.

I have a table set up as follows:

Autonum Unit # H Date Hours
1 103 1/1/2007 2000
4 202 1/1/2007 1500
7 301 1/2/2007 1000
2 103 1/8/2007 2090
5 202 1/8/2007 1600
8 301 1/9/2007 1200
3 103 1/15/2007 2180
6 202 1/15/2007 1700
9 301 1/16/2007 14000

I need to be able to calculate the difference of the most current reading
for each unit # from the next to last reading. For instance, on unit # 301,
I need a query to give me a figure of 12800 (14000 - 1200). As new records
are added, this figure would need to change. I've come up with a few
solutions employing a linked Excel spreadsheet, however it doesn't take into
account the mixed sequence of the data, so when a new record is added, it
does not calculate properly. I've thought of giving each unit it's own
table, but I can't see how to join them together in a query. A junction
table, perhaps? Hope I explained this sufficiently. Any help would be
greatly appreciated.

Chuck

 

[Michel Walsh]

Hi,


In SQL if you need to compare two different rows, of the same table, you
generally involve two references to the same table. Here, I assumed the
field hours is cumulative, like an odometer, for a given unit. That allows
us to use a simplified query:

SELECT a.unitNumber, a.dateTimeStamp, MIN(a.hours-b.hours)
FROM tableName As a LEFT JOIN tableName As b
ON a.unitNumber = b.unitNumber
AND a.dateTimeStamp > b.dateTimeStamp
GROUP BY a.unitNumber



Clearly, references a and b (aliases to our table) refers to records but
in such a way that 'b' records occurred before records from reference 'a',
whatever record form reference 'a' is actually supplying data. So, as
example, for unit 103, with record autonumber =3, hours=2180, the valid
records under reference 'b' will supply values, for hours, of 2000 and of
2090. Each of these two values will be subtracted from 2180, and the one
supplying the minimum value will be kept (through MIN(a.hours-b.hours) ),
as intended.



Now, *IF* you can maintain the autonumbers sequence as it is right now, you
can still make an even simpler query:


SELECT a.unitNumber, a.DateStamp, a.hours-b.hours
FROM tableName As a LEFT JOIN tableName As b
ON a.unitNumber=b.unitNumber AND a.autonum - 1 = b.autonum



Indeed, as example, for record autonumber=3, the only record that can be
under reference 'b' is the one where autonum = 2. I kept the condition

a.unitNumber=b.unitNumber

in the on clause, because we don't want that record autonumber=4 thinks it
follows record autonumber = 3: it does not since they are from different
unit number.


Since the first query is not much harder than the last one, and since the
first query does NOT depends on having consecutive autonumber, you may be
wise to adopt the first one, and don't trouble you with getting the
'autonum' field in the illustrated 'consecutive' way it is, as supplied by
your data sample.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I'll give it a shot! Thanks Michel!

Hi,


In SQL if you need to compare two different rows, of the same table, you
generally involve two references to the same table. Here, I assumed the
field hours is cumulative, like an odometer, for a given unit. That allows
us to use a simplified query:

SELECT a.unitNumber, a.dateTimeStamp, MIN(a.hours-b.hours)
FROM tableName As a LEFT JOIN tableName As b
ON a.unitNumber = b.unitNumber
AND a.dateTimeStamp > b.dateTimeStamp
GROUP BY a.unitNumber



Clearly, references a and b (aliases to our table) refers to records but
in such a way that 'b' records occurred before records from reference 'a',
whatever record form reference 'a' is actually supplying data. So, as
example, for unit 103, with record autonumber =3, hours=2180, the valid
records under reference 'b' will supply values, for hours, of 2000 and of
2090. Each of these two values will be subtracted from 2180, and the one
supplying the minimum value will be kept (through MIN(a.hours-b.hours) ),
as intended.



Now, *IF* you can maintain the autonumbers sequence as it is right now, you
can still make an even simpler query:


SELECT a.unitNumber, a.DateStamp, a.hours-b.hours
FROM tableName As a LEFT JOIN tableName As b
ON a.unitNumber=b.unitNumber AND a.autonum - 1 = b.autonum



Indeed, as example, for record autonumber=3, the only record that can be
under reference 'b' is the one where autonum = 2. I kept the condition

a.unitNumber=b.unitNumber

in the on clause, because we don't want that record autonumber=4 thinks it
follows record autonumber = 3: it does not since they are from different
unit number.


Since the first query is not much harder than the last one, and since the
first query does NOT depends on having consecutive autonumber, you may be
wise to adopt the first one, and don't trouble you with getting the
'autonum' field in the illustrated 'consecutive' way it is, as supplied by
your data sample.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

Your second code worked, however, I now understand what you meant by
autonumber integrity. No, we won't be able to maintain that...it just
happened to work out that way and will probably never happen again.

The first set of code gives me an error of "You tried to execute a query
that does not include the specified expression 'Hdate' as part of an
aggregate function. Hdate being the dateTimeStamp.

Did I miss something? We're so close....I hope it's just an error on my part.

Chuck

 

[Guest]

We got it. Found a solution on Webthang. Just needed a comma and HDate on
the GROUP BY statement.