Results 1 to 3 of 3
  1. #1
    BeardedSith is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    1

    Count() Unique Dates Between Two Variables

    I need to count the # of unique dates in tbPurchases between the greater of ( Max(tblRewards.IssueDate) or DateSerial(Year(Now()),1,1,) ) and Now(). Records previous to these dates should not be counted. Here is some example data:


    tbPurchases

    ID MemberID PurchaseDate PurchaseAmount
    5639
    1
    2/14/2020
    $100.00
    5638
    1
    2/13/2020
    $500.00
    5637
    1
    2/13/2020
    $1,000.00
    5636
    1
    2/13/2020
    $100.00
    5635
    1
    2/11/2020
    $79.99
    5634
    1
    2/6/2020
    $85.63
    5633
    1
    2/6/2020
    $150.00
    5631
    26347
    2/6/2020
    $586.25
    5630
    26347
    2/6/2020
    $150.00
    5629
    26347
    2/6/2020
    $50.00
    5628
    26347
    2/6/2020
    $300.00
    5627
    26347
    2/6/2020
    $150.00
    5626
    1
    2/3/2020
    $15.00
    5622
    1
    1/24/2020
    $65.00
    5621
    1
    1/24/2020
    $2,500.00
    5620
    1
    1/24/2020
    $15.00
    5624
    1
    1/24/2020
    $55.00
    5625
    1
    1/24/2020
    $95.00
    5623
    1
    1/24/2020
    $35.00
    1
    1
    1/9/2020
    $150.99
    3191
    26347
    12/3/2019
    $4.17
    4575
    26347
    12/3/2019
    $4.99
    2886
    26347
    12/3/2019
    $101.95
    346
    26347
    12/3/2019
    $551.18
    4846
    26347
    12/3/2019
    $1,857.70
    1232
    26347
    11/30/2019
    $1.50
    3485
    26347
    11/29/2019
    $130.98
    3484
    2
    11/20/2019
    $300.00
    3483
    2
    11/20/2019
    $300.00
    3482
    2
    11/20/2019
    $300.00
    3481
    2
    11/20/2019
    $300.00




    tblRewards
    ID CustomerID IssueDate IssueAmount
    6008 26347 2/6/2020 $100.00
    6007 1 2/4/2020 $100.00
    6006 26347 12/29/2019 $100.00


    CURRENT OUTPUT
    qryCalculations


    MemberID NumPurchases SumOfPurchaseAmount RealDate IsEligible EligibleAmount
    1 7 $2015.62 2/4/2020 1 $100.00
    26347 5 $1236.25 2/6/2020 0 $100.00


    EXPECTED RESULTS:
    qryCalculations


    MemberID NumPurchases SumOfPurchaseAmount RealDate IsEligible EligibleAmount
    1 3 $1,915.62 2/4/2020 0 $0.00
    26347 0 $0.00 2/6/2020 0 $0.00


    ISSUES:

    • NumPurchases - Currently it's counting each purchase as an individual Count(*)
      • I need it to count unique dates the purchases were made
      • Example: 2/13/2020 - MemberID=1 made 3 purchases. These should only be counted as 1.



    • SumOfPurchaseAmount - Currently calculating all purchases, and not those from NumPurchases



    CURRENT SQL:
    For a starting point, here is the current SQL statement:
    Code:
    SELECT qryCountCurrentYear.MemberID, qryCountCurrentYear.NumPurchases, qryCountCurrentYear.SumOfPurchaseAmount, sqryLastRewards.RealDate, IIf([qryCountCurrentYear]![NumPurchases]>=[tblOptions]![NumPurchases],1,0) AS IsEligible, IIf([qryCountCurrentYear]![SumOfPurchaseAmount]>[tblOptions]![MaxIssue],[tblOptions]![MaxIssue],[qryCountCurrentYear]![SumOfPurchaseAmount]) AS EligibleAmount
    FROM tblOptions, qryCountCurrentYear 
    INNER JOIN sqryLastRewards ON qryCountCurrentYear.MemberID = sqryLastRewards.CustomerID;
    There may be questions, so if so, check here. I'll answer questions here, too.

    1. Why isn't MemberID=2 shown in the results? Because no purchases were made in the current Calendar year.
    2. What is RealDate and IsEligible? You can ignore these, they're expressions in the query.
    3. What is EligibleAmount? Another expression, ignore it.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,370
    Use a new select distinct query (or a totals query grouping by memberid and date) with your desired between dates condition then another in which you group by memberid and count (date).
    Cheers,

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,225
    before anyone gets too deep into this - see the cross post here https://www.access-programmers.co.uk....309367/page-3 - already up to 44 posts

    @BeardedSith - it is not wrong to cross post but it is considered a general courtesy when cross posting to advise you are doing this and providing a link. See this link for the reasons why https://www.excelguru.ca/content.php?184

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

Similar Threads

  1. Unique count
    By Mr.E in forum Queries
    Replies: 1
    Last Post: 02-09-2018, 11:21 AM
  2. Replies: 1
    Last Post: 03-06-2017, 06:48 AM
  3. Unique Value of three variables combined
    By SFGiants21256 in forum Database Design
    Replies: 2
    Last Post: 08-23-2015, 07:18 AM
  4. Replies: 3
    Last Post: 02-06-2014, 10:17 AM
  5. Unique queries - Unique Count
    By bigfish in forum Queries
    Replies: 7
    Last Post: 03-14-2012, 01:28 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 - Senior Forums