Print multiple copies of report based on qty of item on quote. (1 Viewer)

[slharman1]

I have a report that I need to print out for each record on a quote, the problem is that I need the report to print duplicates for each line item based on quantity.
So if Line 1 has a qty of 1 print one copy, line two has a qty of 10 print 10 copies for that item, ect.
Having trouble getting this one figured out, Thanks

 

[arnelgp]

make another table for the count (tblCount) and create a cartesian query (query1).
see this report in Print preview.

 

[slharman1]

make another table for the count (tblCount) and create a cartesian query (query1).
see this report in Print preview.

Thanks Arnel, still trying to decipher all of this

make another table for the count (tblCount) and create a cartesian query (query1).
see this report in Print preview.

Trying to figure out how the code in the module is entering into this setup. Do I just make the Table tblCounter as you have done and create the cartesian query? I can't figure out where the code is running to insert into the table

 

[theDBguy]

I have a report that I need to print out for each record on a quote, the problem is that I need the report to print duplicates for each line item based on quantity.
So if Line 1 has a qty of 1 print one copy, line two has a qty of 10 print 10 copies for that item, ect.
Having trouble getting this one figured out, Thanks

Hi. When you said "10 copies," for example, are you talking about 10 separate pages? Just curious...

 

[Pat Hartman]

I don't understand what you are duplicating. Are you saying to print two lines for an item if it has a quantity of 2 and three if it has a quantity of three? or are you saying to print multiple copies of the report?

 

[slharman1]

make another table for the count (tblCount) and create a cartesian query (query1).
see this report in Print preview.

Hold on
I think i am getting it....
Just run the module to create the long table - correct?

 

[slharman1]

I don't understand what you are duplicating. Are you saying to print two lines for an item if it has a quantity of 2 and three if it has a quantity of three? or are you saying to print multiple copies of the report?

Trying to print the page for the current item in the report as many times as the quantity listed on the order so I will have a copy for each item being produced. So if Item 2 has a qty of 4 then the report should print that page four times.
I believe Arnel has already shown me the way, I'll confirm when I have it.
Thanks guys!

 

[Pat Hartman]

So, each item is a separate page? In that case, @arnelgp is sort of on the right track but you wouldn't use a Cartesian Product. Make a table with values 1-n for the quantities you expect. Then join the items to this table on quantity. This will duplicate the records as many times as the quantity.

The Cartesian Product with the where clause will have the same result as the inner join on quantity. The difference is efficiency. Cartesian products produce HUGE recordsets. Every row in tableA is joined to every row in tableB so 1000 rows times a max of 200 quantity = 200,000 rows and then the criteria is applied to winnow down the recordset.

Of course when you are working with small numbers of records nothing you do matters. But, it it is always better to use best practices when you know them.

 

[Uncle Gizmo]

I answered a very similar question a few years ago. You can see my answer on my website here:-

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

I suspect all you need to do is add an extra loop around the part that generates the report, extract the number of items from the line item and run the loop that number of times.

Unfortunately I am unable to adapt the code myself, as I am on holiday and nowhere near a computer!

 

[Pat Hartman]

Not sure that will work Tony. The outer loop is to produce 1 copy of multiple different reports. This seems to be how to duplicate pages of a report based on a quantity field in the record.

 

[Uncle Gizmo]

Not sure that will work Tony

I'm not too sure either Pat. I really need to be sat in front of a PC to test it!

 

[slharman1]

So, each item is a separate page? In that case, @arnelgp is sort of on the right track but you wouldn't use a Cartesian Product. Make a table with values 1-n for the quantities you expect. Then join the items to this table on quantity. This will duplicate the records as many times as the quantity.

The Cartesian Product with the where clause will have the same result as the inner join on quantity. The difference is efficiency. Cartesian products produce HUGE recordsets. Every row in tableA is joined to every row in tableB so 1000 rows times a max of 200 quantity = 200,000 rows and then the criteria is applied to winnow down the recordset.

Of course when you are working with small numbers of records nothing you do matters. But, it it is always better to use best practices when you know them.

Thank Pat, I will take a look, Arnel's query definitely doesn't product a 100 plus records though.
So maybe this is not in my head straight yet.
Thanks

 

[slharman1]

Not sure that will work Tony. The outer loop is to produce 1 copy of multiple different reports. This seems to be how to duplicate pages of a report based on a quantity field in the record.

Correct

 

[slharman1]

So, each item is a separate page? In that case, @arnelgp is sort of on the right track but you wouldn't use a Cartesian Product. Make a table with values 1-n for the quantities you expect. Then join the items to this table on quantity. This will duplicate the records as many times as the quantity.

The Cartesian Product with the where clause will have the same result as the inner join on quantity. The difference is efficiency. Cartesian products produce HUGE recordsets. Every row in tableA is joined to every row in tableB so 1000 rows times a max of 200 quantity = 200,000 rows and then the criteria is applied to winnow down the recordset.

