Click here to Skip to main content
15,921,716 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have 2 tables MyTbl1, MyTbl2 contains the followings And I wish to have a Select Query
to get the Output like the table MySplittedTbl2

Select * from MyTbl2 ??????????

order_no, item_name, order_qty
A001       Paper      1000
A002       Pen        2000
A003       Pencil     2500
A004       Ink        1500

bill_no   order_no     billing_qty
001       A001         1000
002       A001,A002    1000,2000
003       A002         1500
004       A002,A003    500,2000

From the above MyTbl2 I wish to split order_no column by ,(comma) and duplicated as another row , is it possible?

bill_no   order_no     billing_qty
001       A001         1000
002       A001         1000
002       A002         2000
003       A002         1500
004       A002          500
004       A003         2000

Thank For The Gudences
Thank Regards

What I have tried:

I tried to split the Table field value by comma(,) and need to make it as new row
Updated 7-Apr-21 6:16am
RedDk 7-Apr-21 14:09pm    
I see a lot of typos here. Which makes answering deadly.
CREATE TABLE [dbo].[MySplittedTbl2](
[bill_no] NVARCHAR(42),
[order_no] NVARCHAR(43),
[billing_qty] NVARCHAR(44)

CREATE TABLE [dbo].[MyTbl2](
[bill_no] NVARCHAR(42),
[order_no] NVARCHAR(43),
[billing_qty] NVARCHAR(44)

Why does this "MyCodes" query appear so queer?

SELECT * FROM [PARAMU1973].[dbo].[MyTbl2]

Seems like it should work if you ask me.
CHill60 9-Apr-21 4:13am    
This looks like T-SQL. OP is using MS-Access.

1 solution

In theory it is possible - try searching using your favourite search engine with
MS Access Split string into multiple records
You will need to write some VBA to do this.

However, that would not be a good solution. A better solution is for me to point out the error of your design.

Storing values in comma-delimited columns for this purpose is Bad Design.

1. You come across the problem you currently have when you need to split the data back out again
2. You limit the number of items you can have associated with each bill by the size of the column you choose.

A far better design is to have a linking table that links order number to bill number. In fact that table would look very similar to your expected results - without the billing quantity

Have a look at some of the examples at[^] - e.g. Customers and Invoices Data Model[^] and see how Invoice, Invoice_Line_Items and Products are related
Share this answer
Maciej Los 8-Apr-21 15:03pm    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900