sequence for tickets

[Guest]

good afternoon,

I have following problem.
I would like to number my tickets from 1 to n in order to get the sequenceNo
for each ticket.

my input table is tbl1,
fields> itemNO;NoOfTickets;flag

my output table is tbl2
fields> itemNO;NoOfTickets;sequence

then I have a command in a form which break down my tbl1.Nooftickets
into single lines in tbl2.
my code is as follows>
Private Sub Command0_Click()
Dim qty As Integer
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from tbl2"
DoCmd.RunSQL "UPDATE Tbl1 SET Tbl1.Flag = 1;"
Do Until DCount("[itemNo]", "q1") = 0

Debug.Print check
qty = DLookup("[NoOfTickets]", "Q1")
i = 0
Do Until i = qty
i = i + 1
DoCmd.OpenQuery "q2"
Loop

DoCmd.OpenQuery "Q3"
Loop
End Sub
///////////////////////////////////////
result of this code is>
tbl1;
item1;5;1
,,,,,,,,,,,,,,
TBL2;
item1;5
item1;5
item1;5
item1;5
item1;5

what I do not know to write in code or a query,
which will feed sequence from 1 to 5

many thanks,
Stefan.

 

[Duane Hookom]

Don't use code. Create a table of number (tblNums) with a single, numeric
field (Num) and values from 1 to a maximum number. Then create a query

SELECT tbl1.itemNO, tbl1.NoOfTickets,
tblNums.Num AS Sequence
FROM tbl1, tblNums
WHERE (((tblNums.Num)<=[NoOfTickets]))
ORDER BY tbl1.itemNO, tblNums.Num;

You can convert this to a make table or append query to get your records
into tbl2.

 

[Guest]

Thanks Duane!!!

so simple - so smart!

Cheers,
Stefan.