Vba Sql Select Last??

[DBD2005]

I have a table with daily customer orders from which I want to find th
largest and smallest $ value over a range of dates in order t
determine the $ range (Max - Min) and update the customer table wit
that info.

I used an SQL SELECT (with MAX and MIN, grouped by customer, WHER
daterange), to do it and it works fine. Now I also need to update th
Customer table with the last order value in that date range. I trie
using LAST and tested it using Access Query but I believe that i
provided the last record entered for each customer. How can I mak
sure that it takes the latest date? Sorting by date doesn't seem t
work, probably because I group by customer!?

If this is not possible, what would be the simplest way to find th
order value of a specific customer on a specific date? I could the
run a loop for each customer and find it in the customer file.
believe FIND in RECORDSET cannot handle a complex criteria.

Any help is appreciated, I'm new to VBA

DB

 

[Dirk Goldgar]

I have a table with daily customer orders from which I want to find
the largest and smallest $ value over a range of dates in order to
determine the $ range (Max - Min) and update the customer table with
that info.

I used an SQL SELECT (with MAX and MIN, grouped by customer, WHERE
daterange), to do it and it works fine. Now I also need to update the
Customer table with the last order value in that date range. I tried
using LAST and tested it using Access Query but I believe that it
provided the last record entered for each customer. How can I make
sure that it takes the latest date? Sorting by date doesn't seem to
work, probably because I group by customer!?

If this is not possible, what would be the simplest way to find the
order value of a specific customer on a specific date? I could then
run a loop for each customer and find it in the customer file. I
believe FIND in RECORDSET cannot handle a complex criteria.

Any help is appreciated, I'm new to VBA

DBD

The Last() aggregate function function is pretty much useless in Jet
SQL. For this you need to get, for each customer, the order value for
the order that has that customer ID and the order date that is the
maximum date of the orders for that customer in that date range. There
can be several approaches to getting this; see this web page:

http://www.mvps.org/access/queries/qry0020.htm
Queries: Getting a related field from a GroupBy (total) query

I think the (untested) query below is something like what you need:

SELECT O.CustomerID, O.OrderValue
FROM
Orders As O
INNER JOIN
(
SELECT CustomerID, Max(OrderDate) As OrderMax
FROM Orders
WHERE O.OrderDate >= [RangeStart]
AND O.OrderDate <= [RangeEnd]
GROUP BY CustomerID
)
As T
ON
O.CustomerID = T.CustomerID AND
O.OrderDate = T.OrderMax

 

[DBD2005]

Thanks for your help. I haven't had a chance to look at the site or tr
your code, but I think I solved my problem before . I first did
SELECT INTO a tmp file WHERE daterange ORDER custid, date. Then I di
SELECT MAX MIN LAST FROM tmp GROUPED custid. Do you see any proble
using this approach

 

[Dirk Goldgar]

Thanks for your help. I haven't had a chance to look at the site or
try your code, but I think I solved my problem before . I first did a
SELECT INTO a tmp file WHERE daterange ORDER custid, date. Then I did
SELECT MAX MIN LAST FROM tmp GROUPED custid. Do you see any problem
using this approach?

I see two problems. First, it uses a temporary table, which is okay for
a one-time operation but not good for an ongoing process, as it tends to
lead to database bloat. Second, I wouldn't be completely sure that your
approach will always successfully get the last order value for each
CustID. It may do so in this case, but Jet can be arbitrary in the
order in which it presents records in the absence of an ORDER BY clause,
and so it's hard to get Last() to give you what you want. Your approach
may have succeeded, but I'm just not sure you can count on it always to
do so. Maybe you can.