Repeating rows

[someboddy]

Is there a way to make rows repeat themselves several times in a query?

Lets say I have a table:
value count
A 2
B 5
C 1
D 3
E 4

I want a query that gives the result:
A
A
B
B
B
B
B
C
D
D
D
E
E
E
E

Each field repeat itself as much times as specified in the "count" value.

What I was thinking is to create another table(lets call it tableB. The table I described above will be tableA) containing a single integer field("number"), and fill it numbers from 1 to N. Than I make a query from both tables(without a join), so each field in tableA table will have N rows in the query - one for each field in table B. Finally I state that "number" must be smaller or equal to "count", so only "count" rows will be in the query for each field in tableA:

SELECT tableA.value
FROM tableA, tableB
WHERE (((tableB.number)<=[count]))
ORDER BY tableA.value;

Now, this works, but it's kind of a clumsy method that can really slow things down with large tables. Plus, I have to keep N as low as possible to make the slow-down from getting too bad, which means I have to use a very low max-limit on "count"...

So, is there a better way to solve this?

 

[Atomic Shrimp]

Could count consist of any number (including really large ones), or is there an upper limit?

If there's an upper limit of, say, 10, you could create another table called (say) 'instances' and populate it as follows:
1
2
2
3
3
3
4
4
4
4
(etc)

Then include this table in your query and join it to the count field in your other table

I have to say, however...

The above is a really dirty method, and I expect to be criticised for it.

If Count is an indefinite quantity, this is the wrong method - you'd have to do some sort of iterative temporary table build using VBA.

But perhaps there is an entirely different solution to your problem - what is it that you're trying to achieve that requires these repeating rows?

 

[someboddy]

Well, I'm using Access reports to create cuttable printouts from my tables. There is a table where each row represents a single printout data, many more tables linked to it. Now, I want to let the user choose how many units of each printout he wants to print, so I created a table with the main printouts table's pk and a "count" field - similar to tableA in my example.

Anyways, I guess I could go with a limit of 10 copies for each printout...