First time purchasers (PurchaserID is any field which idedntifies the purchaser - probably a primary key in purchasers table, and a foreign key in purchases table):
Code:
SELECT tbl1.PurchaseDate, tbl1.PurchaserName, tbl1.PurchaseOrder
FROM YourPurhases AS tbl1
WHERE
Year(tbl1.PurchaseDate)=Year(Date()) AND
Month(tbl1.PurchaseDate) = 6 AND
tbl1.PurchaserID NOT IN (SELECT tbl2.PurchaserID FROM YourPurchases AS tbl2 WHERE tbl2.PurchaseDate < DateSerial(Year(Date()),6,1))
Recurrent purchases:
Code:
SELECT tbl1.PurchaseDate, tbl1.PurchaserName, tbl1.PurchaseOrder
FROM YourPurhases AS tbl1
WHERE
Year(tbl1.PurchaseDate)=Year(Date()) AND
Month(tbl1.PurchaseDate) = 6 AND
tbl1.PurchaserID IN (SELECT tbl2.PurchaserID FROM YourPurchases AS tbl2 WHERE tbl2.PurchaseDate < DateSerial(Year(Date()),6,1))
It is possible to write a query, which uses LEFT JOIN instead of IN (SELECT...), but only way I can see how to do it is to use a parametrized saved query (unless you want this query ONLY for june 2018!). An ecample with fixed month:
Saved query
Code:
qryPurchasesBefore201806 =
SELECT DISTINCT PurchaserID
FROM YourPurchases
WHERE PurchaseDate < DateSerial(Year(Date()),6,1)
Final query (here is assumed, that PurchaserID is numeric, when string, then you ave to replace (Nz(qry1.PurchaserID,0)>0) with (Nz(qry1.PurchaserID,"")>"")
Code:
SELECT tbl1.PurchaseDate, tbl1.PurchaserName, tbl1.PurchaseOrder, (Nz(qry1.PurchaserID,0)>0) AS RecurentPurchase
FROM YourPurhases AS tbl1 LEFT JOIN qryPurchasesBefore201806 AS qry1 ON qry1.PurchaserID = tbl1.PurchaserID
WHERE
Year(tbl1.PurchaseDate)=Year(Date()) AND
Month(tbl1.PurchaseDate) = 6