Splitting field data

[Srin]

I have a column with Customer orders. They are listed next to each other like this:

Customer	CustomerOrder
Jani	Crest (Toothbrush) / Softace / XXXX etc.

I want to be able to do something like this:

Customer	CustomerOrder
Jani	Crest (Toothbrush)
Jani	Softace
Jani	XXXX

I have come across codes using delimiters to do this. Since I am new to Access I am not able to write one on my own or figure them out clearly. Please help me out by giving me pointers on how to do the above either using Queries or pointers to write codes or a sample code based on the above example if possible.

Thank you.

[June7]

This is not a normalized data structure. Splitting the composite data and listing as separate records will not be simple. I assume the composite data will not always have the same number of elements. Will probably involve VBA and writing records to another table.

[Srin]

Thanks for your reply @June7

It is not normalized and that is what I am trying to achieve with this imported table generated from a data dump. I want to be able to normalize this for database use by doing the above.

[June7]

Was I correct in my assumption?

The composite data is multiple products ordered?

Is this a one-time effort or recurring process?

Do you know VBA programming? How to open and manipulate recordsets and arrays with looping structure?

[Srin]

I am not sure if this is going to be a one time effort. Although other data dumps might be generated, this is the only time I will be implementing this.

I have worked on the VBA code builder forms, for small manipulations before. But I do not know how to work on tables or have not yet done it till now.

Although I can confidently say the number of CustomerOrders will not increase 3. For the near future.

[June7]

If the composite data is ALWAYS 3 elements separated by / character, this could probably be done entirely with queries. But if there is variation in the string structure, will need VBA. Like:

Sub SplitProduct()
Dim rs As DAO.Recordset, ary As Variant, x As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Orders;", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
While Not rs.EOF
    ary = Split(rs!CustomerOrder, "/")
    For x = 0 To UBound(ary)
        CurrentDb.Execute "INSERT INTO OrdersNew(OrderID, Customer, Product) VALUES(" & rs!OrderID & ", '" & rs!Customer & "', '" & Trim(ary(x)) & "')"
    Next
    rs.MoveNext
Wend
End Sub

I am assuming each order has unique Order identifier. This will be important if there can be multiple records for same customer.

[Srin]

Thanks for the quick reply @June7.

Each Order has an OrderID. Will that work?

I didnt get the line "INSERT INTO OrdersNew", does it mean creating a new order, since there is no new field called Orders.

[June7]

OrderID is exactly what I expected.

OrdersNew is a table you have to build to receive the new records.

I don't know names of your tables and fields. Substitute where appropriate.

BTW, should really save a CustomerID instead of names. But I don't know enough about your data structure to advise how to do that.

[Srin]

Should I create the respective table and field before using the code?

[June7]

If you don't, what do you think will happen? Try it and find out.

[Srin]

I mean the the OrdersNew table. Okay I will try it out. Thanks again @June7

It says "Too few parameters . Expected" runtime error 3061

[June7]

I assumed you meant OrdersNew. Believe me, you can learn a lot from hitting errors. Learn to debug. Refer to link at bottom of my post.

[Srin]

I put in break points and it kept executing inside the for loop. But once I stop it and run normally without breakpoints, it gives me the error for runtime "Too few parameters." on this line:

CurrentDb.Execute "INSERT INTO OrdersNew(OrderID, Customer, Product) VALUES(" & rs!ProductID & ", '" & rs!Customer & "', '" & Trim(ary(x)) & "')"

[June7]

What data types are OrderID, Customer, Product? My code expects OrderID to be number and the other 2 text.

Why are you populating OrderID with rs!ProductID?

[Srin]

OrderID is Short Text since some of the IDs are new and have not been assigned IDs. They have "NEW". Customers and Orders ar also Short Text.