I have a report that looks like this:
12 months down in the detail section with numerous columns across (charges, payments, write offs) and a total and average per month summary at the bottom of this list
Further down a subreport with total and average per month for the previous 12 month period.
A section that shows the difference in totals, difference in averages, and % change in averages, this is actually calculated in the report using the results in the subreports.
Further down Fiscal Year to date total and average per month (July to current month), followed by a section with previous fiscal year to date and a section that does the differences.
Finally the same thing for calendar year to date (January to current month) and previous calendar year to date and those differences.
I had this all set up with subreports and separate queries feeding them and you just picked a division. BUT, I was asked to add more query possibilities. The desired criteria would be division, subdivision, and service provider in any combination. I built the code to create a SQL statement and have a workable report that uses an existing table, deletes its records, appends the results of the SQL statement recordset to it, and runs all the queries that feed the subreports off this filtered, summarized dataset. Works fine... but I need it to work in a multiuser environment and since I am altering the contents of this table it won't if more than one person happens to use it at about the same time.
I thought about naming te table for current user (work in their user name somehow) but then how do I feed the queries when the table would always have a different name. You can't feed a report directly off a recordset can you?
Does anyone have any ideas? I love the way it works now but I am operating on a cross your fingers basis that few people are accessing it now so hopefully it won't break- not good. I need to eventually change all my reports to this style. Yikes!
Thanks for any advice.
Kris
12 months down in the detail section with numerous columns across (charges, payments, write offs) and a total and average per month summary at the bottom of this list
Further down a subreport with total and average per month for the previous 12 month period.
A section that shows the difference in totals, difference in averages, and % change in averages, this is actually calculated in the report using the results in the subreports.
Further down Fiscal Year to date total and average per month (July to current month), followed by a section with previous fiscal year to date and a section that does the differences.
Finally the same thing for calendar year to date (January to current month) and previous calendar year to date and those differences.
I had this all set up with subreports and separate queries feeding them and you just picked a division. BUT, I was asked to add more query possibilities. The desired criteria would be division, subdivision, and service provider in any combination. I built the code to create a SQL statement and have a workable report that uses an existing table, deletes its records, appends the results of the SQL statement recordset to it, and runs all the queries that feed the subreports off this filtered, summarized dataset. Works fine... but I need it to work in a multiuser environment and since I am altering the contents of this table it won't if more than one person happens to use it at about the same time.
I thought about naming te table for current user (work in their user name somehow) but then how do I feed the queries when the table would always have a different name. You can't feed a report directly off a recordset can you?
Does anyone have any ideas? I love the way it works now but I am operating on a cross your fingers basis that few people are accessing it now so hopefully it won't break- not good. I need to eventually change all my reports to this style. Yikes!
Thanks for any advice.
Kris