Calculate first date per customer in a query and mark as Yes/No?

Garindan

Registered User.
Local time
Today, 08:34
Joined
May 25, 2004
Messages
250
Hi all, I am trying to create a calculated field in a query and can't figure it out.

The query is based on tblCustomerOrders and the only three fields I need are OrderNumber (PK Autonum), CustomerNumber and Date_TimeOfOrder.

I'm trying to add a fourth field called 'FirstOrder' to calculate if an OrderNumber is the first order placed by a customer and mark it as Yes/No.

When the field is created/working I want to use it in a form and a report recordsource using Dlookup. In the report there is a check box called 'First Order' and I want it to be ticked if it is a customers first order. In the form I want to use it in the underlying query to group on (for showing products which are part of a first order and which are not).

Here's what I have so far:-
Code:
FirstOrder: IIf([Date_TimeOfOrder]=[First],"Yes","No")
but I know it's not right lol. It just asks me to input 'First' and then marks all Orders as No (in the FirstOrder field).

Here's the sql:-
Code:
SELECT tblCustomerOrders.OrderNumber, tblCustomerOrders.CustomerNumber, tblCustomerOrders.Date_TimeOfOrder, IIf([Date_TimeOfOrder]=[First],"Yes","No") AS FirstOrder
FROM tblCustomerOrders
ORDER BY tblCustomerOrders.CustomerNumber;

I know I can change the query to a totals query, remove OrderNumber from the query and group on CustomerNumber, then put 'First' in the total for Date_TimeOfOrder and this gives me the first date for a customer, but then the 'OrderNumber' field is no longer in the query and how can I link it to an OrderNumber in the report and form as required?

Any help would be very much appreciated! Many thanks!
 
Having battled with First and Last very recently, I'd advocate using Min and Max instead!
 
So are you saying if there is only 1 record returned from the query then it is the customers first order?

You could probably do a sub select statment within this statment that counts the number of ordernumbers for the customer and if it = 1 then its the customers first order.

I am not an expert at this so there is probably a more elegant way to do it.
 
Boots is correct you must use Min not First on the Date Field, having found in query1 the first date per cutomer this information is joined, along with customernumber back to the table to pick up the rest of the info.

Another option might be to use DFirst as explained by Bob in Boots's thread.

Brian
 
It's ok I've got it working with someones help :D Thanks very much to all replies on here! It used DMin in the end :)

Code:
SELECT OrderNumber, CustomerNumber, Date_TimeOfOrder, 
IIf([Date_TimeOfOrder]=DMin("Date_TimeOfOrder", "tblCustomerOrders", "CustomerNumber=" & [CustomerNumber]), "Yes", "No") As FirstOrder
FROM tblCustomerOrders
ORDER BY CustomerNumber
 

Users who are viewing this thread

Back
Top Bottom