Query help (SQL)

[vbnetman via AccessMonster.com]

I need to create a listbox that displays the history of meters (odometer,
hourmeter etc) for equipment. This will need to be based on a query, a topic
that is my greatest weakness and I am requesting assistance. I have the
following table / field arrangement; (for simplicity some fields have been
omitted).

tbl_runningMeter
<<unit of measure>>

tbl_meterReading
<<unitID>>
<<meterID>>
<<replacementNumber>>
<<readDate>>
<<meterRead>>

Here’s a bit of info about the arrangement:
UnitOfMeasure - defines the type of meter (hour meter or odometer)

The meter reading table looks something like this:
unitID meterID meterRead replacementNumber readDate replace
1 1 10 0
10/1/06
1 1 15 0
10/3/06
1 1 20 0
10/4/06 x
1 1 7 1
10/5/06
1 1 35 1
10/6/06

1) Note that unit#1, meter#1 replacement#0 is a replaced meter and unit#1,
meter#1, replacement#0 is the current meter, not to be included in the
listbox.
2)The db can have more than one unit and more than one meter per unit.
3) Although the unit itself has a total reading of 38, the per meter
breakdown is as follows and here’s what I need for the end result;

unitOfMeasure unitID meterID startRead endRead totalRun replaceDate
miles 1 1 10 20
10 10/4/06


