creating new records from 1 field

  • Thread starter Thread starter Geoff Wilson
  • Start date Start date
G

Geoff Wilson

This may be a simple one but im a bit lost with it .
Basically I am trying to create 10 new records if a record
has a value of 10, 20 if it has 20 and so on. all other
field values should stay the same eg the output area that
has a percentage of 10 will be split into ten individual
records for the same output area.(Output area is a field
name) any ideas??
 
Easiest to do this with a loop in code.

This example adds 10 records to Table1, where the field MyNumber has the
values 1 to 10, and the field Output Area has the same value as that in the
form:

Dim lng As Long
Dim rs As DAO.Recordset

Set rs = dbEngine(0)(0).OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)
For lng = 1 to 10
rs.AddNew
![MyNumber] = lng
![Output Area] = Me.[OutputArea]
'etc for other fields.
rs.Update
Next
rs.Close
Set rs = Nothing
 
This may be a simple one but im a bit lost with it .
Basically I am trying to create 10 new records if a record
has a value of 10, 20 if it has 20 and so on. all other
field values should stay the same eg the output area that
has a percentage of 10 will be split into ten individual
records for the same output area.(Output area is a field
name) any ideas??
Click to expand...

Hi Geoff,

In addition to Allen's sage advice,
another method uses a simple "number table"
("Iotas") in a Cartesian join with your
original table (in below example will call
it "tblCopy"), then applying a filter
to return n records for [OutputArea]=n.

{all of below was previously demonstrated
by Michel}

*************************
Step 1: Making the table Iotas:

Make a table Ds with field d, 10 records,
values for d = 0 to 9.

In a make table query,
bring Ds table 3 times so you have
Ds
Ds_1
Ds_2

In a free column type:

Iota: Ds.d + 10*Ds_1.d + 100*Ds_2

Generate the table Iotas and make Iota a primary key.

**********************************************
Step 2: Query to return n records if [OutputArea]=n:

So you have a table tblCopy with number field [OutputArea].

Start with a simple example to see how this works:

If tblCopy has 3 records and you bring both
tblCopy and Iotas into a SELECT query

SELECT Iotas.Iota, tblCopy.[OutputArea] From Iotas, tblCopy;

for every Iota, you'll get 3 records, i.e., # of records in tblCopy:
(If there were n records in tblCopy,
for every Iota, you'd get n records.)

Iota [OutputArea]
0 x
0 x
0 x
1 x
1 x
1 x
2 x
2 x
2 x
....
Iota = 3 thru 998 repeats pattern
....
999 x
999 x
999 x

Suppose values for [OutputArea] in tblCopy were:

[OutputArea]
Record1 1
Record2 2
Record3 3

Our full select would give us:

Iota [OutputArea]
0 1
0 2
0 3
1 1
1 2
1 3
2 1
2 2
2 3
....
Iota = 3 thru 998 repeats pattern
....
999 1
999 2
999 3

Now what if we filtered for [OutputArea] > Iota
(strip away every record above where [OutputArea] > Iota)

SELECT Iotas.Iota, tblCopy.[OutputArea]
From Iotas, tblCopy
WHERE (tblCopy.[OutputArea] > Iotas.Iota)


Iota [OutputArea]
0 1
0 2
0 3
1 2
1 3
2 3

or sorting by [OutputArea]:

Iota [OutputArea]
0 1
0 2
1 2
0 3
1 3
2 3

In other words, the above filter will give:

if [OutputArea]= # of copies of this record
1 1
2 2
3 3


So...to get n records if [OutputArea]=n
and include all other fields:

SELECT
Iotas.Iota,
tblCopy.[OutputArea],
tblCopy.Field2,
tblCopy.Field3,
tblCopy.Field4,
.....
tblCopy.Fieldn
From Iotas, tblCopy
WHERE (tblCopy.[OutputArea] > Iotas.Iota);

{replace "tblCopy" and "Fieldx"
with actual table name and actual
field names}

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Geoff, Gary's idea of the Cartesian product is a really good idea where you
need to do this regularly. You can use the Criteria in the query to limit it
to the desired number of records.

There is another example of this technique, including the code to populate
your counting table in this link:
http://allenbrowne.com/ser-39.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Gary Walter said:
This may be a simple one but im a bit lost with it .
Basically I am trying to create 10 new records if a record
has a value of 10, 20 if it has 20 and so on. all other
field values should stay the same eg the output area that
has a percentage of 10 will be split into ten individual
records for the same output area.(Output area is a field
name) any ideas??
Click to expand...

Hi Geoff,

In addition to Allen's sage advice,
another method uses a simple "number table"
("Iotas") in a Cartesian join with your
original table (in below example will call
it "tblCopy"), then applying a filter
to return n records for [OutputArea]=n.

{all of below was previously demonstrated
by Michel}

*************************
Step 1: Making the table Iotas:

Make a table Ds with field d, 10 records,
values for d = 0 to 9.

In a make table query,
bring Ds table 3 times so you have
Ds
Ds_1
Ds_2

In a free column type:

Iota: Ds.d + 10*Ds_1.d + 100*Ds_2

Generate the table Iotas and make Iota a primary key.

**********************************************
Step 2: Query to return n records if [OutputArea]=n:

So you have a table tblCopy with number field [OutputArea].

Start with a simple example to see how this works:

If tblCopy has 3 records and you bring both
tblCopy and Iotas into a SELECT query

SELECT Iotas.Iota, tblCopy.[OutputArea] From Iotas, tblCopy;

for every Iota, you'll get 3 records, i.e., # of records in tblCopy:
(If there were n records in tblCopy,
for every Iota, you'd get n records.)

Iota [OutputArea]
0 x
0 x
0 x
1 x
1 x
1 x
2 x
2 x
2 x
...
Iota = 3 thru 998 repeats pattern
...
999 x
999 x
999 x

Suppose values for [OutputArea] in tblCopy were:

[OutputArea]
Record1 1
Record2 2
Record3 3

Our full select would give us:

Iota [OutputArea]
0 1
0 2
0 3
1 1
1 2
1 3
2 1
2 2
2 3
...
Iota = 3 thru 998 repeats pattern
...
999 1
999 2
999 3

Now what if we filtered for [OutputArea] > Iota
(strip away every record above where [OutputArea] > Iota)

SELECT Iotas.Iota, tblCopy.[OutputArea]
From Iotas, tblCopy
WHERE (tblCopy.[OutputArea] > Iotas.Iota)


Iota [OutputArea]
0 1
0 2
0 3
1 2
1 3
2 3

or sorting by [OutputArea]:

Iota [OutputArea]
0 1
0 2
1 2
0 3
1 3
2 3

In other words, the above filter will give:

if [OutputArea]= # of copies of this record
1 1
2 2
3 3


So...to get n records if [OutputArea]=n
and include all other fields:

SELECT
Iotas.Iota,
tblCopy.[OutputArea],
tblCopy.Field2,
tblCopy.Field3,
tblCopy.Field4,
....
tblCopy.Fieldn
From Iotas, tblCopy
WHERE (tblCopy.[OutputArea] > Iotas.Iota);

{replace "tblCopy" and "Fieldx"
with actual table name and actual
field names}

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
 
Back
Top