MakeTable Query (1 Viewer)

[kirkm]

My query has 2 fields. Field2 is a vblf demlimited list.
I would like to make a table where Field1 stays Field1 but the Field2 list becomes a separate field for each item.
Before playing around with a UDF I thought I'd ask here as there may be some existing "Access" way to do this ?

 

[MajP]

Field1 but the Field2 list becomes a separate field for each item

That is probably no better. If field 1 is an ID then you should push the list into a seperate table
In table 1 if you have
1 Cat;Dog;Mouse
2Bird; Fish

Table 2 should be
SomeForeignKey  ListItem
1               cat
1               dog
1               mouse
2               Bird
2               Fish

 

[theDBguy]

No existing Access way to do it, but there may be an existing Excel way to do it.

If you follow @MajP suggestion, you would be able to use a Crosstab query in Access to display the data horizontally in separate columns.

 

[kirkm]

Sure, I'll try that. You don't have an example of a crosstab query? I've never seen one.
For table2 I can use Field1 as the foreign key.
Thanks both

 

[MajP]

Here is a demo
Table OriginalTable
OriginalTable OriginalTable

ParentID	ParentDescription	ListItems
1​	Pets	Cat;Dog;Fish;Bird
2​	Reptiles	Frog;snake;lizard
3​	Birds	Eagle;stork;buzzard

Empty new table
NewChildTable NewChildTable

listItemID	ListItem	ParentID_FK
17​		0​

Run this code

Public Sub SplitList()
  Dim rs As DAO.Recordset
  Dim i As Integer
  Dim arList() As String
  Dim strSql As String
 
  Set rs = CurrentDb.OpenRecordset("originalTable")
  Do While Not rs.EOF
     arList = Split(rs!listitems, ";")
       For i = 0 To UBound(arList)
        strSql = "INSERT INTO NewChildTable (ParentID_FK,ListItem) Values (" & rs!parentID & ", '" & arList(i) & "')"
        CurrentDb.Execute strSql
      Next i
    rs.MoveNext
  Loop
End Sub

Now looks like

NewChildTable NewChildTable

listItemID	ListItem	ParentID_FK
18​	Cat	1​
19​	Dog	1​
20​	Fish	1​
21​	Bird	1​
22​	Frog	2​
23​	snake	2​
24​	lizard	2​
25​	Eagle	3​
26​	stork	3​
27​	buzzard	3​

Use in a query with the original table
qryParentChild qryParentChild

ParentID	ParentDescription	ListItem
1​	Pets	Cat
1​	Pets	Dog
1​	Pets	Fish
1​	Pets	Bird
2​	Reptiles	Frog
2​	Reptiles	snake
2​	Reptiles	lizard
3​	Birds	Eagle
3​	Birds	stork
3​	Birds	buzzard

 

[kirkm]

