SQL help

[secretary]

I would like to generate a single-field query of names like so:



Smith, John & Mary
Baker, Phillip
McGill, Sandra

where couples inwhich each person has kept their last name gets a separate record (eg. Phillip and Sandra). The names are concatenated strings taken from table fields of First and Last Names:

SELECT [FirstMember].[LastName] & ", " & [FirstMember].[FirstName] & " & " & [SecondMember].[FirstName]  AS Name
FROM FirstMember, SecondMember
WHERE FirstMember.LastName = SecondMember.LastName

UNION

SELECT FirstMember.LastName & ", " & FirstMember.FirstName AS Name
FROM FirstMember, SecondMember
WHERE FirstMember.LastName <> SecondMember.LastName

UNION 

SELECT SecondMember.LastName & ", " & SecondMember.FirstName AS Name
FROM FirstMember, SecondMember
WHERE FirstMember.LastName <> SecondMember.LastName;

The following data is generated:

Smith, John
Smith, John & Mary
Smith, Mary
Baker, Phillip
McGill, Sandra

Can someone advise me where I'm going wrong?

[rpeare]

What is your primary key? it's not referenced in your query

I created two tables one named FIRSTMEMBER, one named SECONDMEMBER. Each table had the fields ID, FIRSTNAME, LASTNAME

I put in your data (John and mary smith were linked via a matching ID, philip baker and sandra mcgill were linked via a matching ID) and I ran this query

SELECT [FirstMember].[LastName] & ", " & [FirstMember].[FirstName] & " & " & [SecondMember].[FirstName] AS Name
FROM FirstMember, SecondMember
WHERE ((FirstMember.LastName = SecondMember.LastName) and (firstmember.id = secondmember.id))

UNION

SELECT FirstMember.LastName & ", " & FirstMember.FirstName AS Name
FROM FirstMember, SecondMember
WHERE ((FirstMember.LastName <> SecondMember.LastName) and (firstmember.id = secondmember.id))

UNION

SELECT SecondMember.LastName & ", " & SecondMember.FirstName AS Name
FROM FirstMember, SecondMember
WHERE ((FirstMember.LastName <> SecondMember.LastName) and (firstmember.id = secondmember.id))

and got the results you were looking for. If you do not put in the matching ID's you're going to get a result. Your individual queries in the union query are basically saying make a record for every possible combination of items in the table FIRSTMEMBER and SECONDMEMBER, which means each of your union parts will have the number of items in each table multiplied by each other.

If you do not have an ID field you're in trouble but I suspect you do. You need to use that ID field that links them together in your query.

[secretary]

it was just as you said. i was thinking about how I would use the primary key in the criteria. quick fix. thanks!

EDIT: Just thought of something.
What if, in addition to the above, there is no SecondMember and I just have a single person that I want listed as

Sawyer, Jack

?

[rpeare]

First, I would really recommend that you look at the SQL driving each portion of your union query. Make sure it's giving you the results you want, then work on the next portion. As I said you are not linking your tables in each portion of the union query which can be a dangerous practice. If you do link them you probably would not need the part of the criteria where the firsttable.id = secondtable.id

Lastly your people that appear in your firsttable should be getting captured by your statement, because the secondtable.id will be null and therefore not match. If they are not appearing build a query that shows the population you want (probably by linking the tables and doing an isnull in the secondtable.id criteria) then add it with another UNION statement.

[secretary]

How do I link the tables so that the ID criteria is not required?

[rpeare]

I don't understand the question. Are you saying you do not have an ID field on either table? Or are you asking how to link the tables properly in each section of your union query. If it's the former you're in trouble every table should have a unique key field, if it's the latter just do as I suggested in the previous post and take each section of your union query to a separate query and view the design of it, then you can correctly link them and copy the SQL code back to your union query.

[secretary]

Oh okay. I only got confused because you said if I link the tables I'd likely not need the firsttable.id = secondtable.id part of the criteria...but I thought that having that was vital to linking the tables.

EDIT:

I've just realized that technically secondmember.id is never NULL. It simply, on occasion, doesn't have an ID in common with firstmember table, so I'm having difficulty generating just that one name that I want where it only has a firstmember entry and no secondmember counterpart.

[rpeare]

that's why I said you needed a fourth section of your union query one that had secondmember.id as a null value (isnull(Secondmember.id) in the criteria.

[secretary]

I've been trying different kinds of criteria, but I only ever get ZERO names generated:

SELECT FirstMember.LastName & ", " & FirstMember.FirstName AS Name
FROM FirstMember, SecondMember
WHERE IsNull(SecondMember.MembershipNum) AND (FirstMember.MembershipNum = SecondMember.MembershipNum)

If I use IsNull(SecondMember.MembershipNum) in the criteria I'm only ever going to get a null result unless I can somehow relate it to a FirstMember.MembershipNum.

I've tested all 3 other queries individually like you suggested and I get the appropriate name types.

I can't figure out what condition will tell the query to get that one name type though.

I WAS THINKING:
Would it be easier to make a simple query of 6 fields (firstID, secondID, firstFirstName, firstLastName, secondFirstName, secondLastName) and then use THOSE fields as a source for my union query instead??? Because then I figure IsNull will actually work.

EDIT: I just used an existing query as a source instead of the tables. It worked out! I'm just curious why I couldn't manage to achieve the result I needed by using the tables directly.

Thanks for all your help!