Convert Range Field To Unique Records

[Rudy]

Please Help -

I have a database that contains a "To" and a "From" column for each
record. However, I am trying to expand the file into unique records
for each "To" From" combination. The following is an example of data:

This is what I have:
UserID FromCostCenter ToCostCenter
A 0001 0005
B 1006 1009

This is what I'd like to have:
UserID CostCenter
A 0001
A 0002
A 0003
A 0004
A 0005
B 1006
B 1007
B 1008
B 1009

I started by converting the fields to numeric values and subtracting
the From value from the To value to understand the number of cost
centers that either need to be incremented from the minimum value
(minus 1 for the existing maximum). I imagine I need to write a
statement that takes the user ID and somehow uses the increment field
but I do not know how to programatically do this.

If you can help that would be great.

Thanks - Rudy

 

[Allen Browne]

2 approaches.

1. Write some code to OpenRecordset on the source table and the target
table. Loop through the source records. For each one, loop through the
FromCostCenter ToCostCenter values, creating a record for each one.

The DAO code will use AddNew, assigning each field, followed up Update to
save the record.

2. Create a table with one field named CountID, of type Number. Mark it as
primary key. Save the table as tblCount. Enter values from 1 to the highest
number you have (perhaps 9999.)

Create a query using both your original table and tblCount. There must be no
line joining the 2 tables in the upper pane of query design.

Add CountID to the grid. If FromCostCenter is a Text field (not a Number
field), Change the field row from:
CountID
to:
Format([CountID], "0000")
Add this Criteria under it:
Between [FromCostCenter] And [ToCostCenter]

You can now convert this query to an Append query (Append on query menu) to
add the records to the target table.

The function below will programmatically add records to tblCount instead of
typing them by hand. For example, you can add 9999 records by opening the
Immediate Window (Ctrl+G) and entering:
? MakeData(9999)

Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount", dbOpenDynaset)
For lng = 1 To HowMany
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

The function is also an example of how to add records programatically
(method 1 above.)