-----Original Message-----
Try something like the following. Note I used the column number in the sort by
statement. I think you should be able to use "ORDER BY SpecialSort" as the last line.
SELECT NZ([Father Last Name],[Mother Last Name]) as SpecialSort,
1 AS recType,
[Father Salutation] AS [Father Salute],
[Father First Name] AS [Father First],
[Father Last Name] AS [Father Last],
[Father Address] AS [Father Addr],
[Father City] AS [Father C],
[Father State] AS [Father ST],
[Father Zip] AS [Father Z],
[Father Home Phone] AS [Father Phone],
[Father E-Mail] AS [Father E],
[Mother Salutation] AS [Mother Salute],
[Mother First Name] AS [Mother First],
[Mother Last Name] AS [Mother Last],
[Mother Address] AS [Mother Addr],
[Mother City] AS [Mother C],
[Mother State] AS [Mother ST],
[Mother Zip] AS [Mother Z],
[Mother Home Phone] AS [Mother Phone],
[Mother E-Mail] AS [Mother E],
[Family Number] AS [Development Number],
[Affiliation] AS [Affil]
FROM [students]
WHERE [status]="Current"
UNION
SELECT "", 2, [GF1 Salutation],
[GF1 First], [GF1 Last], [GF1 Address],
[GF1 City], [GF1 State], [GF1 Zip],
[GF1 Home Phone], [GF1 Email], [GM1 Salutation],
[GM1 First], [GM1 Last], [GM1 Address],
[GM1 City], [GM1 State], [GM1 Zip],
[GM1 Home Phone],[GM1 Email],[Family Number1],
[Affiliation]
FROM [Grandparents]
UNION
SELECT "", 3, [GF2 Salutation],[GF2 First], [GF2 Last],
[GF2 Address], [GF2 City], [GF2 State], [GF2 Zip],[GF2
Home Phone], [GF2 Email],[GM2 Salutation],[GM2 First],
[GM2 Last], [GM2 Address], [GM2 City], [GM2 State], [GM2
Zip],[GM2 Home Phone],[GM2 Email],[Family Number2],
[Affiliation]
FROM [Grandparents]
WHERE [GF2 Last] is not null
ORDER BY 1;
They are in the same select statement. I tried your
order by statement but get a syntax error. Here is my
SQL. Thanks for your help.
"SELECT 1 AS recType, [Father Salutation] AS [Father
Salute],[Father First Name] AS [Father First], [Father
Last Name] AS [Father Last], [Father Address] AS [Father
Addr], [Father City] AS [Father C], [Father State] AS
[Father ST], [Father Zip] AS [Father Z],[Father Home
Phone] AS [Father Phone],[Father E-Mail] AS [Father E],
[Mother Salutation] AS [Mother Salute],[Mother First
Name] AS [Mother First], [Mother Last Name] AS [Mother
Last], [Mother Address] AS [Mother Addr], [Mother City]
AS [Mother C], [Mother State] AS [Mother ST], [Mother
Zip] AS [Mother Z],[Mother Home Phone] AS [Mother Phone],
[Mother E-Mail] AS [Mother E],[Family Number] AS
[Development Number], [Affiliation] AS [Affil]
FROM [students]
WHERE [status]="Current"
UNION SELECT 2, [GF1 Salutation],[GF1 First], [GF1
Last], [GF1 Address], [GF1 City], [GF1 State], [GF1 Zip],
[GF1 Home Phone], [GF1 Email],[GM1 Salutation],[GM1
First], [GM1 Last], [GM1 Address], [GM1 City], [GM1
State], [GM1 Zip],[GM1 Home Phone],[GM1 Email],[Family
Number1],[Affiliation]
FROM [Grandparents]
UNION SELECT 3, [GF2 Salutation],[GF2 First], [GF2 Last],
[GF2 Address], [GF2 City], [GF2 State], [GF2 Zip],[GF2
Home Phone], [GF2 Email],[GM2 Salutation],[GM2 First],
[GM2 Last], [GM2 Address], [GM2 City], [GM2 State], [GM2
Zip],[GM2 Home Phone],[GM2 Email],[Family Number2],
[Affiliation]
FROM [Grandparents]
WHERE [GF2 Last] is not null
ORDER BY [Mother Last];
-----Original Message-----
POST your SQL please. Is Mother Last name in the same query as Father Last Name
or are these in two different Select statements in the UNION Query?
It could be as simple as having a calculated field and sorting by it.
Order By NZ(FatherName,MotherName)
daisy wrote:
I have a union query in which I want to sort by father
last name, then by mother last name. But if there
is
no
father last name, sort by mother last name. How do
I
do
.
