Dynamic report sections or query data based on field value

[mlance]

Hello, I have a question regarding how to structure my query and/or report.

Assume I have a simple query that looks like this:

Group Product Qty # of batches
GroupA ProdA 100 3
GroupA ProdB 200 3
GroupA ProdC 300 3
GroupB ProdA 10 2
GroupB ProdB 20 2
GroupB ProdC 30 2


# of batches remains constant for each grouping. What I need is the Access Report to break the data up based on the number of batches and repeat the data that many times.

For example, my output would look like:

Group A ProdA 100
Group A ProdB 200
Group A ProdC 300

Group A ProdA 100
Group A ProdB 200
Group A ProdC 300

Group A ProdA 100
Group A ProdB 200
Group A ProdC 300

Group B ProdA 10
Group B ProdB 20


Group B ProdC 30

Group B ProdA 10
Group B ProdB 20
Group B ProdC 30


So because Group A had 3 batches, you can see it is repeated 3 times. Because Group B has 2 batches it is repeated twice. Any ideas on how I would be able to do this? Obviously there is much more behind my example and I am simplifying it. I am intermediate with Access but have no VB or SQL skills. I am using Access 2007 and Windows 7. Any help is appreciated.

[June7]

There are programmatic methods to force a report to repeat rows but I think your requirement is too complex.

The only other approach would be writing the duplicate records to a temp table and basing report on that table.

[ranman256]

Do this in the query. The data in 1 tbl/qry and the multiplier in another. DO NOT JOIN. The absence of the join will cause the tables to multiply!

[June7]

ranman, clever idea. I tested and it works.

The multiplier table would be like:

Row	Multiple
1	1
1	2
2	2
1	3
2	3
3	3
1	4
2	4
3	4
4	4
1	5
2	5
3	5
4	5
5	5

The query would be like:
SELECT Group, Product, Qty, Batches, Multiple
FROM Table1, Table2
WHERE (((Batches)=[Multiple]))
ORDER BY Group, Row, Product;