Results 1 to 5 of 5
  1. #1
    StevelKnievel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2

    Need query to identify new customers each month


    Greetings everyone and thank you in advance. I have simple table of credit card purchases, with field for date of purchase, name, order etc. In English I need to find, "For all purchases in June, how many were first time purchasers and how many were existing customers." So I need to query for customers with "date between 6/1 and 6/30" AND also "date < 6/1" to list all existing customers who made purchases in June and before. Versus customers who only purchased in June (thus new)... But I'm stumped. I hope this is possible? Please help. Thank you so much.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    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

  3. #3
    StevelKnievel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2
    Thank you so much! However I am a novice, and is there a way to do this in Design view? Thanks again...

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by StevelKnievel View Post
    Thank you so much! However I am a novice, and is there a way to do this in Design view?
    I'm almost sure it is a way for version with saved query and join, but maybe someone other gives more advice, as:
    1. I almost never use Design View myself, I prefer SQL View;
    2. For some 5 last years I almost haven't used queries in Access, as I have back-ends for my databases in SQL Server, and mostly I use SQL Server views instead - and SQL Server query syntax gives a lot of options not available in Access;
    3. I haven't Access available this week, and so I can't test anything I am not sure about.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there,
    Have a look at the attached sample, look at the query qryNewReturning - you will need to adapt the last calculated field to match your table and field names and data types.
    Cheers,
    Vlad
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-18-2015, 08:28 AM
  2. Replies: 1
    Last Post: 09-09-2014, 11:29 PM
  3. Replies: 6
    Last Post: 05-05-2014, 10:51 PM
  4. Customers unsold this month Query
    By Rpuzwebb in forum Queries
    Replies: 3
    Last Post: 01-23-2011, 10:00 PM
  5. Replies: 11
    Last Post: 12-09-2010, 10:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums