Results 1 to 4 of 4
  1. #1
    ThornofSouls's Avatar
    ThornofSouls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2015
    Location
    Sweden, Gothenburg
    Posts
    42

    Question How do i populate my grand total report efficiency?

    Hello.
    I'm building a grand total report which which purpose is show the users the grand total from several tables. It is statistics on how many clients it is in the db, how many clients have active and closed measures, and much more in various combinations. There was no big deal with the grand total statistics, however i found to difficult to populate the grand total report. In total i have 44 queries to hater all the grand total statistics and i feel this is going overboard but i guess it's a common thing. Anyway i don't know i how i should or want to approach the method to populate the grand total report with these queries.

    The grand total report is unbound and all the comboboxes on the report as well. If i should use combobox or text fields i'm not entirely sure on, but i thought a combobox would make it easier.
    Of these 44 queries are pretty much identical, the only thing which differs is the source and target column.




    Only a handful of the 44 queries look like the 3 at the top and the majority looks like the last query where the table, column and the filterID changes. With this approach it seems like i have these queries populate a grand total table and from there populate the report. However, since īmost of the queries are almost identical i would prefer to convert them to vba and put the in a function i can call whenever i need to, and change the FilitID with an variable instead of all these queries i currently have. As in the second example. Then if i can take the query result and pass it to another variable and then to the grand total report, it would be more efficient i believe, at least easier to grasp.

    Example 1

    Code:
    SELECT Count([ClientID]) AS SumTblClients
    FROM tblClients;
    
    SELECT Count([EndDate]) AS SumTblClientsStartDate
    FROM tblClients;
    
    
    SELECT Count([Startdate]) AS SumTblClientsEndDate
    FROM tblClients;
    
    SELECT Count([SysEfter]) AS SumSysEfter_Arb
    FROM tblATG
    WHERE (((tblATG.SysEfter)=1));
    Example 2
    Code:
    SQL = "SELECT Count([SysEfter]) AS [SumSysEfter_Arb]" _
           & FROM [tblATG]" _
           & "WHERE ((([tblATG].[SysEfter])=" & FiltID & "));"
    Download the db here.
    The db default language is Swedish but you can change the language to english with the language controle, just use the flags on the StartUp form.
    If you feel some information in my question is missing, please let me know and i'll add it as soon i can.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In a macro put all your append queries.
    each query will append data to the target 'report' table into its desired columns
    then report on this table will sum all the columns/subject.

  3. #3
    ThornofSouls's Avatar
    ThornofSouls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2015
    Location
    Sweden, Gothenburg
    Posts
    42
    I don't really know how the macros work, haven't ust them that much as all. I know they are a powerful tool but i tend to use vba more.
    So simply i don't know where to start to make this with a macro. On the other hand did i split all the count queries to tow queries and then merge them with another query, that way i did actually avoid the memory overflow which surprised me. It works and the date is so far correct. But i don't know how will this method will work in the long run.

    To make this even better would be if the statistics could be sorted by quarter and year and half year.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    just add the queries to the macro
    setwarnings OFF
    openquery "qry1"
    openquery "qry2"
    ...

    then save and run the macro.

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

Similar Threads

  1. Grand total on grouped report?
    By dmon2010 in forum Reports
    Replies: 1
    Last Post: 02-26-2015, 02:02 PM
  2. Replies: 4
    Last Post: 06-07-2014, 01:54 PM
  3. How to Calculate this Grand Total
    By TWD in forum Reports
    Replies: 2
    Last Post: 01-13-2012, 11:36 AM
  4. Grand Total Field ???
    By damo1995 in forum Forms
    Replies: 6
    Last Post: 02-10-2011, 02:43 PM
  5. :?: newbie...grand total?
    By PnerraD in forum Access
    Replies: 1
    Last Post: 08-24-2006, 06:28 PM

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