Class reunion query

S

steve

I am doing a db to keep track of my classmates. I have written two queries,
one for men and one for women. What I am trying to do is get one query that
sorts alphabetically by the original last name. The men's have not changed
but the womens have for the most part so I put in a MaidenName field to
store the original name.

here is the SQL for the queries.

qryMaidenName

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " & [MI] & "
" & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0));

qryMaleNames

SELECT tblDemographics.LastName, tblDemographics.FirstName,
tblDemographics.MI, [FirstName] & " " & [MI] & " " & [MaidenName] & " " &
[LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0));

This may be a case of me just not looking at this in the right way.

Any help appreciated as always.

TIA

Steve
 
D

Douglas J. Steele

I don't understand why you're including MaidenName in the calculation of
FullName for the males.

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
UNION
SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
ORDER BY Nz([MaidenName], [LastName]), [FirstName]
 
S

steve

Doug,

When I copy/paste the code into the SQL of a blank query, it gives the
following error:

The ORDER BY expression (Nz([MaidenName],[LastName])) includes fields that
are not selected by the query. Only those fields requested in the first
query can be included in an ORDER BY expression.

I tried to include the fully qualified name [tblDemographics].[MaidenName],
[tblDemographics].[LastName] but that did not change the error.

suggestions?

Thanks
Steve
Douglas J. Steele said:
I don't understand why you're including MaidenName in the calculation of
FullName for the males.

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
UNION
SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
ORDER BY Nz([MaidenName], [LastName]), [FirstName]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


steve said:
I am doing a db to keep track of my classmates. I have written two
queries, one for men and one for women. What I am trying to do is get one
query that sorts alphabetically by the original last name. The men's have
not changed but the womens have for the most part so I put in a MaidenName
field to store the original name.

here is the SQL for the queries.

qryMaidenName

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " & [MI] &
" " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0));

qryMaleNames

SELECT tblDemographics.LastName, tblDemographics.FirstName,
tblDemographics.MI, [FirstName] & " " & [MI] & " " & [MaidenName] & " " &
[LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0));

This may be a case of me just not looking at this in the right way.

Any help appreciated as always.

TIA

Steve
Click to expand...
Click to expand...
 
D

Douglas J. Steele

That's odd. It should have worked...

Try adding the Nz function to the query, and sorting by the field number

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName,
Nz([MaidenName], [LastName]), [FirstName] AS SortKey
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
UNION
SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName,
Nz([MaidenName], [LastName]), [FirstName]
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
ORDER BY 5, 3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


steve said:
Doug,

When I copy/paste the code into the SQL of a blank query, it gives the
following error:

The ORDER BY expression (Nz([MaidenName],[LastName])) includes fields that
are not selected by the query. Only those fields requested in the first
query can be included in an ORDER BY expression.

I tried to include the fully qualified name
[tblDemographics].[MaidenName], [tblDemographics].[LastName] but that did
not change the error.

suggestions?

Thanks
Steve
Douglas J. Steele said:
I don't understand why you're including MaidenName in the calculation of
FullName for the males.

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
UNION
SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
ORDER BY Nz([MaidenName], [LastName]), [FirstName]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


steve said:
I am doing a db to keep track of my classmates. I have written two
queries, one for men and one for women. What I am trying to do is get
one query that sorts alphabetically by the original last name. The men's
have not changed but the womens have for the most part so I put in a
MaidenName field to store the original name.

here is the SQL for the queries.

qryMaidenName

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " & [MI]
& " " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0));

qryMaleNames

SELECT tblDemographics.LastName, tblDemographics.FirstName,
tblDemographics.MI, [FirstName] & " " & [MI] & " " & [MaidenName] & " "
& [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0));

This may be a case of me just not looking at this in the right way.

Any help appreciated as always.

TIA

Steve
Click to expand...
Click to expand...
Click to expand...
 
S

steve

Doug,

This query worked, almost <g>.

It sorts by the first name and not the LastName/(MaidenName) as I had hoped.
Is this possible?

Thanks for the help!

Steve
Douglas J. Steele said:
That's odd. It should have worked...

Try adding the Nz function to the query, and sorting by the field number

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName,
Nz([MaidenName], [LastName]), [FirstName] AS SortKey
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
UNION
SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName,
Nz([MaidenName], [LastName]), [FirstName]
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
ORDER BY 5, 3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


steve said:
Doug,

When I copy/paste the code into the SQL of a blank query, it gives the
following error:

The ORDER BY expression (Nz([MaidenName],[LastName])) includes fields
that are not selected by the query. Only those fields requested in the
first query can be included in an ORDER BY expression.

I tried to include the fully qualified name
[tblDemographics].[MaidenName], [tblDemographics].[LastName] but that
did not change the error.

suggestions?

Thanks
Steve
Douglas J. Steele said:
I don't understand why you're including MaidenName in the calculation of
FullName for the males.

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
UNION
SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
ORDER BY Nz([MaidenName], [LastName]), [FirstName]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am doing a db to keep track of my classmates. I have written two
queries, one for men and one for women. What I am trying to do is get
one query that sorts alphabetically by the original last name. The
men's have not changed but the womens have for the most part so I put in
a MaidenName field to store the original name.

