Problem with km

[Bosnjanac]

Hi all,

I am first time on forum and I say hello to all. I am glad to be member of this forum.

I make one table for Fuel statistics for my friend. After I import data from Excel I get one problem.




Here is problem. I have query which have to fields with data for km. One field should be Max millage for past mounth, and other field should be Max millage for this month. How can I set this in query? Millage is in same table like other relevant data for fuel statistics.

Thanks

[June7]

Do you mean mileage?

Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[ranman256]

You need the criteria to limit the date range to 1 month.

[Bosnjanac]

Yes, i mean milage. When I enter citeria in Query Design I get this error:

[June7]

That criteria is text. Is km field a date/time type?

Review http://allenbrowne.com/ser-36.html

[Bosnjanac]

I confuse you and myself. Is there any way to use Where Clause in Max(table.field) AS Max. I need to find Max value for millage for certain month.

[June7]

Maybe:

SELECT Year([date field]) & Month([date field]) AS YrMo, Max([field name]) AS MaxSomething FROM tablename GROUP BY Year([date field]) & Month([date field]);

[Bosnjanac]

Thanks, just to ask can I into Max([field name]) AS MaxSomething add where clause?

[June7]

Criteria for what field? Under the constructed YrMo field can have: "201501"

[Bosnjanac]

I stuck here. I must to find Max milage for venchile for this mounth and take away max milage for venchile for past mounth.


In field Prosli:km I need to show max millage to find Max milage for February 2015 (Feb-15). Field km from table evidencija show max milage for mounth "Mar-15".

[June7]

That usually requires a nested subquery. Review link referenced back in post 2.

[Bosnjanac]

Hmm, I am stuck here. Is there any way to share my db with you and help me with it?

[June7]

Can attach file to post. Follow instructions at bottom of my post.

[Bosnjanac]

I attach you database. In db is only query there is two fields with km value from fuel. First it should be Max km from past mount, and second should be Max km from last mount. There is no date and time for mounts, only values Jan-15, Feb-15, Mar-15.

Thanks for help.

[June7]

The [mount] data is not a date/time type. This really complicates sorting process. Build a query that returns a YrMo value. I modified Main to:

SELECT fuel.*, cars.registration, Left([mount],3) AS Mo, Right([mount],2) & "-" & Switch([Mo]="Jan","01",[Mo]="Feb","02",[Mo]="Mar","03",[Mo]="Apr","04",[Mo]="May","05",[Mo]="Jun","06",[Mo]="Jul","07",[Mo]="Aug","08",[Mo]="Sep","09",[Mo]="Oct","10",[Mo]="Nov","11",[Mo]="Dec","12") AS YrMo
FROM cars LEFT JOIN fuel ON cars.[card] = fuel.[card];

Now use that query like a table in other queries.

Each card has multiple records for each mount and this is causing complication. Really needed the day part of dates, not just month and year.

Maybe this is what you want:

SELECT Main.ID, Main.Registration, Main.card, Main.YrMo, Main.km, (SELECT TOP 1 Dupe.km
FROM Main AS Dupe
WHERE Dupe.Card = Main.Card AND Dupe.ID<Main.ID
ORDER BY Dupe.Card, Dupe.ID DESC) AS PriorValue
FROM Main
ORDER BY Card, Main.ID;

Unfortunately it relies on linking and sorting by the ID field. This means the ID must always be increasing when records are entered and also that records are entered in an order that the km is always increasing.


Why is [station] field in both tables? What is [sum] field for?