Complex report w/ flexible SQL and multiuser environ

[KMKelly]

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

 

[vb6novice]

Kris,

That's a pretty good set up. I often do the same when the result set is too complex for a query.

I've never tried this but I'm sure I could make it work. My suggestion is that instead of deleting the records from the report table each time the report is called and appending the new records, you make a new table with the right records. The problems to work out are:

- How to give each new table a different name
- How to get rid of that table later
- How to make the report use the right table

For the table names, a simple lookup table with a counter will do and just use the table name you have now with a few digits tacked on the end.

Getting rid of the old table(s) is just a matter of cleanup. Each time the report is called, the first thing it should do is get rid of any old tables. There might be a little conflict there but you could always have it get rid of all but the last 5 of the report tables, or something like that.

To get the report to use the right table, I would change the report's Record Source property in the reports Open event.

Give these suggestions a try and tell us how it works.