create serial numbers

[CJA]

Hello!

I have this table:

cod | desc | qt |
1 art 1 2
2 art 2 3

based on the last table i want to create one like this:

serial | cod | desc |
000001 1 art1
000002 1 art1
000003 2 art2
000004 2 art2
000005 2 art2

in words if i buy 2 monitors (cod=1, desc=art1), i want to create 2 records
in another table wit the same "cod" and "desc" but diferent serial numbers.

how can i do this?

Thanks for helping!

 

[ldiaz]

Hi CJA..

I hope this help you...
[Serial] is your field where you want to create your consecutive number.
[yourtable] is the source where the datas are stored..

[serial] = "00-" & Right(DMax("serial", "yourtable"), Len(DMax("serial",
"yourtable")) - InStr(1, DMax("serial", "yourtable"), "-")) + 1

any question please e-mail me..

at: (e-mail address removed)

ldiaz
thanks..

 

[CJA]

I´m sorry! i dont understand how can i use this and maybe i don't explain
correctly.
Imagine a form with the "table01", i want to create a button to append the
information in "table02".
The serial number is not the problem, because this field is an autonumber in
the "table02", the problem is how can i create records in "table02" based on
field "quant" in "table01".
If i have in the "table01" a record with quant=3 i want to append in
"table02" three records. If the next record in "table01" have quant=4 then i
want to create 4 records in "table02".

Thanks for helping !!

Hi CJA..

I hope this help you...
[Serial] is your field where you want to create your consecutive number.
[yourtable] is the source where the datas are stored..

[serial] = "00-" & Right(DMax("serial", "yourtable"), Len(DMax("serial",
"yourtable")) - InStr(1, DMax("serial", "yourtable"), "-")) + 1

any question please e-mail me..

at: (e-mail address removed)

ldiaz
thanks..

Hello!

I have this table:

cod | desc | qt |
1 art 1 2
2 art 2 3

based on the last table i want to create one like this:

serial | cod | desc |
000001 1 art1
000002 1 art1
000003 2 art2
000004 2 art2
000005 2 art2

in words if i buy 2 monitors (cod=1, desc=art1), i want to create 2
records
in another table wit the same "cod" and "desc" but diferent serial
numbers.

how can i do this?

Thanks for helping!

 

[John Vinson]

Hello!

I have this table:

cod | desc | qt |
1 art 1 2
2 art 2 3

based on the last table i want to create one like this:

serial | cod | desc |
000001 1 art1
000002 1 art1
000003 2 art2
000004 2 art2
000005 2 art2

in words if i buy 2 monitors (cod=1, desc=art1), i want to create 2 records
in another table wit the same "cod" and "desc" but diferent serial numbers.

how can i do this?

Thanks for helping!

I really question whether there's a good reason to do this, but... if
you really want to do so... consider NOT copying the DESC field
redundantly. You don't need it; you can always join your new
individual-items table to your current table to pick up the
description.

Create a table (I call mine Num) with one Integer field N; fill it
with values from 1 through the largest value of qt you're ever likely
to need. Be generous (this table is useful for other things, I usually
put in 10,000 records).

Run an Append query:

INSERT INTO newtable
SELECT [N], [cod]
FROM yourtable, Num
WHERE Num.N <= yourtable.qt;


John W. Vinson[MVP]