Combining SQL statements in a Query

[jcbhydro]

Good Morning,

I have 2 queries serving useful routines, one to filter out duplicate addresses for a mailing label routine, the second to convert tick boxes into Y/N answers for a Tab Delimited Text file export.
My question is; can I combine the 2 SQL statements in the 2nd query without amending them in any way.

The SQL statements are;

FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-News List])=False) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));
******************************************
SELECT [Mail List].[E-Mail], [Mail List].[First Name], [Mail List].Surname, IIf([e-News List],"Y","N") AS ENewsYN, IIf([SO],"Y","N") AS SOYN, IIf([Leader of Groups],"Y","N") AS LeaderYN, IIf([Committee],"Y","N") AS CommitteeYN
FROM [Mail List]
WHERE ((([Mail List].[E-Mail]) Is Not Null))
ORDER BY [Mail List].Surname;
******************************************

Regards,

jcbhydro

 

[MarkK]

I don't understand the question. To combine is to amend. How can you combine and not amend?

 

[jcbhydro]

I don't understand your 'failure to understand'! It is quite possible to combine 2 elements without amending the individual elements.
If I combine steel with concrete I get reinforced concrete, yet neither the steel nor the concrete have been amended.

My question was very simple. Is it feasible to combine the 2 SQL statements without modifying the text of the individual statements?

jcbhydro

 

[MarkK]

It totally depends on how you combine them. For more immediate feedback, play with these in the query designer, SQL view, and see what you run into.
Hope this helps,

 

[jcbhydro]

Thanks for the comment,

I shall play about with the SQL coding and see what happens.

jcbhydro

 

[jcbhydro]

Good Afternoon,

I have attempted to combine and modify the SQL statements as suggested, but I get a persisting syntax error highlighted on the second 'Select' statement. I assumed that was due to a mismatch of ellipsesm but I have failed to find the error.

SELECT a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List]
FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (CStr([a].[Member ID])+[a].[Address 1])=SELECT (cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])))
SELECT [Mail List].[E-Mail], [Mail List].[First Name], [Mail List].Surname, IIf([e-News List],"Y","N") AS ENewsYN, IIf([SO],"Y","N") AS SOYN, IIf([Leader of Groups],"Y","N") AS LeaderYN, IIf([Committee],"Y","N") AS CommitteeYN
FROM [Mail List]
WHERE ((([Mail List].[E-Mail]) Is Not Null))
ORDER BY [Mail List].Surname;

Any suggestions would be very welcome.

jcbhydro

 

[MarkK]

I don't believe you can have multiple SELECT clauses in Jet SQL. It looks like you have multiple FROM clauses also.

 

[jcbhydro]

Hi there,

It is not correct that one cannot have more than one 'SELECT' or 'FROM' in an SQL statement.

I have now succeeded in combining the 2 SQI statements which includes duplicate 'SELECT' & 'From' statements, The revised SQL performs precisely as intended.

Quote
SELECT a.[Member ID], a.[E-Mail], a.[First Name], a.Surname, IIf([e-News List],"Y","N") AS ENewsYN, IIf([SO],"Y","N") AS SOYN, IIf([Leader of Groups],"Y","N") AS LeaderYN, IIf([Committee],"Y","N") AS CommitteeYN
FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[E-Mail], a.[First Name], a.Surname, IIf([e-News List],"Y","N"), IIf([SO],"Y","N"), IIf([Leader of Groups],"Y","N"), IIf([Committee],"Y","N"), CStr([a].[Member ID])+[a].[E-Mail]
HAVING (((CStr([a].[Member ID])+[a].[E-Mail])=(Select cStr(Min(b.[Member ID])) + b.[E-Mail]
From [Mail List] as b
Where b.[E-Mail] = a.[E-Mail]
Group By [E-Mail])));
Unquote

jcbhydro