Solved Transpose table

[Kayleigh]

Hi I would like to create a query which returns all students' parent's names as list of father and mother's names returned in one list.
Currently stored as fldFatherTitle, fldFatherFirstName, fldSurname etc.
So how do I output as 'Title First Name Surname' in one field for all parents?

 

[MajP]

FatherFullName:[fldFatherTitle] & " " & [FldFatherFirstName] & " " & [fldSurname]

 

[MajP]

Now you did not mention the mother so I think you want a union query Something like

Select
"Father" as Parent, [fldFatherTitle] & " " & [FldFatherFirstName] & " " & [fldSurname] as FullName
FROM
SomeTable
Union
Select
"Mother" as Parent, [fldMotherTitle] & " " & [FldMotherFirstName] & " " & [fldSurname] as FullName
FROM
SomeTable
Order by 3,1,2

 

[Kayleigh]

Would it work with all info in same table?

 

[Kayleigh]

So this worked great but didn't recognise the ORDER BY row?

Select
"Father" as Parent, [fldPLastName] & ", " & [fldFatherTitle] & " " & [fldFatherFirstName] as FullName
FROM
tblParents
UNION Select
"Mother" as Parent, [fldPLastName] & ", " & [fldMotherTitle] & " " & [fldMotherFirstName] as FullName
FROM
tblParents;

 

[MajP]

So this worked great but didn't recognise the ORDER BY row

Mistake on my part there are only 2 columns
Order by 2,1
or
Order by Fullname, Parent

 

[Kayleigh]

Perfect. How would I add a link to the student table so it can used as a lookup to display only parents relevant to current student?
My structure is tblParent > fldParentID; tblStudent > fldStudentID, fldParentID...

 

[Isaac]

You do not order unions.

 

[Kayleigh]

So I base a query on the union query?

 

[MajP]

I have no idea what Isaac is talking about. Of course you sort a union query.
Do you have just Parent ID or MotherID and FatherID in your student table?

 

[MajP]

There are a lot of student type database on this forum. If you are just tracking mother and father then maybe you can do it with just two IDs but only if life was that easy. I think more common is a junction table for multiple relations.
Here is the reason why. I know around here a lot of divorces and remarried parents. So there may be more than 2 parents authorized to pick up the kid from school or get called when they are in trouble. Also you have non traditional families (2 moms or 2 dads) or legal guardians that are not mother or father.

tblParent
1 Mr Jones
2 Mrs Jones
3 Mr Brown
4 Mr Green
5 Ms Smith
7 Ms Orange
8 Ms Purple

tblRelationType
1 Mother
2 Father
3 Grand Mother
4 Grand Father
5. Step Mother
6. Step Father
7. Legal Guardian


You need a junction table

tblStudents_Parent
---studentID_FK
---ParentID_FK
---RelationTypeID

so student 1 has Mom Mrs Jones, Step Father Mr. Brown, Bio Father Mr. Jones, Step Mother Ms Smith
That is stored in the junction table
1 1 2
1 2 1
1 3 6
1 5 5

If Student 2 has two mothers Ms Organge and Purple then
2 1 7
2 1 8

 

[Isaac]

I have no idea what Isaac is talking about. Of course you sort a union query.
Do you have just Parent ID or MotherID and FatherID in your student table?

I was thinking SQL server trying to order by in the individual components. Feel free to try it.

 

[MajP]

You cannot do this in SQL Server

SELECT Columns
FROM TABLE1
ORDER BY Columns
UNION ALL
SELECT Columns
FROM TABLE2
ORDER BY Columns

you get
Msg 156, Level 15, State 1, Line

But that is definitely not what I did.

However this is not SQL Server nor was there an example of trying to sort the individual parts. The query works fine once with the fix in #6. Thus my confusion since the statement made no sense.