Solved Expand multiple identical items (1 Viewer)

[Charlie8585]

So I'm building report with recordset based on this query for single order:

Problem is I need to print 6 x stickers so need 6 x report entries - i.e. need to expand that NumberOfSmoothies field. Is there functionality in Access reports to do this? Or do I have to adjust query/recordset so it looks like this (and, if so, how(!))?:

 

[Pat Hartman]

The easy way to do this is to create a table with a hundred rows (or more if you need more). Each row has a single field with the values of 1-100. In the label query, join the quantity field to this table. Then switch to SQL view and change the join to >= and that will duplicate the records so each item will have as many labels as are needed. No coding is required. you end up with 3 labels for the Peachy Mangoes because 3 is >= 3,2,and 1, 2 for Blueberry and 1 for Cacao & Vanilla.

Once you change the join type, you won't be able to change back to design view since the qBE can only graphically represent "equi" joins which are joins using the = sign.

 

[June7]

Why do you show 2 Blueberry and only 1 Cacao & Vanilla? Shoudn't it be 1 Blueberry and 2 Cacao & Vanilla?

 

[Pat Hartman]

I looked at the sample data rather than the initial count. One of the two is a typo.

 

[Charlie8585]

Why do you show 2 Blueberry and only 1 Cacao & Vanilla? Shoudn't it be 1 Blueberry and 2 Cacao & Vanilla?

Yes it should! Error in my mockup, now corrected, thank you!

 

[CJ_London]

The easy way to do this is to create a table with a hundred rows

you can do it with a table with10 rows and a query - and @June7 has a method just using a query

usysCounter table

​

usysCount query

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM usysCounter AS singles, usysCounter AS tens, usysCounter AS hundreds, usysCounter AS thousands;

which gives numbers from 0 to 9999

If you only ever need to go to 999 then you can remove the 'thousands' element. Similarly if you need to go larger, add a tenThousand element.

I prefix with usys (user system) as I use it everywhere and have found 9999 is as large as I have ever needed to go (actually more like 3700 for around 10 years worth of data). Also means it will be hidden from users unless they have ticked show system objects in file>options

Then switch to SQL view and change the join to >=

Alternatively just have a cartesian query (no join) and put <= qty (or whatever your field is called) as a criteria on the counter field

 

[Pat Hartman]

When you create a Cartesian product and then filter with criteria, you cause the query engine to do a more work than if you use a non-equi-join as I suggested because before the recordset can be filtered, it has to be built. I'm pretty sure we are not talking about more than a few hundred records in this situation but there is no point in using poor practice just because your recordset is small.

 

[Charlie8585]

The easy way to do this is to create a table with a hundred rows (or more if you need more). Each row has a single field with the values of 1-100. In the label query, join the quantity field to this table. Then switch to SQL view and change the join to >= and that will duplicate the records so each item will have as many labels as are needed. No coding is required. you end up with 3 labels for the Peachy Mangoes because 3 is >= 3,2,and 1, 2 for Blueberry and 1 for Cacao & Vanilla.

Once you change the join type, you won't be able to change back to design view since the qBE can only graphically represent "equi" joins which are joins using the = sign.

This worked perfectly! Lovely solution, thank you @Pat Hartman .

you can do it with a table with10 rows and a query - and @June7 has a method just using a query

usysCounter table
View attachment 100179

​

usysCount query

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM usysCounter AS singles, usysCounter AS tens, usysCounter AS hundreds, usysCounter AS thousands;

which gives numbers from 0 to 9999

If you only ever need to go to 999 then you can remove the 'thousands' element. Similarly if you need to go larger, add a tenThousand element.

I prefix with usys (user system) as I use it everywhere and have found 9999 is as large as I have ever needed to go (actually more like 3700 for around 10 years worth of data). Also means it will be hidden from users unless they have ticked show system objects in file>options


Alternatively just have a cartesian query (no join) and put <= qty (or whatever your field is called) as a criteria on the counter field

Thank you @CJ_London . Pat's solution is fine for me for now as currently only sending out a maximum of 7 x smoothie mixes (they can be all same flavour or all different or in between) so I don't need usysCounter table to be any more than that. However I have pinched your "usys" prefix suggestion to use with this.

Minor question: I tried your solution (as will tuck it into back pocket in case I need down the line) and your SQL gave me number from 1111-9999 rather than 0-9999?