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

[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.