Solved: Select Query

[JustJerry]

Geekgirl showed me an excellent way to perform a similar scenerio, but trying to figure out a different route.

I have a table named Mile set up as follows:

MileID Date Odometer State
1 1/1/2006 20000 MT
2 1/31/2006 25000 OR

Ok, so I have a query based on an Microsoft KB article that looks at this table and selects the previous mileage and calucates the difference. The query is based on the table Mile, with an Alias name of Mile1.

SELECT Mile1.MileID, Mile1.Date, Mile1.Odometer, Mile1.State, (Select Max(Odometer) from tblMileTotal
Where Odometer < Mile1.[Odometer]) AS PrevMileage, [Odometer]-[PrevMileage] AS Elapsed
FROM tblMileTotal AS Mile1;


So I get this from running the query:

MileID Date Odometer State PrevMileage Elapsed
1 1/1/2006 20000 MT
2 1/31/2006 25000 OR 20000 5000

Ok, here again, I can't figure out how to add to this query that looks at the 'previous state' value. So for Mile ID 2 for example, there would be a field to be called PrevState that would show MT, and so on for all the records down the line.

Is it 'SIMPLE' to add to my Select Query to bring forth the State like I can the Previous Odometer reading??

Thank you YET again

Jerry

[XLGibbs]

SELECT Mile1.MileID, Mile1.Date, Mile1.Odometer, Mile1.State, (Select Max(Odometer) from tblMileTotal
Where Odometer < Mile1.[Odometer]) As PrevMileage, [Odometer]-[PrevMileage] As Elapsed,mile1.[State] as PrevState
FROM tblMileTotal As Mile1;


You want the State fromt he same table as previous mileage right? the above adds it in..based on syntax only!

Untested..

[JustJerry]

Hey Gibbs,

I get a syntax error concerning the FROM clause in the part you added. I tried something similar to this before,and couldn't get it to work. Thank you for the suggestion though. I'll keep plugging away at it.

Yes, I want the State from the same table. Where I choose the Max(Odometer), I also want the state associated with that mileage.

If that makes sense

[XLGibbs]

Well you essentially have a sub query inside the main query...the subquery needs to be witin parenthesis...to break it up a bit

SELECT 'each column is selected and separated by commas. A Select query can be used to get column data

Mile1.MileID,
Mile1.Date,
Mile1.Odometer,
Mile1.State,

(Select Max(Odometer) from tblMileTotal Where Odometer < Mile1.[Odometer]) As PrevMileage,

[Odometer]-[PrevMileage] As Elapsed,

(Select [State] From tblMileTotal where Odometer < mile1.[Odometer]) as PrevState

FROM tblMileTotal As Mile1; 'this creates and alias to "self join: the table for the subqueries


For a final query like this.

SELECT Mile1.MileID,Mile1.Date, Mile1.Odometer,
Mile1.State, (Select Max(Odometer) from tblMileTotal Where Odometer < Mile1.[Odometer]) As PrevMileage, [Odometer]-[PrevMileage] As Elapsed,(Select [State] From tblMileTotal where Odometer < mile1.[Odometer]) as PrevState
FROM tblMileTotal As Mile1;


My syntax was off before as I misread it slightly...hope this one helps a little more :)

[JustJerry]

Ok, I see what I was typing in wrong regarding:

FROM tblMileTotal As Mile1

I had it in my query twice. But now it tells me this type of query can not return more than one record. I may have to find a different way to enter in the data to bypass this error.

Thank you again for your help

[XLGibbs]

If you want more than one record, you would have to have a Group By or a Having clause to identify the row level grouping... but with the subqueries hard-typing that kind of syntax gets tricky.

There are easier ways to join the tables and get a full list, but they are hard to explain in writing, much much simpler to "see"...ifyou want to send me the zip file, i can set one up for you...

[JustJerry]

Here is a stripped down and zipped file that I'm working with. It's still a work in progress, but hope it helps.

I've changed some things like adding a new field called StatePv in the tblMileDetail in trying to go around the problem I've been having, but if I can get it to work without doing this, probably be much better

Jerry