Need help with SQL in an Access query that has UNION for the non-relational queries/tables

[Pat Hartman]

Sorry about the period. All sentences end with a period and my fingers just do it

Get rid of the FirstName from the Order by. I forgot the Group By. Make sure the names I chose are correct or change them to match your names. Or, you can fix the outer query that has the union as a subquery so that it includes the last name and assigns a meaningful alias for the count(*) result.

Select qryMembershipUnion.LastName, Count(*) As DupeLastName
From qryMembershipUnion
Group By LastName;

If you want to group by first and last name then add first name to both the select clause and the group by.

Select qryMembershipUnion.LastName, qryMembershipUnion.FirstName, Count(*) As DupeLastName
From qryMembershipUnion
Group By LastName, qryMembershipUnion.FirstName;

When you don't know how to write SQL, your best bet is to stick with the QBE and let Access build it for you. Unfortunately, the QBE gives you no help for Union queries or queries with subqueries.

 

[dlhappy]

did you try the suggestion in Post #16

Yes, sorry, I didn't realize that I didn't reply. Would you believe it, but I had to change your Post #16 from a link to plain text, above in the question, because it thought my reply was spam and would not let me reply. I

Posting just that code by itself only relates to the count, and so I get one column and row. The column is LastNamesCount and the row shows 161
That count is wrong, as the actual records from my query that works is 170. I assume that the code that you supplied needs to be added to the original code, that works, giving the names, email addresses, etc., but does not show a count total on the bottom of any column (which then exports to the Excel worksheet). I can of course, see the total number just by looking at the bottom of the datasheet table where it shows record (the number of the record/row) of 170.

Anytime I add any of the code suggestions above to any of my already existing code, I get an error message that there is a syntax error. It seems that I can't add any SELECT or anything else in between the end and existing code.

Here is your code, as an addition to the original code:

SELECT qryMembershipNotArchivedEmailMailingList.NonMemberID,Title,LastName,FirstName,Email
FROM qryMembershipNotArchivedEmailMailingList

UNION SELECT qryNonMemberPurchaserNotArchivedForEmailing.NonMemberID,Title,LastName,FirstName,Email
FROM qryNonMemberPurchaserNotArchivedForEmailing
ORDER BY LastName, FirstName

SELECT
COUNT(*) AS LastNamesCount
FROM (
SELECT DISTINCT
m.LastName
FROM tblMembership m
WHERE m.Email <> ""
AND m.Email IS NOT NULL
AND ValidateAddrUsePeriod(m.AddrUseMonthBegin, m.AddrUseMonthEnd) = True
AND m.Archived = False
UNION
SELECT DISTINCT
p.LastName
FROM tblNonMemberPurchaser p
WHERE p.Archived = False
AND (p.ArchiveReason = "" OR p.ArchiveReason IS NULL)
);

Trying to save it generates a Syntax error in query expression 'FirstName and then lists the rest of the code until the end.
If I add a "," to FirstName then I get Syntax error in query expression 'SELECT
COUNT(*) AS LastNamesCount

 

[cheekybuddha]

The column is LastNamesCount and the row shows 161
That count is wrong

Probably caused by the DISTINCT's.

Do you get 170 records if you use:

SELECT
  COUNT(*) AS LastNamesCount
FROM (
  SELECT
    m.LastName
  FROM tblMembership m
  WHERE m.Email <> ""
    AND m.Email IS NOT NULL
    AND ValidateAddrUsePeriod(m.AddrUseMonthBegin, m.AddrUseMonthEnd) = True
    AND m.Archived = False
  UNION
  SELECT 
    p.LastName
  FROM tblNonMemberPurchaser p
  WHERE p.Archived = False
    AND (p.ArchiveReason = "" OR p.ArchiveReason IS NULL)
);

 

[dlhappy]

Probably caused by the DISTINCT's.

Do you get 170 records if you use:

SELECT
  COUNT(*) AS LastNamesCount
FROM (
  SELECT
    m.LastName
  FROM tblMembership m
  WHERE m.Email <> ""
    AND m.Email IS NOT NULL
    AND ValidateAddrUsePeriod(m.AddrUseMonthBegin, m.AddrUseMonthEnd) = True
    AND m.Archived = False
  UNION
  SELECT
    p.LastName
  FROM tblNonMemberPurchaser p
  WHERE p.Archived = False
    AND (p.ArchiveReason = "" OR p.ArchiveReason IS NULL)
);

No it shows a total of 161. But just a count without the table listing of names, email addresses, etc. which is what I want, all combined - just a total at the bottom. I think I'll just use the sum symbol on the ribbon/menu and then I get what I want (suitable for exporting to Excel with the total.

However, I want to thank you, and everyone else for the effort and time devoted in replying to my inquiry.

 

[dlhappy]

No it shows a total of 161. But just a count without the table listing of names, email addresses, etc. which is what I want, all combined - just a total at the bottom. I think I'll just use the sum symbol on the ribbon/menu and then I get what I want (suitable for exporting to Excel with the total.

However, I want to thank you, and everyone else for the effort and time devoted in replying to my inquiry.

 

[cheekybuddha]

all combined - just a total at the bottom

Ah, I hadn't twigged that! I thought you just wanted a count of the records you were going to send emails to.

You will have to UNION the total to create the last row in a dataset.

Using your original query that works, you can try:

SELECT
  NonMemberID,
  Title,
  LastName,
  FirstName,
  Email
FROM qryMembershipNotArchivedEmailMailingList
UNION
SELECT
  NonMemberID,
  Title,
  LastName,
  FirstName,
  Email
FROM qryNonMemberPurchaserNotArchivedForEmailing
UNION
SELECT
  NULL,
  'Total:',
  COUNT(*),
  NULL,
  NULL
FROM (
  SELECT
    NonMemberID
  FROM qryMembershipNotArchivedEmailMailingList
  UNION
  SELECT
    NonMemberID
  FROM qryNonMemberPurchaserNotArchivedForEmailing
)
ORDER BY
  IIf(Title = 'Total:', 'zzzz', LastName),
  FirstName
;

 

[dlhappy]

Ah, I hadn't twigged that! I thought you just wanted a count of the records you were going to send emails to.

You will have to UNION the total to create the last row in a dataset.

Using your original query that works, you can try:

SELECT
  NonMemberID,
  Title,
  LastName,
  FirstName,
  Email
FROM qryMembershipNotArchivedEmailMailingList
UNION
SELECT
  NonMemberID,
  Title,
  LastName,
  FirstName,
  Email
FROM qryNonMemberPurchaserNotArchivedForEmailing
UNION
SELECT
  NULL,
  'Total:',
  COUNT(*),
  NULL,
  NULL
FROM (
  SELECT
    NonMemberID
  FROM qryMembershipNotArchivedEmailMailingList
  UNION
  SELECT
    NonMemberID
  FROM qryNonMemberPurchaserNotArchivedForEmailing
)
ORDER BY
  IIf(Title = 'Total:', 'zzzz', LastName),
  FirstName
;

Hi:

I tried it but it would not open and instead I got the following error message:

The ORDER BY expression (IIf(Title='Total:','zzzz',LastName)) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an OREDER BY expression.

I tried making a few changes (not, however, really knowing what I was doing) such as changing IIf to if but nothing worked.

Anyway, as I said in an earlier post, I can get the results by merely clicking the summation button on the ribbon from my original union query and that works fine.

So, again, many thanks for all of work that you, and other did. But at this point, I just use the ribbon/menu button and get what I want.