The date 10/1/06 is the start date of meter1. (assume in this case that the
meter was purchased from the factory with “10†on it. The date in this
example of 10/4/06 represents the date of the last and final reading of the
meter before it’s replaced, thus a total read for the original meter is 10.
10/5/06 is the date of the first reading for its replacement.

Could someone give me an assist with the SQL?

Thank you in advance

 

[mscertified]

I'm confused by this statement:

"Note that unit#1, meter#1 replacement#0 is a replaced meter and unit#1,
meter#1, replacement#0 is the current meter"

seems like they are the same thing

Dorian

 

[mscertified]

"Although the unit itself has a total reading of 38,"

Where does the 38 come from?

So you only want to process meters that have been replaced? what about
meters that have not been replaced? what about meters that have been replaced
more than once i.e if meter A is replaced by meter B and meter B is replaced
by meter C, do you want to include both meter A and meter B?

Dorian

 

[vbnetman via AccessMonster.com]

Hi Dorian,
Thank you for the response. Yes, let me clarify as it is a bit confusing.

The db handles multiple meters on multiple units. Thus, there could be unit 2
/ meter 2 or unit 3 meter 4. For the sake of clarity I will stick with one
unit.

The first entry dated 10/1/06 is the start date for meter one on unit one.
The reading at the time of install is 10 (let's say that's the way it came
from the factory).
The second entry dated 10/3/06 is simply a reading taken for unit one / meter
one during the course of, say a maintenace service.
The third entry dated 10/4/06 is the last and final reading for unit one /
meter one (let's say the meter failed and got replaced on this date - thus
the "X" flagging it as replaced. Therefore, the total reading for this meter
is 10 (started at 10 and ended on 20)

OK, here comes the fun part...

So.....the meter gets replaced. Now, because the meter was an odometer for
example, I need to replace it with an odometer....still meter one just
flagged as "replacement one" The start reading for the replacement meter is 7
with a start date of 10/5/06. Since the meter has not been replaced yet (it's
the current meter with the current read) I cannot flag it as replaced. So the
38 comes from the 10 that was run on the original meter and the 28 run on the
current meter.

I need to look at the rest of your questions.

Make sense so far?

"Although the unit itself has a total reading of 38,"

Where does the 38 come from?

So you only want to process meters that have been replaced? what about
meters that have not been replaced? what about meters that have been replaced
more than once i.e if meter A is replaced by meter B and meter B is replaced
by meter C, do you want to include both meter A and meter B?

Dorian

I need to create a listbox that displays the history of meters (odometer,
hourmeter etc) for equipment. This will need to be based on a query, a topic

[quoted text clipped - 49 lines]

Thank you in advance

 

[vbnetman via AccessMonster.com]

Hi Dorian,
They really are not the same thing...the first one is the original meter that
has been replaced (note the 'replace' flag). The second one is the current
meter as it has not been replaced.

I'm confused by this statement:

"Note that unit#1, meter#1 replacement#0 is a replaced meter and unit#1,
meter#1, replacement#0 is the current meter"

seems like they are the same thing

Dorian

I need to create a listbox that displays the history of meters (odometer,
hourmeter etc) for equipment. This will need to be based on a query, a topic

[quoted text clipped - 49 lines]

Thank you in advance

 

[vbnetman via AccessMonster.com]

Yes, I am interested in processing only those meters that have been replaced.
Meter one, for example, will always get replaced by meter one. The only
difference is that a replaced meter one will be flagged with not only a
'replace' (yes/no field) but a replacement number as well.

"Although the unit itself has a total reading of 38,"

Where does the 38 come from?

So you only want to process meters that have been replaced? what about
meters that have not been replaced? what about meters that have been replaced
more than once i.e if meter A is replaced by meter B and meter B is replaced
by meter C, do you want to include both meter A and meter B?

Dorian

I need to create a listbox that displays the history of meters (odometer,
hourmeter etc) for equipment. This will need to be based on a query, a topic

[quoted text clipped - 49 lines]

Thank you in advance

 

[vbnetman via AccessMonster.com]

"Although the unit itself has a total reading of 38,"

Where does the 38 come from?

So the unit has a total of 38, it's just that 2 meters are accounting for the
total......one totals 10 and the other totals 28.

"Although the unit itself has a total reading of 38,"

Where does the 38 come from?

So you only want to process meters that have been replaced? what about
meters that have not been replaced? what about meters that have been replaced
more than once i.e if meter A is replaced by meter B and meter B is replaced
by meter C, do you want to include both meter A and meter B?

Dorian

I need to create a listbox that displays the history of meters (odometer,
hourmeter etc) for equipment. This will need to be based on a query, a topic

[quoted text clipped - 49 lines]

Thank you in advance

 

[vbnetman via AccessMonster.com]

Yes, you are correct here...I made a typo...it should read
"Note that unit#1, meter#1 replacement#0 is a replaced meter and unit#1,

meter#1, replacement#1 is the current meter". Sorry about the confusion.

I'm confused by this statement:

"Note that unit#1, meter#1 replacement#0 is a replaced meter and unit#1,
meter#1, replacement#0 is the current meter"

seems like they are the same thing

Dorian

I need to create a listbox that displays the history of meters (odometer,
hourmeter etc) for equipment. This will need to be based on a query, a topic

[quoted text clipped - 49 lines]

Thank you in advance

 

[mscertified]

I split this task into 2 queries.
The first query results feed the second query.
The first just picks out the replaced meters:

SELECT DISTINCT M1.*
FROM tblMeters AS M1, tblMeters AS M2
WHERE M1.unitid = M2.unitid and M1.meterid = M2.meterid and M2.replnum >
M1.replnum;

The second does the calculations and puts out the final table:

SELECT unitid, meterid, min(meterread) AS Start, max(meterread) AS [End],
max(meterread) - min(meterread) AS Run, max(readdate)
FROM qryGetReplaced
GROUP BY unitid, meterid;

-Dorian

"Although the unit itself has a total reading of 38,"

Where does the 38 come from?

So the unit has a total of 38, it's just that 2 meters are accounting for the
total......one totals 10 and the other totals 28.

"Although the unit itself has a total reading of 38,"

Where does the 38 come from?

So you only want to process meters that have been replaced? what about
meters that have not been replaced? what about meters that have been replaced
more than once i.e if meter A is replaced by meter B and meter B is replaced
by meter C, do you want to include both meter A and meter B?

Dorian

I need to create a listbox that displays the history of meters (odometer,
hourmeter etc) for equipment. This will need to be based on a query, a topic

[quoted text clipped - 49 lines]

Thank you in advance

 

[vbnetman via AccessMonster.com]

Dorian,
I've run a few samples and everything appears seamless. I really appreciate
the time you spent on this and I know you did....this was simply beyond my
brain capacity.

Thank you so much!
Sincerely
vbnetman

I split this task into 2 queries.
The first query results feed the second query.
The first just picks out the replaced meters:

SELECT DISTINCT M1.*
FROM tblMeters AS M1, tblMeters AS M2
WHERE M1.unitid = M2.unitid and M1.meterid = M2.meterid and M2.replnum >
M1.replnum;

The second does the calculations and puts out the final table:

SELECT unitid, meterid, min(meterread) AS Start, max(meterread) AS [End],
max(meterread) - min(meterread) AS Run, max(readdate)
FROM qryGetReplaced
GROUP BY unitid, meterid;

-Dorian

"Although the unit itself has a total reading of 38,"

[quoted text clipped - 18 lines]

Thank you in advance