Copy columns to another table and separate records with commas (1 Viewer)

[hllary]

I have an access table that contains data similar to the first table below. (There is more but I don't need it for this part)

MasterNum	FIG	ITEM	Additional Fig	Additional Item
88	BULK	24	130, 155, 157	22, 6
89	4-2		25, 212	11, 14

I'm trying to come up with a way to copy the information from this table and append it in another table. But the additional Fig and Additional Item data has been moved then separated into the Fig and Item. The MasterNum will link them to record in original table.

Also, if a FIG or ITEM is blank then 9999 is add to the record. And sometimes there is an Additional FIG that does not have a matching Additional Item (for example, for MasterNum 88 there are there Additional Fig but only two Additional Item. The Additional Item associated with 157 will be 9999 since it does not exist.)

Please let me know if I'm making any sense.

MasterNum​	Fig​	Item​
88	BULK​	24​
88	130​	22​
88	155​	6​
88	157​	9999​
89	212​	14​
89	4-2​	9999​
89	25​	11​
89	212​	14​

I have only started thinking about doing so I do not have any code to share.

Does someone know of some code I can modify to accomplish this task or point me in the direction of some info on how to do this.
Thanks

 

[theDBguy]

Hi. Looks like you'll definitely need a code to do that. I think one function that will be useful for you is the Split() function. You can use it to create an array for each column and use the index to match a fig with an item. Cheers!

 

[CJ_London]

if more parameters are known, such as is there a maximum number of additional fig/items? can there be more additional items than figs? can there be records with no additional items or figs? that might enable your to simplify your code.

I agree with DBG - you'll find the split function useful.

other thing to consider - is this a one off exercise or one that will be applied on a regular basis

 

[hllary]

if more parameters are known, such as is there a maximum number of additional fig/items? can there be more additional items than figs? can there be records with no additional items or figs? that might enable your to simplify your code.

I agree with DBG - you'll find the split function useful.

other thing to consider - is this a one off exercise or one that will be applied on a regular basis

There is not a maximum number of additional fig/items and there can be records with no additional figs/items. Also, sometime there might be an additional item but no additional fig. (those records are erroneous and will be QCed later but they still need to be shown)

This might be applied on a regular basis at least more than once. Since the total number of records in the first table is <5k, I was going to add a delete all records in table before the function. Then run it anytime new data is added.

 

[CJ_London]

ok, in that case I think you are going to have to run this entirely from a vba function. As a framework to get you started

1. open a recordset based on your table - this may need criteria to limit to records not previously posted - perhaps based on masterNum not being in your new table

2. loop through the recordset doing the following for each loop

• create and execute a sql string for FIG and ITEM fields to insert into your new table - for Item use nz(Item,9999) - that's the easy bit
• create two arrays based on additional Fig and additions items (use the split function)
• compare size of each array
• for each element of the smaller array (or the figs if the same) create and execute a sql string for FIG and ITEM fields to insert into your new table - for Item use nz(Item,9999)
• for the remaining items in the larger array create and execute a sql string for FIG and ITEM fields to insert into your new table

3. close the recordset

 

[Pat Hartman]

Sometimes you have to do stuff like this to interface with an existing system but you should never be doing this to store the data for your own use. Any time you want the columns mushed, use a query that calls the concatenate function. That way, your tables remain properly normalized and you can still have the data view you want.

 

[CJ_London]

totally agree - my understanding is the OP has imported the data and wants to normalise it