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.