Results 1 to 8 of 8
  1. #1
    kpr is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2019
    Posts
    4

    Post Show only full records of Users that have sold product this year including all their past Sales hist

    I am trying to get show records of Users who have sold product form year to year but only need to see users who have sold items this year while also including all their past years sales.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    What does your data look like?

    I would do it in a series of two queries:
    1. First Query - return a distinct list of Users who have sales in the current year (can use an Aggregate Query to do this)
    2. Second Query - link the First Query to your original Table on the Users, and return all matching records

    Then just use the Second Query as the data source of your Report.

  3. #3
    kpr is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2019
    Posts
    4
    Ok I will try this .
    thank you for your rapid response.
    My basic data criterias. users, classcode, events by date, event points
    Event Points are summed.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Another possibility:

    SELECT * FROM Sales WHERE users IN (SELECT users FROM Sales WHERE Year([events by date]) = 2019);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    kpr is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2019
    Posts
    4
    Ok I will try this .
    thank you for your rapid response.
    My basic data criterias. users, classcode, events by date, event points
    Event Points are summed.

    tried but it has a bit more complexity, has multiple class codes, multiple dates per class, and Points by each date that need summed.
    if that makes since.

    needs three groups and 3 sorts

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Build report and use its Sorting & Grouping features with aggregate calcs. Use one of suggested queries as report RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    kpr is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Oct 2019
    Posts
    4
    thank You for all your assistance, but I am still unable to get this to work properly.

    here is what I have

    fields
    users
    classcode (30 Different Class Codes)
    horse (many horses, some users own more than one horse)
    events (many events by Date)
    points ( Points by each user based on horse and class)
    awards (Awards are given when user and horse meets specific criteria by class, and points)

    I have a report that has all this data properly calculating each users individual class and points by date accuretly.
    what i need to pull from this data is only those users who have events for this year but see all there events associated with the user for previous years.

    This is somewhat complex as There are also other criteria for awards based on certain criteria meet in each class.

    If there is anything you can help with I would greatly appreciate it.

    Thank You

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why not? What happens - error message, wrong result, nothing?

    Provide db for analysis. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-29-2018, 01:22 PM
  2. Replies: 8
    Last Post: 05-03-2017, 02:26 PM
  3. Replies: 5
    Last Post: 03-22-2016, 04:06 PM
  4. Replies: 3
    Last Post: 02-26-2013, 05:07 AM
  5. Replies: 9
    Last Post: 05-11-2012, 11:18 AM

Tags for this Thread

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