Is there a way to create "automated" forms/reports in MS Access?

Question

Shortly, I have workers/production/salary dataset, where I need to create a form/report to show 2N number of variables for each worker. 2N, because I need to show how many N products were produced and for each product, show it's produced money equivalent. That number is coming from the fact that one period, firm produces one N types of goods, another period many new positions could appear. (I've made a form to input new products). My final table looks something like:

Worker	var1_$	...	varN_$	var1_made	...	varN_made	Total_$	Total_made
Arthur	100	...	500	900	...	1300	1700	2100
Molly	200	...	600	1000	...	1400	1800	2200
William	300	...	700	1100	...	1500	1900	2300
Charlie	400	...	800	1200	...	1600	2000	2400

Everytime I create a form like this:

Worker: Arthur	$	Made
var1	100	900
...	...	...
varN	500	1300
Total	1700	2100

From what I understand, if I need a form/report, each period, I need to create it manually, because I have new set of variables. That makes last report useless, because item number N+1 won't be showing, or if item number 20 was not created at all, it will be empty but still take some space. Repeating it too many times, you would suffer.

The problem is the file will be used by someone now, who is very old and who will suffer for sure to learn it.

How can I automate it?

Answer 1

Well, first up, what did and has the industry done for the last 40 years?

In other words, it would be remarkable that you out of 8+ billion people are the only person in this part of a galaxy that has this issue, right?

So, the common term here is what we call a cross tab query. That turns many rows of data into columns and their sum totals.

So, you have to keep 100% separate the concept of normalized data (databases are NOT a spreadsheet, and thus thinking of data management and database systems in terms of spreadsheets is a mistake that is all too common here).

So, the first issue is the "n" or number of columns you want for output. If you just looking to output the summary data, then you can often send the data to excel (probably the most common solution). So, Excel as an output from the database is still a great way to produce such cross-tab output of data.

The other approach is to LIMIT the number of groups you wish to report on.

So, say we build some complex query, multiple table joins, and the resulting summary query outputs the data with typical SQL grouping.

so, what you can then do is launch the query wizard and select a cross-tab query.

Hence, say we have some bookings, by city, and then by hotel (with a total).

So above is normalized data.

But, with the cross-tab wizard, then the output now goes across.

So, we can launch the query wizard against the above. hence:

So, for each city, we now have hotel headings across the top. (And it grows to as many hotels as we have in the report

The result is this:

So, while a cross tab will take rows going down, and spit them out going across?

The issue in most cases is such reports can't fit on the page. In above, the issue is that the group of hotels for EACH city becomes a column, and each city does not have common hotels, so we wind up with quite a few columns.

So, you CAN build a report system with say a max of 20 columns going across. And anymore columns, then I dump into a last column called "other".

As a result, often I send the output of such a query to Excel, since as noted, trying to send to a report not all that great, since it will never fit on the page going across anyway.

The only REAL big lesson and takeaway here?

Always still build a good normalized database. You can then use queries to slice and dice the data anyway you want -- including of taking groups, and having them go "across" the page in place of typical report grouping going down.

So, due to above, just have a button, maybe a filter option.

Let user select the destinations or whatever.

So, one of my forms looks like this:

So, a date range, and multiple destinations can be selected.

So, with 3 cities, then the report looks like this:

So, once such queries are built, then as noted, you can build a report that would allow selecting of some groups, but you have to decide ahead of time the max number of columns you want in that report (I allowed 8 max in above). So, often, as noted, you can let the user select (filter) what groups they want, but then you export the data to Excel and launch Excel as your report and data viewer.

Answer 2

Whether or not if creating a table for each condition is a good or bad idea by itself, a possible approach might be as following:

Do not create any form over the final-table;

1. Create an unbound form.
2. Add a combo-box to the form header to select the worker (workers table as datasource).
3. Add an unbound sub-form to the form.
4. In the combo after-update event, run the VBA codes/queries to create the final-table based on the selected worker (first delete last final-table if exists).
5. Set the source-object of the sub-form to the final-table.