Access: Cartesian product within a row

Question

I am running Access 2016 with a table (called Table1) of parents and their children. The parents are listet in the first column and their children are shown in columns two to four and all the children correspond to the parent of the same row. However, the number of children per parent varies and hence some cells in the higher column numbers are blank:

Parent     Children1  Children2  Children3  Children4  Children5  
  A            A1         A2         A3         A4         A5
  B            B1         B2         --         --         --
  C            C1         C2         C3         --         --    

Is there a way to write a Query which transforms this table into a direkt assignment of each children to its parent individually? I need the output to look like this (the blank cells shall be ignored):

Parent     Children  
  A            A1    
  A            A2                              
  A            A3       
  A            A4      
  A            A5   
  B            B1 
  B            B2           
  C            C1   
  C            C2  
  C            C3  

Technically this should be something like the cartesian product of all columns from table1 but just within the row of each parent. I searched in the directions of access functions for cartesian product, transpose and transform but I could not find an appropriate exapmle.

Thanks

Answer 1

You can use union all:

select parent, children1 as child from t where children1 is not null
union all
select parent, children2 as child from t where children2 is not null
union all
select parent, children3 as child from t where children3 is not null
union all
select parent, children4 as child from t where children4 is not null
union all
select parent, children5 as child from t where children5 is not null