Need a Query Guru with patience!

[TheNovice]

God Morning,

I have a query that need to do a couple of things that the message board
really doesnt cover or I dont understand.

This is what I am looking for. I have () fields that I need to determine a
sequence number and to calculate the previous distance.

I have:
DropKey
DropKeySeq
DelDate
Route
Customer
ArrDate
ArrTime
DepDate
DepTime
OdoMeterValue

there is also a Catch, some stops are what we consider Multi Stops, which
means that the truck never moves, that is the Drop key and the sequence is a
Input sequence number for all transactions.

I would like to know how to put a sequence number in the query and how to
get the mileage difference from the previous stop to this one. the
OdoMeterValue is that odometer reading at the stop.

Thanks in advance for any help,

Charles Davis

 

[KARL DEWEY]

Post some sample data.

 

[Michel Walsh]

Assuming OdometerValue has no duplicated value, then, in SQL view of a
query, type:


SELECT a.odometerValue,
a.odometerValue-MAX(b.odometerValue) As differenceOdometerValue,
1+COUNT(b.odometerValue) AS sequenceNumber
FROM yourTableNameHere AS a LEFT JOIN yourTableNameHere AS b
ON a.odometerValue > b.odometerValue
GROUP BY a.odometerValue



Save that query, say, under the name of q1.

If required, make a second query, bring your original table and query q1,
then, in the graphical editor, join the two tables over their common fields
odometerValue. Drag the other fields, as wanted, in the grid.


Use the query as if it was a table, when and where you need it.



Vanderghast, Access MVP

 

[TheNovice]

Karl,

I hope you can read this:

DropKey DropKeySeq DelDay Route Customer ArrDate ArrTime DepDate DepTime OdoMeterValue
13703602 6 5/9/2008 5002 442632 5/9/2008 4:47 5/9/2008 6:32 56240.4
13703602 6 5/9/2008 5002 774521 5/9/2008 4:47 5/9/2008 6:32 56240.4
13703604 8 5/9/2008 5002 443267 5/9/2008 6:40 5/9/2008 7:10 56241.4
13703607 10 5/9/2008 5002 450437 5/9/2008 7:15 5/9/2008 7:47 56242.3
13703609 12 5/9/2008 5002 508317 5/9/2008 7:52 5/9/2008 10:06 56243.5
13703611 14 5/9/2008 5002 602318 5/9/2008 10:11 5/9/2008 10:42 56244
13703613 16 5/9/2008 5002 794123 5/9/2008 10:48 5/9/2008 12:16 56246.3
13703613 16 5/9/2008 5002 101873 5/9/2008 10:48 5/9/2008 12:16 56246.3
13703613 16 5/9/2008 5002 358713 5/9/2008 10:48 5/9/2008 12:16 56246.3
13703619 22 5/9/2008 5002 788679 5/9/2008 13:52 5/9/2008 14:15 56249.1
13703619 22 5/9/2008 5002 650002 5/9/2008 13:52 5/9/2008 14:15 56249.1
13703619 22 5/9/2008 5002 497834 5/9/2008 13:52 5/9/2008 14:15 56249.1


Please let me know if you cannot.

Thanks in Advance,

Charles Davis

 

[Michel Walsh]

Make a first query: type in SQL view:


SELECT odometerValue
FROM yourTableNameHere
GROUP BY odometerValue

Save it as q1.


Make a second query:

SELECT a.odometerValue,
a.odometerValue-MAX(b.odometerValue) As differenceOdometerValue,
1+COUNT(b.odometerValue) AS sequenceNumber
FROM q1 AS a LEFT JOIN q1 AS b
ON a.odometerValue > b.odometerValue
GROUP BY a.odometerValue


Save it as q2.


Make a last query: In the graphical view: Bring your original table and
query q2. Join them on their common field odometerValue. Bring the fields
you need from your table in the grid. Bring sequenceNumber and
differenceOdometerValue fields, from q2, in the grid. That should be it.




Hoping it may help,
Vanderghast, Access MVP