Union All Select

Topic

New Reply

Sometime ago this forum helped me with a Union All Select query. I still do not have a handle on this type of query. In this one I have two tables in which a query is run for making name tags. The format for the classmate is:
Firstname
(Maidenname) if any
Lastname

The format for the spouse is:
Firstname
Lastname

The where part is if both are attending the event then the count is 2 otherwise if only one is attending the count is one, which is obvious but I am playing around trying to get this thing to work. As of now I have this query:

[sql]SELECT FirstName, LastName, MaidenName, ClassYear, ClassPhoto, ClassYear AS OrderYear,
LastName AS Sortname, 1 AS SortOrder
FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
WHERE EventAttend=True
UNION ALL SELECT Spouse, LastName, ”,
IIF(Spouse = ”,Classyear,”),
IIF(MaidenName = ‘(Spouse)’,”,”), ClassYear,
Lastname, 2 As SortOrder
FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
WHERE Spouse ” AND EventAttend=True
ORDER BY OrderYear, SortName, SortOrder[/sql]

However, with this query, both Classmate and Spouse show up even though the record shows that only one will be in attendance. So I tried inserting this: IIF(NumAttend=1,1,NumAttend). But can’t seem to tie to the query to be effective.

Any ideas would be great. Thanx.

Reply | Quote

Replies

I think it’ll be hard to help you without seeing a stripped down and zipped copy of the database.

What is the purpose of IIF(MaidenName = ‘(Spouse)’,”,”) ? The value will always be an empty string whether MaidenName is “(Spouse)” or not.

IIF(NumAttend=1,1,NumAttend) isn’t very useful either, it’s equivalent to NumAttend, but what is NumAttend? A field in one of the tables?

Reply | Quote

Yes, NumAttend is field in a table. The qty in most cases will be either 1 or 2. If the classmate is married but spouse is not attending this function, then that number would be 1. Trying to use that field to somehow eliminate the printing of that name tag. At the present my thinking is to add an other field called SpouseEventAttend as a boolean. When the user fills in the number attending this event that boolean field could be flagged as true if both classmate and spouse are attending or false if only the class mate is attending. If classmate is bringing an additional friend, then I’m really stumped.

The database is used in a VB6 program.Hopefully, that should be of no concern.

I think that the IIF(MaidenName = ‘(Spouse)’,”,”) is to even out the Union All Select.

The zipped, stripped db is attached.

Appreciate the help.

Reply | Quote

In the record with ID 213, how should the query determine to whom the maiden name belongs? (They’re not attending, but that is beside the point.) I know that Rosie is probably a woman and Donald a man, but Access doesn’t know that.

Why do you have separate Members and Attending tables? They have a one-to-one relationship so it would be more efficient to combine the tables into one.

Reply | Quote

The Maiden name goes to the married classmate. Rosie is the classmate as entered into the user interface.

I understand the efficiencies or lack thereof with a one to one. However, for now that has to stay as is. Hopefully, that will not affect the Union All Select, will it?

Reply | Quote

Yes, you can keep the two tables.

Does this do what you want?

SELECT Firstname, Lastname, MaidenName, ClassYear, Classphoto, ClassYear AS SortYear, Lastname AS SortName, 1 AS SortOrder
FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
WHERE NumAttend>0
UNION ALL SELECT Spouse, Lastname, Null, Null, Null, ClassYear, Lastname, 2
FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
WHERE Spouse Is Not Null AND NumAttend>1
ORDER BY SortYear, SortName, SortOrder

Reply | Quote

Hans,

Many thanks for the help. That is exactly what I was/am looking to do. Appreciate your help.

Reply | Quote