Results 1 to 10 of 10
  1. #1
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

    Grouping

    Hi Everyone

    In the attached database the Report displays the Sum of All Data in the Current Month.

    If No Data has been entered for any Previous Months is it possible to Display a Zero Value?



    What I want to see in the Report is this:-
    Attached Thumbnails Attached Thumbnails Report.PNG  

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a 'report' table to hold all months with zeros.
    empty, then add the months & zeros of your date range, every time you begin the report. Q1 will pull this data.
    then Q2 is your regular data from the main table.

    Q3 is a union query that adds both Q1 & Q2.
    run the report on Q3.

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi ranman

    This Report is just a subreport that is giving a Summary of data for the Main Report.

    It is not based on Quarters at all.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what is the controlsource to your Current Month? It may be you can use the format property to display 0 if null

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Ajax It it using the Date fields in the underlying query

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sorry - I meant previous month and I mean what you actually have in the controlsource - =DSum(whatever), not a description

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Ajax

    I attache the modified example with some fields removed from the table.

    At the moment the query lists all of the records for this Month which are then displayed in the Report as the Current Sum of Additions

    What I am trying to do is add to the query a criteria that would show a Zero Value for the Previous Months, even though there are no previous records.

    Hope that makes sense?
    Attached Files Attached Files

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so basically you want a single query that provides a brought forward figure.

    You can't do that if the data doesn't exist, so you have to create it

    use something like this as the control source to your previous month control

    Code:
    =Nz(DSum("additions","tblChangeOrderSummary","Periodto<=dateadd('d',-day(date()),date())"),0)
    or drop the nz and use the format property as previously suggested to display 0.00 when null

    You can apply the same principle in an aggregate query by using a calculated date

    Code:
    SELECT iif(Periodto<=dateadd('d',-day(date()),date()),dateadd('d',-day(date()),date()),PeriodTo) as Period, sum(additions) as addition
    FROM tblChangeOrderSummary
    GROUP BY iif(Periodto<=dateadd('d',-day(date()),date()),dateadd('d',-day(date()),date())
    however that will not produce a zero previous balance if there are no records

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    An example file added.

    I added a table where months are registered as 1st of month (you can register months for any time period for advance there), and in your table instead of separate fields for incoming and outgoing sums, added a single sums field and a sign field. I left your old sum fields in place (so your original query still works), but they are now abundant.
    Attached Files Attached Files

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi ArviLaanemets

    I am sorry but I am not following your solution.

    How would I populate the table's Sign field from the Data Input Form?


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

Similar Threads

  1. Grouping
    By Yewee in forum Reports
    Replies: 12
    Last Post: 07-05-2017, 12:52 PM
  2. About Grouping
    By GraeagleBill in forum Reports
    Replies: 7
    Last Post: 03-02-2017, 02:36 PM
  3. Grouping by certain qty.
    By reldridge in forum Queries
    Replies: 1
    Last Post: 12-03-2012, 03:41 PM
  4. Help with grouping
    By RachelBedi in forum Queries
    Replies: 1
    Last Post: 11-05-2012, 12:55 PM
  5. Grouping By Age
    By xnixiel in forum Queries
    Replies: 1
    Last Post: 07-01-2010, 09:14 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
  •  
Other Forums: Microsoft Office Forums