Results 1 to 9 of 9

View created record totals for different periods

  1. #1
    mick3911 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Posts
    28

    View created record totals for different periods

    I wish to view how many records (by number) have been created during the following periods;


    Last Week
    Last Quarter
    Last Year
    Would I have to create 3 separate queries or can I just create one query with all the required data?
    If just one query can be created, what would the different date expressions look like and how would I separate the different totals?

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,606
    If just one query can be created, what would the different date expressions look like and how would I separate the different totals?
    I have done something like this before.
    What you would do is create three calculated fields.
    First: an IIF formula that returns a 1 if in the last week, or 0 otherwise
    Second: an IIF formula that returns a 1 if in the last quarter, or 0 otherwise
    Third: an IIF formula that returns a 1 if in the last year, or 0 otherwise

    Then, you can do an Aggregate Query, using the SUM aggregate function on these three calculated fields to return the totals.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    mick3911 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Posts
    28
    Hi JoeM,

    Thanks for your reply.

    This is unknown territory for me so shall have a 'play' and see what i can do.

  4. #4
    mick3911 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Posts
    28
    So I have created a query with the following expressionQuery.zip; but when i open/run the query it lists all the records as 1 instead of just listing the records as 1 using the date criteria.

    Where I have I gone wrong?

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,606
    I cannot download files from my current location.
    Can you switch your query to SQL View, and copy and past the SQL code here so I can see how you have written it?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  6. #6
    mick3911 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Posts
    28
    Hi JoeM,

    As requested;

    SELECT IIf([Date] Between Date() And Date()-6,"1","0") AS [Last Week], IIf([Date]>=1/1/18<=31/3/18,"1","0") AS [1st Quarter], IIf([Date]>=1/4/18<=30/6/18,"1","0") AS [2nd Quarter], IIf([Date]>=1/7/18<=30/9/18,"1","0") AS [3rd Quarter], IIf([Date]>=1/10/18<=31/12/18,"1","0") AS [4th Quarter], IIf([Date]>=1/1/18<=31/12/18,"1","0") AS [2018 Total]
    FROM [NCR Details];

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,606
    A few things. That is not an Aggregate (Totals) Query. See here: https://www.fontstuff.com/access/acctut04.htm
    You will want to click on the Totals button, and change the field under each calculated field to SUM.

    A few other notes:
    - You want to return numeric values, not text ones. So remove the double-quotes from around the 1 and 0 in your formulas.
    - I believe you need to surround your dates in #, i.e. [Date]>=#1/10/18#. Otherwise, you are doing division (1 divided by 10 divided by 18)
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,178
    Date is a reserved word. You should not use them for field, variable or object names. It's not the cause of your problem, but the habit will get you into trouble sooner or later.
    http://allenbrowne.com/AppIssueBadWord.html

    http://access.mvps.org/access/general/gen0012.htm

    https://access-programmers.co.uk/for...d.php?t=225837
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Code isn't smart. It does what you ask with what you give it.

  9. #9
    mick3911 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Posts
    28
    That might explain why after doing what JoeM suggested, I am still getting all the records showing/being calculated instead of the records that were raised within the dates. My boss has now decided to go down a different route so thank you to all who have submitted suggestions/advice.

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

Similar Threads

  1. Sum Specific Rows as Totals in Datasheet View
    By CementCarver in forum Forms
    Replies: 4
    Last Post: 07-31-2013, 06:31 AM
  2. YTD Totals in Form View and Reports
    By nrh0804 in forum Access
    Replies: 3
    Last Post: 01-10-2012, 09:15 AM
  3. Replies: 0
    Last Post: 11-30-2011, 01:01 PM
  4. View Created date and time for key in Registry
    By startop10 in forum Programming
    Replies: 2
    Last Post: 01-21-2011, 12:52 PM
  5. Subform, Totals, in Datasheet view
    By eww in forum Programming
    Replies: 1
    Last Post: 09-27-2010, 09:22 AM

Posting Permissions

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