0
myTable
+-----------+-----------------------+
|  Field1   |        Field2         |
+-----------+-----------------------+
|    AAA    | Value1, Value2,Value3 |
+-----------+-----------------------+
|  BBB; CCC | Value4, Value5        |
+-----------+-----------------------+
|    DDD    | Value6                |
+-----------+-----------------------+


Select Field1, Split(Field2, ",") As SplitField2
From myTable

+-----------+-------------+
|  Field1   | SplitField2 |
+-----------+-------------+
|    AAA    |   Value1    |
+-----------+-------------+
|    AAA    |   Value2    |
+-----------+-------------+
|    AAA    |   Value3    |
+-----------+-------------+
|  BBB; CCC |   Value4    |
+-----------+-------------|
|  BBB; CCC |   Value5    |
+-----------+-------------+
|    DDD    |   Value6    |
+-----------+-------------+

Note that, the function 'Split' is what I want to program. But so far, I haven't got any idea how to immplement it.

I'm inspired by the post: Split Field Into Multiple Records in Access DB . It has given a useful way to create new records. Under the guidance of this post, I have written a similar function called SplitField. With the help of data macros in MS Access, the function SplitField can keep a new table named myNewTable having the same records as myTable (Of course, With Field2 being split). But in this way, I have to create new tables, write a new data macro to pass table name, field name, current record and etc to the function SplitField.

Is it able to program a function named "Split" and use it in the way shown at the beginning of this post? With this function, users may split field more efficiently. Users can even split more than one field at the same time, for instance.

Select Split(Field1, ";") As SplitField1, Split(Field2, ",") As SplitField2
From myTable

+---------------+-------------+
|  SplitField1  | SplitField2 |
+---------------+-------------+
|      AAA      |   Value1    |
+---------------+-------------+
|      AAA      |   Value2    |
+---------------+-------------+
|      AAA      |   Value3    |
+---------------+-------------+
|      BBB      |   Value4    |
+---------------+-------------+
|      BBB      |   Value5    |
+---------------+-------------+
|      CCC      |   Value4    |
+---------------+-------------+
|      CCC      |   Value5    |
+---------------+-------------+
|      DDD      |   Value6    |
+---------------+-------------+
June7
  • 19,874
  • 8
  • 24
  • 34
Frank Lin
  • 1
  • 1
  • Why are you using "M$" to refer to Microsoft? Is it really the late-1990s Slashdot here? – Dai Nov 04 '21 at 01:42
  • OK. I have corrected it. – Frank Lin Nov 04 '21 at 01:52
  • Assuming that the names and number of values packed into your cells are not always the same (i.e. _always_ 2 or 3 values and _always_ named "AAA" and "BBB") then **no**: it is not possible in Access using SQL alone. Access's horribly outdated SQL dialect (which is an incomplete implementation of SQL-92 that has not been updated *at all* since Access 97). You will need to use VBA to extract values and then re-insert them into a new `TABLE`. – Dai Nov 04 '21 at 01:56
  • 2
    The real solution: Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). – sticky bit Nov 04 '21 at 01:58
  • I didn’t expect it to be updated for 20 years. That's the way I am using it. – Frank Lin Nov 04 '21 at 02:01
  • @FrankLin Why is that a good reason? You’re missing out on so many useful features like CTEs, windowing functions, and much more. If you don’t need a GUI I strongly recommend SQLite. – Dai Nov 04 '21 at 05:17
  • It is not necessary to create a new table each time process is run. Records can be saved to an existing table. With a multi-user split database, this table should sit in the frontend. – June7 Nov 04 '21 at 06:44

0 Answers0