Taking Last Value from range before query starts.

[Mike Abbott]

Good Morning from the UK.

I am a newcomer to access sorry if this is a simplistic question.

I guess this is part a Query question part a report question, apologies if
it's in the wrong group.

The Inland Revenue like me to keep track of the business use of my van.

I have a table into which I record only my business trips.

It has the following fields

Date, Start Miles, End Miles

I don't use my van every day .

I might have entries for

25th Jan

3rd Feb
4th Feb

etc to
25th Feb

I can run a parameter query to select all records between say 1st Feb and
28th Feb.

However to perform the calculation I need I have to
take the endmiles value from the 25th Jan record.


Lets call it LE

How do I get this to happen so I can then use the value in a calculation in
a report?

I am working in UK date format dd/mm/yyyy.

Thanks to all who post on this group it's really useful.

Mike

 

[Tom Ellison]

Dear Mike:

This would use a correlated subquery to find the "largest" value of date
within the specified range, and then filter to the row with that date.

A weakness I see in this is a follows. What happens if you use the vehicle
on that last date, say, 25 Feb, for a business trip, then for a personal
trip, then for another business trip. Would you not then nave two rows of
data for that same date? I expect you would want the latter of these two
trips.

One might expect that the trip with the larger StartMiles would be the one.
But, being a very picky database designer, I see a problem. What if you
have two different vehicles you may be using? If the mileage on the one
used earlier in the day is higher than the mileage on the one used later in
the day, there is now no way to tell which is actually the "last" one.

Likely this is not the case for what you say, but it is not something you
should probably ignore when you spend your time creating a database.
Recognizing such possibilities is an important part of what I do, anyway.

Now, likely the whole concept of a "correlated subquery" is foreign to you.
Well, the particulars of your database are actually quite foreign to me.
I'll give it a shot:

SELECT [Date], MAX([Start Miles]), MAX([End Miles])
FROM YourTableNameGoesHere T
WHERE Date =
(SELECT MAX([Date])
FROM YourTableNameGoesHere T1
WHERE T1.[Date]
BETWEEN [Enter Start Date:] AND [Enter End Date:])
GROUP BY [Date]

You must put the actual name of your table in this.

The stuff on lines 4-7 is the "correlated subquery".

Tom Ellison