If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert Range Field To Unique Records
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 |
#2
|
|||
|
|||
Convert Range Field To Unique Records
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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rudy" wrote in message ps.com... 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 |
Thread Tools | |
Display Modes | |
|
|