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.
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.
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.
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.
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.
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
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.
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
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.