Thanks for the demo, it was helpful to see that method. But I wasn't able (at least yet) to apply it to the job I had. What I was after was for all the 'list items to have their own field, but horizontally not vertically.
It may not be what a cross tab query does.. I couldn't prove it one way or another as the wizard gave an error about
The table or query you selected doesn't have enough fields to provide data for a crosstab query. Select a table or query with at least 3 numeric, date or text fields.
There were only 2 fields (I didn't have an ID field) so that was that. Tried Googling but only very complex (thus useless) examples found. I got it done though, but by connecting to Excel and using a worksheet.

 

[theDBguy]

Thanks for the demo, it was helpful to see that method. But I wasn't able (at least yet) to apply it to the job I had. What I was after was for all the 'list items to have their own field, but horizontally not vertically.
It may not be what a cross tab query does.. I couldn't prove it one way or another as the wizard gave an error about
The table or query you selected doesn't have enough fields to provide data for a crosstab query. Select a table or query with at least 3 numeric, date or text fields.
There were only 2 fields (I didn't have an ID field) so that was that. Tried Googling but only very complex (thus useless) examples found. I got it done though, but by connecting to Excel and using a worksheet.

Hi. A Crosstab query requires three pieces of information: one for the row header, one for the column header, and one for the value. However, that doesn't mean you need three fields for the Crosstab query. You can use the same field in all three or maybe in two of them (if you only have two fields to use).

 

[kirkm]

The column header is the field name, yes ? What's a row header ?

 

[theDBguy]

The column header is the field name, yes ? What's a row header ?

When you create a Crosstab Query, you need to designate a row heading, column heading, and a value.

 

[MajP]

If your max list items is small you can do this with a function. This is pretty inefficient but simple to do

Public Function GetListItem(VarList As Variant, ItemNumber As Integer, Optional Delimiter As String = ";") As String
  Dim arList() As String
  Dim i As Integer
 
  'Pass in a 1 based item number then convert to 0 based
  ItemNumber = Abs(ItemNumber - 1)
  If Not (VarList & "" = "") Then
    arList = Split(VarList, Delimiter)
    If UBound(arList) >= ItemNumber Then
      GetListItem = arList(ItemNumber)
    End If
  End If
End Function

Used in a query would be like this. It is just cut and paste

SELECT originaltable.parentid,
       originaltable.parentdescription,
       originaltable.listitems,
       Getlistitem([listitems], 1) AS Item1,
       Getlistitem([listitems], 2) AS Item2,
       Getlistitem([listitems], 3) AS Item3,
       Getlistitem([listitems], 4) AS Item4,
       Getlistitem([listitems], 5) AS Item5
FROM   originaltable;

qryFunction qryFunction

ParentID	ParentDescription	ListItems	Item1	Item2	Item3	Item4	Item5
1​	Pets	Cat;Dog;Fish;Bird	Cat	Dog	Fish	Bird
2​	Reptiles	Frog;snake;lizard	Frog	snake	lizard
3​	Birds	Eagle;stork;buzzard	Eagle	stork	buzzard

 

[kirkm]

Using MajP's example in message 5, "Pets" would be the row header? "Cat;Dog;Fish;Bird" would be split by ";" to 4 fields in that row. But what would be the column header? Or it is anything (irrelevant) ?
Is the wizard capable of constructing this, or am I on the wrong track here ?

 

[kirkm]

MaJP. ours msgs crossed. Just gettig your db now.,

 

[MajP]

There may be some confusion here, but in order to use a xtab you would have to normalize the data first like I described into rows. The cross tab would not help with the splitting

 

[MajP]

I see in the original post that your list is serperated by a CRLF. Then you need to pass that to the function

SELECT originaltable.parentid,
       originaltable.parentdescription,
       originaltable.listitems,
       Getlistitem([listitems], 1,Chr(13) + Chr(10)) AS Item1,
       Getlistitem([listitems], 2,Chr(13) + Chr(10)) AS Item2,
       Getlistitem([listitems], 3,Chr(13) + Chr(10)) AS Item3,
       Getlistitem([listitems], 4,Chr(13) + Chr(10)) AS Item4,
       Getlistitem([listitems], 5,Chr(13) + Chr(10)) AS Item5
FROM   originaltable;

where CHr(13) + chr(10) is the vbcrlf
qryFunction qryFunction

parentid	parentdescription	listitems	Item1	Item2	Item3	Item4
1​	Pets	Cat Dog Fish bird	Cat	Dog	Fish	bird
2​	Reptiles	Frog Snake lizard	Frog	Snake	lizard
3​	Birds	Eagle Stork Buzzard	Eagle	Stork	Buzzard

 

[theDBguy]

Using MajP's example in message 5, "Pets" would be the row header? "Cat;Dog;Fish;Bird" would be split by ";" to 4 fields in that row. But what would be the column header? Or it is anything (irrelevant) ?
Is the wizard capable of constructing this, or am I on the wrong track here ?

Okay, I took the sample file @MajP provided in Post #5 and added the Crosstab Query for you. Hope it helps...

 

[MajP]

To add if you have a lot of data and lots of items the workaround in post 14 would be very inefficient compared to the Xtab shown above. Also that will handle any amount of columns. However, as pointed out you have to use what I posted earlier to create a populate child table.