Carl, you could use a pair of subqueries to count how many orders there are
in total for the customer, and which one this is.
You would type something like this into the Field row in query design:
OrderNum: (SELECT Count(OrderID) FROM tblOrder AS Dupe
WHERE ((Dupe.CustomerID = tblOrder.CustomerID)
AND (Dupe.OrderDate <= tblOrder.OrderDate)))
Then in the next field in query design:
OrderCount: (SELECT Count(OrderID) FROM tblOrder AS Dupe
WHERE (Dupe.CustomerID = tblOrder.CustomerID))
If the customer placed 2 orders on the one date (i.e. tied values), the
results will be duplicates.
If subqueries are new, see:
http://support.microsoft.com/?id=209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Carl" <cs****@nospamhere.net> wrote in message
news:cs**************@newsread3.news.atl.earthlink .net...
TblCustomer
CustomerID
CustomerName
TblOrder
OrderID
CustomerID
OrderDate
From the above tables, how do I write a query that returns the following
for
each customer:
CustomerA Order 1/4
CustomerA Order 2/4
CustomerA Order 3/4
CustomerA Order 4/4
Where CustomerA is the customer name, Order is text, CustomerA placed 4
orders and the orders are sequenced by OrderDate in chronological order.
Thanks!
Carl