Convert columns to rows: create duplicate rows based on column val

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with two columns, one with a characteristic and the other with
a count. I want to create a new table with just one column: a list of the
characteristic repeated for as many times as the count value.

For example, go from this:
BLUE 3
RED 1
GREEN 2

To this:
BLUE
BLUE
BLUE
RED
GREEN
GREEN

I need to this so I can easily randomly select some rows from this list
instead of creating weighting factors based on the original counts.

I am NOT familiar with SQL. I was hoping I could just do this with a query.

Any help is much appreciated!
 
I can't think of a way to use SQL. However, you can use a little VBA code.
Put the following code into a module:
'-----code begin---------
Sub DuplicateRows()
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rsSource = db.OpenRecordset("table1")
Set rsTarget = db.OpenRecordset("table2")

Do While Not rsSource.EOF
For i = 1 To rsSource![Field2]
rsTarget.AddNew
rsTarget!Field1 = rsSource!Field1
rsTarget.Update
Next i
rsSource.MoveNext
Loop

End Sub
'------code end--------

Replace your actual table and field names for Table1, Table2, Field1, and
Field2. You can call this subroutine from code any where in your
application or just put the cursor in the module some where and click the
Run button.

Note: If using Access 2000 or Access XP, this requires a reference to
Microsoft DAO 3.6 Object Model. To set a Reference to DAO, go to any code
window. Choose Tools > References and scroll down the list until you find
"Microsoft DAO 3.6 Object Model". Put a checkmark next to that and close
the window.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Actually, there is a way in SQL, but it involves a little trickery... <g>

Assume that the existing table (for the sake of argument, call it Table1)
has two fields Characteristic and OccurrenceCount.

Create a second table (Table2) that contains a single numeric column
(Counter). Populate Table2 with as many rows as you like (provided it
exceeds the maximum number of occurrences for the characteristic). Have
Counter = 1 on the first row, 2 on the second row, and so on up to n on the
nth row.

Create a query that joins the two tables (on Table1.OccurrenceCount to
Table2.Counter) and returns the Characteristic field. Running that query
will return one row for each row in Table1. However, go into the SQL view
(View | SQL View from the menu bar). The SQL should look like:

SELECT Table1.Characteristic
FROM Table1 INNER JOIN Table2
ON Table1.OccurrenceCount = Table2.Counter;

Change the = to >= and rerun the query. You'll get exactly what you're
looking for. (Note: You will not be able to go back to Design view once you
make the change to the SQL, as Access has no way of illustrating what you've
just done)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Roger Carlson said:
I can't think of a way to use SQL. However, you can use a little VBA code.
Put the following code into a module:
'-----code begin---------
Sub DuplicateRows()
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rsSource = db.OpenRecordset("table1")
Set rsTarget = db.OpenRecordset("table2")

Do While Not rsSource.EOF
For i = 1 To rsSource![Field2]
rsTarget.AddNew
rsTarget!Field1 = rsSource!Field1
rsTarget.Update
Next i
rsSource.MoveNext
Loop

End Sub
'------code end--------

Replace your actual table and field names for Table1, Table2, Field1, and
Field2. You can call this subroutine from code any where in your
application or just put the cursor in the module some where and click the
Run button.

Note: If using Access 2000 or Access XP, this requires a reference to
Microsoft DAO 3.6 Object Model. To set a Reference to DAO, go to any
code
window. Choose Tools > References and scroll down the list until you
find
"Microsoft DAO 3.6 Object Model". Put a checkmark next to that and close
the window.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



CarrieR said:
I have a table with two columns, one with a characteristic and the other with
a count. I want to create a new table with just one column: a list of
the
characteristic repeated for as many times as the count value.

For example, go from this:
BLUE 3
RED 1
GREEN 2

To this:
BLUE
BLUE
BLUE
RED
GREEN
GREEN

I need to this so I can easily randomly select some rows from this list
instead of creating weighting factors based on the original counts.

I am NOT familiar with SQL. I was hoping I could just do this with a query.

Any help is much appreciated!
Click to expand...
Click to expand...
 
It worked! Thank you very much. I gotta learn more SQL... once I finish
this dissertation :)

Roger Carlson said:
I can't think of a way to use SQL. However, you can use a little VBA code.
Put the following code into a module:
'-----code begin---------
Sub DuplicateRows()
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rsSource = db.OpenRecordset("table1")
Set rsTarget = db.OpenRecordset("table2")

Do While Not rsSource.EOF
For i = 1 To rsSource![Field2]
rsTarget.AddNew
rsTarget!Field1 = rsSource!Field1
rsTarget.Update
Next i
rsSource.MoveNext
Loop

End Sub
'------code end--------

Replace your actual table and field names for Table1, Table2, Field1, and
Field2. You can call this subroutine from code any where in your
application or just put the cursor in the module some where and click the
Run button.

Note: If using Access 2000 or Access XP, this requires a reference to
Microsoft DAO 3.6 Object Model. To set a Reference to DAO, go to any code
window. Choose Tools > References and scroll down the list until you find
"Microsoft DAO 3.6 Object Model". Put a checkmark next to that and close
the window.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



CarrieR said:
I have a table with two columns, one with a characteristic and the other with
a count. I want to create a new table with just one column: a list of the
characteristic repeated for as many times as the count value.

For example, go from this:
BLUE 3
RED 1
GREEN 2

To this:
BLUE
BLUE
BLUE
RED
GREEN
GREEN

I need to this so I can easily randomly select some rows from this list
instead of creating weighting factors based on the original counts.

I am NOT familiar with SQL. I was hoping I could just do this with a query.

Any help is much appreciated!
Click to expand...
Click to expand...
 
Back
Top