here is the SQL for the queries.

qryMaidenName

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " & [MI]
& " " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0));

qryMaleNames

SELECT tblDemographics.LastName, tblDemographics.FirstName,
tblDemographics.MI, [FirstName] & " " & [MI] & " " & [MaidenName] & " "
& [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0));

This may be a case of me just not looking at this in the right way.

Any help appreciated as always.

TIA

Steve
Click to expand...
Click to expand...
Click to expand...
 
S

steve

Doug,

I looked again and the SortKey value is just the FirstName. The MaidenName
or LastName is not included at all.

To answer the question from your first post about including the MaidenName
in the male query, it was just cut and paste on my part. Had a query that
worked ...

Thanks much!!

Steve
 
S

steve

Here is the SQL that finally worked.

SELECT tblDemographics.DemographicsId, tblDemographics.MaidenName,
tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName,
Nz([MaidenName], [LastName]), [FirstName] AS SortKey
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
UNION SELECT tblDemographics.DemographicsId, tblDemographics.MaidenName,
tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName,
Nz([MaidenName], [LastName]), [FirstName]
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
ORDER BY Expr1006;

The query in datasheet view was returning Expr1006 for the Nz([MaidenName],
[LastName]) so I used that to sort by. I tried to us an AS but I kept
getting a column must be in both queries error.

Thanks for the help!!!!

Steve

Doug,

I looked again and the SortKey value is just the FirstName. The
MaidenName or LastName is not included at all.

To answer the question from your first post about including the MaidenName
in the male query, it was just cut and paste on my part. Had a query that
worked ...

Thanks much!!

Steve
steve said:
I am doing a db to keep track of my classmates. I have written two
queries, one for men and one for women. What I am trying to do is get one
query that sorts alphabetically by the original last name. The men's have
not changed but the womens have for the most part so I put in a MaidenName
field to store the original name.

here is the SQL for the queries.

qryMaidenName

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " & [MI] &
" " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0));

qryMaleNames

SELECT tblDemographics.LastName, tblDemographics.FirstName,
tblDemographics.MI, [FirstName] & " " & [MI] & " " & [MaidenName] & " " &
[LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0));

This may be a case of me just not looking at this in the right way.

Any help appreciated as always.

TIA

Steve
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The ORDER BY clause says to sort by the fifth field, then by the third
field. Unless my counting is off, the fifth field is the Nz([MaidenName],
[LastName]) field.

(Incidentally, there's no need to repeat FirstName as I originally did)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


steve said:
Doug,

This query worked, almost <g>.

It sorts by the first name and not the LastName/(MaidenName) as I had
hoped. Is this possible?

Thanks for the help!

Steve
Douglas J. Steele said:
That's odd. It should have worked...

Try adding the Nz function to the query, and sorting by the field number

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName,
Nz([MaidenName], [LastName]), [FirstName] AS SortKey
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
UNION
SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName,
Nz([MaidenName], [LastName]), [FirstName]
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
ORDER BY 5, 3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


steve said:
Doug,

When I copy/paste the code into the SQL of a blank query, it gives the
following error:

The ORDER BY expression (Nz([MaidenName],[LastName])) includes fields
that are not selected by the query. Only those fields requested in the
first query can be included in an ORDER BY expression.

I tried to include the fully qualified name
[tblDemographics].[MaidenName], [tblDemographics].[LastName] but that
did not change the error.

suggestions?

Thanks
Steve
I don't understand why you're including MaidenName in the calculation of
FullName for the males.

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0))
UNION
SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0))
ORDER BY Nz([MaidenName], [LastName]), [FirstName]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am doing a db to keep track of my classmates. I have written two
queries, one for men and one for women. What I am trying to do is get
one query that sorts alphabetically by the original last name. The
men's have not changed but the womens have for the most part so I put
in a MaidenName field to store the original name.

here is the SQL for the queries.

qryMaidenName

SELECT tblDemographics.MaidenName, tblDemographics.LastName,
tblDemographics.FirstName, tblDemographics.MI, [FirstName] & " " &
[MI] & " " & [MaidenName] & " " & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Not Null) AND
((tblDemographics.Deceased)=0));

qryMaleNames

SELECT tblDemographics.LastName, tblDemographics.FirstName,
tblDemographics.MI, [FirstName] & " " & [MI] & " " & [MaidenName] & "
" & [LastName] AS FullName
FROM tblDemographics
WHERE (((tblDemographics.MaidenName) Is Null) AND
((tblDemographics.Deceased)=0));

This may be a case of me just not looking at this in the right way.

Any help appreciated as always.

TIA

Steve
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting Query Results - Advice Please 5
query question 2
sort union query 1
Union query 5
update Query written 12
reconstruct table that was normalized into several tables 3
Varying Number of Columns in Crosstab Query 2
Criteria on query w/ calculation acts like perameter query 1

Top