Of course when you are working with small numbers of records nothing you do matters. But, it it is always better to use best practices when you know them.

Pat, did you look at Arnel’s database he posted. I am not seeing what is wrong with it. Can you expound on your method please.

 

[arnelgp]

Hold on
I think i am getting it....
Just run the module to create the long table - correct?

you only Run the code once to produced those 1000 records (more or less if you want).
its tiring to insert those records manually!

the heart is on the query and the report (it has PageBreak control).

 

[slharman1]

you only Run the code once to produced those 1000 records (more or less if you want).
its tiring to insert those records manually!

the heart is on the query and the report (it has PageBreak control).

Thanks I am going to give it a try in the morning.

 

[Pat Hartman]

Pat, did you look at Arnel’s database he posted. I am not seeing what is wrong with it. Can you expound on your method please.

You might want to reread #8 again

Arnel's query definitely doesn't product a 100 plus records though.

There are 4 records in the data table and 1000 records in the counter table. Therefore the query will produce 4 x 1000 = 4000 records. The where clause will then whittle that down to nine records. Remove the where clause to see what the cross join does. You only have 4 records in the test table so you should be able to clearly understand what the cross join is doing. The query engine must create the 4,000 records in memory BEFORE it can apply the where clause. Just because you don't see them doesn't mean they weren't created in order to get to the answer you need.

Then start again with that same query1 but make two changes.
1. remove the where clause
2. draw a join line between the quantity field and the number field. Switch to SQL view and change the "=" to ">="

Here's the modified query:

SELECT tblLineItems.parentID, tblLineItems.item, tblLineItems.quantity, tblCounter.number
FROM tblLineItems INNER JOIN tblCounter ON tblLineItems.quantity >= tblCounter.number
ORDER BY tblLineItems.item, tblCounter.number;

I included the number field from the counter table because I think it should be there to identify each row.

Here's the modified query1
SELECT tblLineItems.parentID, tblLineItems.item, tblLineItems.quantity, tblCounter.number
FROM tblLineItems, tblCounter
WHERE (((tblCounter.number)<[quantity]+1))
ORDER BY tblLineItems.item, tblCounter.number;

That will show you the difference. My query returns the same nine records but didn't have to create 3, 991 useless ones first.

I will repeat again, the example arnel posted will produce the same results as my suggested change. The difference is simply efficiency. But when you have only 4 records in one table and a relatively small list in the other table, NOTHING matters. However, if you want to use this technique on a larger set of records, you will find the cross join to be unusable because of the huge intermediate recordset it has to create.

There are situations where you actually do need to use the cross join. This just isn't one of them.

 

[slharman1]

You might want to reread #8 again

There are 4 records in the data table and 1000 records in the counter table. Therefore the query will produce 4 x 1000 = 4000 records. The where clause will then whittle that down to nine records. Remove the where clause to see what the cross join does. You only have 4 records in the test table so you should be able to clearly understand what the cross join is doing. The query engine must create the 4,000 records in memory BEFORE it can apply the where clause. Just because you don't see them doesn't mean they weren't created in order to get to the answer you need.

Then start again with that same query1 but make two changes.
1. remove the where clause
2. draw a join line between the quantity field and the number field. Switch to SQL view and change the "=" to ">="

Here's the modified query:

SELECT tblLineItems.parentID, tblLineItems.item, tblLineItems.quantity, tblCounter.number
FROM tblLineItems INNER JOIN tblCounter ON tblLineItems.quantity >= tblCounter.number
ORDER BY tblLineItems.item, tblCounter.number;

I included the number field from the counter table because I think it should be there to identify each row.

Here's the modified query1
SELECT tblLineItems.parentID, tblLineItems.item, tblLineItems.quantity, tblCounter.number
FROM tblLineItems, tblCounter
WHERE (((tblCounter.number)<[quantity]+1))
ORDER BY tblLineItems.item, tblCounter.number;

That will show you the difference. My query returns the same nine records but didn't have to create 3, 991 useless ones first.

I will repeat again, the example arnel posted will produce the same results as my suggested change. The difference is simply efficiency. But when you have only 4 records in one table and a relatively small list in the other table, NOTHING matters. However, if you want to use this technique on a larger set of records, you will find the cross join to be unusable because of the huge intermediate recordset it has to create.

There are situations where you actually do need to use the cross join. This just isn't one of them.

Cool,
I will give it a try but I have one question - What if I want one extra printout, so if Qty is 4, the query will produce 5 records?

 

[Pat Hartman]

The simplest is to make the quantity whatever you want to print. Otherwise, you need a calculation in join:

Select ..
From YourTable Inner Join tblCounter on YourTable.Quantity +1 >= tblCounter.Number

 

[slharman1]

The simplest is to make the quantity whatever you want to print. Otherwise, you need a calculation in join:

Select ..
From YourTable Inner Join tblCounter on YourTable.Quantity +1 >= tblCounter.Number

Thanks