Display a data set x times

[cheberdy]

Is there a way to create a query ,that does the following. There is a table with column name, identification number and a quantity. The query should represent the name and identifiaction number. Both should be representet quantity times.

 

[theDBguy]

You may be able to use a Tally table and a Cartesian Query to get what you want.

 

[Pat Hartman]

@cheberdy You're new here. Welcome. Your question is unclear. Can you give us an example of the data you have and the results you want.

 

[cheberdy]

@cheberdy You're new here. Welcome. Your question is unclear. Can you give us an example of the data you have and the results you want.

This is my table
name ________id_________quantity
car____________1__________5

The query should do this
name ________id
car____________1
car____________1
car____________1
car____________1
car____________1

 

[moke123]

What's your ultimate goal? Based on your example above, do you want 5 records in a table?

 

[cheberdy]

What's your ultimate goal? Based on your example above, do you want 5 records in a table?

quantity is a number and it gives the number of records

 

[theDBguy]

Here's an example query using a Tally table.

SELECT [name], id
FROM TableName, TallyTable
WHERE TallyTable.ID<=TableName.quantity

(untested)
Hope that helps...

Edit: Fixed SQL. Thanks to @CJ_London for spotting the error.

 

[moke123]

quantity is a number and it gives the number of records

That I understand. What I meant is what is the purpose of the query?

 

[CJ_London]

@theDBguy - think you meant

SELECT [name], id
FROM TableName, TallyTable
WHERE TallyTable.ID<=TableName.quantity

The tally table ID's need to run consecutively from 1 to at least whatever number is big enough for the maximum quantity in your quantity field

 

[cheberdy]

@theDBguy - think you meant

SELECT [name], id
FROM TableName, TallyTable
WHERE TallyTable.ID<=TableName.quantity

The tally table ID's need to run consecutively from 1 to at least whatever number is big enough for the maximum quantity in your quantity field

How do I create this tally tabel

 

[cheberdy]

That I understand. What I meant is what is the purpose of the query?

It is for creating labels

 

[cheberdy]

Here's an example query using a Tally table.

SELECT [name], id
FROM TableName, TallyTable
WHERE TallyTable.ID<=TableName.id

(untested)
Hope that helps...

How do I create a tally table in access

 

[CJ_London]

How do I create this tally tabel

see post #9

it is just a table with one column

 

[cheberdy]

see post #9

it is just a table with one column

Is it possible to do the same with a loop

 

[Pat Hartman]

Here's a working example that might help.

 

[cheberdy]

Maybe something like
Declare i as Integer
Select mytable.Quantity
from mytable
while mytable.Quantity < i
Begin
Select mytable.Id, mytable.name from mytable
Set i = i+1
End

 

[theDBguy]

@theDBguy - think you meant

SELECT [name], id
FROM TableName, TallyTable
WHERE TallyTable.ID<=TableName.quantity

The tally table ID's need to run consecutively from 1 to at least whatever number is big enough for the maximum quantity in your quantity field

Haha, good thing I said "(untested)."

Thanks for the assist!

 

[Gasman]

Maybe something like
Declare i as Integer
Select mytable.Quantity
from mytable
while mytable.Quantity < i
Begin
Select mytable.Id, mytable.name from mytable
Set i = i+1
End

But you never set what i is???
Why not go with the simple solution the experts supplied?

 

[CJ_London]

@cheberdy - are you using sql server as a backend?

 

[moke123]

Another less efficient method would be using a temp table.