'First Ordered' Query

[janeyd]

I have a 'samples sent' table with fields, Customer No., Name, Country, Samples ID, Date Samples Sent.

I want to run a query showing customers from USA whose first order was after 1st April this year. Obviously the USA part is easy but I don't know how to filter to find first order.

Thank you

 

[GohDiamond]

Something like this:

I used the northwind database ORDERS Table to run this test...

SELECT First(Orders.OrderDate) AS FirstOfOrderDate, Orders.CustomerID, Orders.ShipCountry
FROM Orders
GROUP BY Orders.CustomerID, Orders.ShipCountry
HAVING (((First(Orders.OrderDate))>=#12/1/1996#) AND ((Orders.ShipCountry)="USA"))
ORDER BY First(Orders.OrderDate);​

SORT ascending on the OrderDate
GROUP by FIRST on the OrderDate
Enter OrderDate Criteria: >=#YOURDATE#
Enter Country Criteria: "USA"

This example gave me the FIRST order of the corresponding customer ID then displayed those customer IDs with their first order occuring after the criteria date if they were in the USA

I hope this example helps...

Cheers!
Goh

 

[John Big Booty]

Use criteria;

[YourDateField] > #4/1/2012#

 

[janeyd]

Thank you GohDiamond I will give that a go

Re: John Big Booty - can't use this as it will show orders placed after 1st april but doesn't eliminate anyone who also ordered previous to this