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

[dlhappy]

Please have patience with me. I not familiar with modern programming. I'm almost 81 years old and had two Fortran II classes at NYU in the mid 1960s. The most I do now are relatively simple batch files that I run from the command prompt on my desktop computer. I'm retired but am a membership VP and on the Board of Directors of a non-profit organization. We are all unpaid volunteers.

I'm using MS Access and had to use SQL because I need to combine data from two tables within the database that are non-relational. I've never used SQL coding before, but doing online research, I got the need information, especially using the UNION statement to get the desired results.

This works:
-------------------------------------
SELECT qryMembershipNotArchivedEmailMailingList.NonMemberID,Title,LastName,FirstName,Email
FROM qryMembershipNotArchivedEmailMailingList

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

But I want a count of LastName from the combination arrived at by the UNION

I tried adding COUNT(LastName) and also tried COUNT(*) and in both instances and when that didn't work, I added GROUP BY Lastname statements, and no matter what I do, or where I put these statements, I always get an error when trying to save it, or in some variations, I can save it but then it won't open, except in design view.

If not a syntax error, then a missing operator error.

Can someone please complete the code that will give a combined count of the LastName from both queries (which are from two non-relational tables within the database).

Also, is in the first example, that works, but does not give me the total count that I want, is there a way to eliminate the Box that first pops-up asking me to enter a parameter for NonMemberID. I just hit enter and the datasheet view opens up. I saw examples of how to do that with a regular Access query, but not one that only has SQL code , as this one does because of the UNION statement (no properties window or anything else).

I saw that I can get my total count when opening the query by selecting home on the ribbon and then selecting the summation symbol menu item for the column or columns. Also the number of records on the bottom shows the total as well. But I would like the total to always show (as a count) so that when I export the query results to Excel, the total shows at the bottom of the Excel worksheet.

Thanks.

 

[isladogs]

You can't do this directly in the UNION query. Save the query then do an aggregate (totals) query based on the saved union query

 

[plog]

isladogs answer is the correct one for the main issue. For this one:

is there a way to eliminate the Box that first pops-up asking me to enter a parameter for NonMemberID.

It is caused by NonMemberID not being a field in the underlying datasource. That pop up is there because when you use NonMemberID in the query it has no idea where to get it from. Either eliminate it from the query or add the table that contains it.

Do you know what query is actually causing it?

 

[dlhappy]

You can't do this directly in the UNION query. Save the query then do an aggregate (totals) query based on the saved union query

Thanks, that worked with this code in a separate query:

SELECT Count(LastName) AS LastNameCombined
FROM qryEmailListNonArchivedMembersANDNonArchivedNonMemberPurchasers;

Just as an aside, I originally just had Count but Access indicated I needed SELECT and then it worked and when I looked at the code, it added AS Expr1. So I changed Expr1 to LastName and got an error message, so I changed it to LastNameCombined and it worked.

But while I get the total, it is not what I want, because I want the total in the original query in datasheet view. But it really does not matter that much since I can just use the datasheet view for the main query and click the summation symbol with the cursor in the LastName field and get the count, which will then export to Excel.

 

[dlhappy]

It

isladogs answer is the correct one for the main issue. For this one:



It is caused by NonMemberID not being a field in the underlying datasource. That pop up is there because when you use NonMemberID in the query it has no idea where to get it from. Either eliminate it from the query or add the table that contains it.

Do you know what query is actually causing it?

Thanks for your reply.

I think so. It must be this one FROM qryMembershipNotArchivedEmailMailingList. I guess that it must be caused by the Union query itself because one query/table contains that field (NonMemberID) whereas the other table does not (and should not). So when I just click enter to ignore the box, in datasheet view, the non-members rows have their NonMemberID number shown in the related column, whereas the members, from a different table, have nothing in that column in their row. Just as a test I entered and equal sign in the popup parameter box and then that column for non-members still had their NonMemberID whereas for members, the = sign was in their row in the NonMemberID column. Actually, when I merely hit enter without typing anything in the parameter popup box, the datasheet results are correct in that only the non-members have a number in the NonMemberID column, whereas members show that field blank (which is correct for members)

UPDATE:
When I eliminate NonMemberID entirely from the SQL, then Access opens the query in datasheet view without the parameter pop-up box. I want the non-member IDs to show, though, so that I can quickly tell from the listing, those who are not members, because they have an ID # in the field whereas everyone else (the members) merely have a blank in that field column.

 

[cheekybuddha]

But I want a count of LastName from the combination arrived at by the UNION

You can probably achieve this directly if you don't use the queries but access the tables themselves.

What is the underlying SQL for queries:
qryMembershipNotArchivedEmailMailingList
qryNonMemberPurchaserNotArchivedForEmailing

 

[dlhappy]

You can probably achieve this directly if you don't use the queries but access the tables themselves.

What is the underlying SQL for queries:
qryMembershipNotArchivedEmailMailingList
qryNonMemberPurchaserNotArchivedForEmailing

I didn't write any of it - I merely did an SQL view and copied and pasted them here:

For qryMembershipNotArchivedEmailMailingList it is:

SELECT qryMembership.Title, qryMembership.LastName, qryMembership.FirstName, qryMembership.Email
FROM qryMembership
WHERE (((qryMembership.Email)<>"" And (qryMembership.Email) Is Not Null) AND ((ValidateAddrUsePeriod([AddrUseMonthBegin],[AddrUseMonthEnd]))=True) AND ((qryMembership.Archived)=False))
ORDER BY qryMembership.LastName, qryMembership.FirstName;

For qryNonMemberPurchaserNotArchivedForEmailing it is:

SELECT tblNonMemberPurchaser.Title, tblNonMemberPurchaser.LastName, tblNonMemberPurchaser.FirstName, tblNonMemberPurchaser.Archived, tblNonMemberPurchaser.ArchiveReason, tblNonMemberPurchaser.Notes, tblNonMemberPurchaser.LastPurchaseDateForAging, tblNonMemberPurchaser.Email, tblNonMemberPurchaser.Country, tblNonMemberPurchaser.FormerMember, tblNonMemberPurchaser.NonMemberID
FROM tblNonMemberPurchaser
GROUP BY tblNonMemberPurchaser.Title, tblNonMemberPurchaser.LastName, tblNonMemberPurchaser.FirstName, tblNonMemberPurchaser.Archived, tblNonMemberPurchaser.ArchiveReason, tblNonMemberPurchaser.Notes, tblNonMemberPurchaser.LastPurchaseDateForAging, tblNonMemberPurchaser.Email, tblNonMemberPurchaser.Country, tblNonMemberPurchaser.FormerMember, tblNonMemberPurchaser.NonMemberID
HAVING (((tblNonMemberPurchaser.Archived)=False) AND ((tblNonMemberPurchaser.ArchiveReason)="" Or (tblNonMemberPurchaser.ArchiveReason) Is Null))
ORDER BY tblNonMemberPurchaser.LastName, tblNonMemberPurchaser.FirstName;

 

[cheekybuddha]

Can you post the SQL of qryMembership as well please.

That said, you will still probably require the UNION since you store people in different tables for members and non-members which is not ideal.

Your final result will probably be something like

SELECT
  COUNT(*) AS LastNamesCount
FROM (
  SELECT
    LastName
  FROM qryMembership
  WHERE Email <> ""
    AND Email IS NOT NULL
    AND ValidateAddrUsePeriod(AddrUseMonthBegin, AddrUseMonthEnd) = True
    AND Archived = False
  UNION
  SELECT
    LastName
  FROM tblNonMemberPurchaser
  WHERE Archived = False
    AND (ArchiveReason = "" Or ArchiveReason IS NULL)
)
GROUP BY
  LastName
;

 

[dlhappy]

Can you post the SQL of qryMembership as well please.

That said, you will still probably require the UNION since you store people in different tables for members and non-members which is not ideal.

Your final result will probably be something like

SELECT
  COUNT(*) AS LastNamesCount
FROM (
  SELECT
    LastName
  FROM qryMembership
  WHERE Email <> ""
    AND Email IS NOT NULL
    AND ValidateAddrUsePeriod(AddrUseMonthBegin, AddrUseMonthEnd) = True
    AND Archived = False
  UNION
  SELECT
    LastName
  FROM tblNonMemberPurchaser
  WHERE Archived = False
    AND (ArchiveReason = "" Or ArchiveReason IS NULL)
)
GROUP BY
  LastName
;

Thanks.

I copied the code that you supplied and ran a test but the result only had one column, labeled LastNameCount and just the number 1 in each row for 161 records. That for some reason is not the correct number because my original query has the correct 170 records. Also, my original union query has 5 columns (see datasheet view of query name and column headings, below)

The SQL View for the qryMembership is:

SELECT tblMembership.MembershipID, tblMembership.Title, tblMembership.LastName, tblMembership.FirstName, tblMembership.Status, Max(tblMembersTrans.MembershipYear) AS MaxOfMembershipYear, tblMembership.Archived, tblMembership.ArchiveDate, tblMembership.ArchiveReason, tblMembership.AddressLine1, tblMembership.AddressLine2, tblMembership.City, tblMembership.State, tblMembership.Zipcode, tblMembership.Country, tblMembership.Email, tblMembership.PhoneNumber, tblMembership.Notes, tblMembership.JoinYr, tblMembership.EmailOptIn, tblMembership.ePubsOnly, tblMembership.AddDate, tblMembership.EditDate, tblMembership.AddrUseMonthBegin, tblMembership.AddrUseMonthEnd
FROM tblMembership INNER JOIN tblMembersTrans ON tblMembership.MembershipID = tblMembersTrans.MemberID
GROUP BY tblMembership.MembershipID, tblMembership.Title, tblMembership.LastName, tblMembership.FirstName, tblMembership.Status, tblMembership.Archived, tblMembership.ArchiveDate, tblMembership.ArchiveReason, tblMembership.AddressLine1, tblMembership.AddressLine2, tblMembership.City, tblMembership.State, tblMembership.Zipcode, tblMembership.Country, tblMembership.Email, tblMembership.PhoneNumber, tblMembership.Notes, tblMembership.JoinYr, tblMembership.EmailOptIn, tblMembership.ePubsOnly, tblMembership.AddDate, tblMembership.EditDate, tblMembership.AddrUseMonthBegin, tblMembership.AddrUseMonthEnd
ORDER BY tblMembership.LastName, tblMembership.FirstName, Max(tblMembersTrans.MembershipYear);


Also, in case needed, this is the SQL View for qryNonMemberPurchaserNotArchivedForEmailing

SELECT tblNonMemberPurchaser.Title, tblNonMemberPurchaser.LastName, tblNonMemberPurchaser.FirstName, tblNonMemberPurchaser.Archived, tblNonMemberPurchaser.ArchiveReason, tblNonMemberPurchaser.Notes, tblNonMemberPurchaser.LastPurchaseDateForAging, tblNonMemberPurchaser.Email, tblNonMemberPurchaser.Country, tblNonMemberPurchaser.FormerMember, tblNonMemberPurchaser.NonMemberID
FROM tblNonMemberPurchaser
GROUP BY tblNonMemberPurchaser.Title, tblNonMemberPurchaser.LastName, tblNonMemberPurchaser.FirstName, tblNonMemberPurchaser.Archived, tblNonMemberPurchaser.ArchiveReason, tblNonMemberPurchaser.Notes, tblNonMemberPurchaser.LastPurchaseDateForAging, tblNonMemberPurchaser.Email, tblNonMemberPurchaser.Country, tblNonMemberPurchaser.FormerMember, tblNonMemberPurchaser.NonMemberID
HAVING (((tblNonMemberPurchaser.Archived)=False) AND ((tblNonMemberPurchaser.ArchiveReason)="" Or (tblNonMemberPurchaser.ArchiveReason) Is Null))
ORDER BY tblNonMemberPurchaser.LastName, tblNonMemberPurchaser.FirstName;


The datasheet view from my original Union query looks like this (I deleted the rows showing the personal data (there are a total of 170 rows/records)

 

[cheekybuddha]

Of course untested, but the following should give you the result you need:

SELECT
  COUNT(*) AS LastNamesCount
FROM (
  SELECT
    m.LastName
  FROM tblMembership m
  INNER JOIN (
    SELECT
      MembershipID,
      MAX(MembershipYear) AS MaxOfMembershipYear
    FROM tblMembersTrans
    GROUP BY
      MembershipID
  ) mt
          ON m.MembershipID = mt.MembershipID
  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)
)
GROUP BY
  LastName
;

This will give you a count of the different LastName's, but will give an incorrect result if you want a count of how many members/non members if any share the same LastName with another.

 

[dlhappy]

Of course untested, but the following should give you the result you need:

SELECT
  COUNT(*) AS LastNamesCount
FROM (
  SELECT
    m.LastName
  FROM tblMembership m
  INNER JOIN (
    SELECT
      MembershipID,
      MAX(MembershipYear) AS MaxOfMembershipYear
    FROM tblMembersTrans
    GROUP BY
      MembershipID
  ) mt
          ON m.MembershipID = mt.MembershipID
  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)
)
GROUP BY
  LastName
;

This will give you a count of the different LastName's, but will give an incorrect result if you want a count of how many members/non members if any share the same LastName with another.

Thanks.
It doesn't open, Access returns the following error message:
The specified field 'MembershipID' could refer to more than one table listed in the FROM clause of your SQL statement.

Incidentally, I don't the the max of membership year (the last membership year for which the member paid dues) for this query, but I do want the count of all last names, whether there are some with the same last name or not I need a total number for the emailing, which is what this query does, it lists all non-archived members (I never delete a member, whether the member drops out or passes away, I just archive those former members. The non-member purchasers sometimes become a dues paying member and then they are added to the membership table and are archived from the non-member purchasers table, etc.

This does not pertain to the coding, but I'm just explaining what I'm doing, so it makes sense (maybe?)
What the present query does is just list all non-archived members and non-member purchasers for purposes of an emailing announcement, etc. to all on our mailing lists. Also, when the emailing is done, it is sent as Bcc so no one sees any other's email address (the opt-in that you might have noticed in the tables are for a special emailing of those members who wish to share their email address with other members so that they can communicate with each other). For emailing purposes, I can only Bcc an email, with I think less than 100 addressees, because otherwise my bulk email gets rejected as spam. So, now, with 170, I split the emailing into two separate emails, with half the addresses in each.
Incidentally, in case anyone is interested, this is the organization involved: jussibjorlingsociety.org

 

[cheekybuddha]

OK, try it like:

SELECT
  COUNT(*) AS LastNamesCount
FROM (
  SELECT
    m.LastName
  FROM tblMembership m
  INNER JOIN (
    SELECT
      t.MembershipID,
      MAX(t.MembershipYear) AS MaxOfMembershipYear
    FROM tblMembersTrans t
    GROUP BY
      t.MembershipID
  ) mt
          ON m.MembershipID = mt.MembershipID
  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)
)
GROUP BY
  LastName
;

 

[cheekybuddha]

but I do want the count of all last names, whether there are some with the same last name or not I need a total number for the emailing

Perhaps you should have a count of all the different email addresses to figure out how many emails will be sent?

Or, calculate according to FirstName and LastName to get how many individuals will be emailed?

Otherwise John Smith and Tom Smith will be counted as one person/email if it's based solely on LastName's

 

[dlhappy]

OK, try it like:

SELECT
  COUNT(*) AS LastNamesCount
FROM (
  SELECT
    m.LastName
  FROM tblMembership m
  INNER JOIN (
    SELECT
      t.MembershipID,
      MAX(t.MembershipYear) AS MaxOfMembershipYear
    FROM tblMembersTrans t
    GROUP BY
      t.MembershipID
  ) mt
          ON m.MembershipID = mt.MembershipID
  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)
)
GROUP BY
  LastName
;

 

[dlhappy]

Thanks, but no, it doesn't work. The datasheet view shows one column "LastNamesCount" with the number 1 in each row, and only 28 records (there should be 170)

 

[cheekybuddha]

The joys of not testing!

Let's simplify a little since you aren't interested in the MembershipYear.

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)
);

 

[dlhappy]

Perhaps you should have a count of all the different email addresses to figure out how many emails will be sent?

Or, calculate according to FirstName and LastName to get how many individuals will be emailed?

Otherwise John Smith and Tom Smith will be counted as one person/email if it's based solely on LastName's

Hi, although counting on last name only works (not the code) in datasheet view when I click on the ribbon summation key (and correctly, in that at the bottom of the datasheet table, it shows total 170, I could not in the code select LastName and FirstName because not all records have a name in the FirstName field, because we have some organizations, i.e. libraries, a business, etc., and those names I only enter in the LastName field.
The email address, though would be unique for everyone (except our members who do not have email, in which case they are not listed in this query because the membership query used is one that only picks up members for whom we have an email address. That query picks up the those members whose email field is: <>"" And Is Not Null The non-member purchasers all have email as they use computers, etc., and make their purchases at our website, online.

 

[Pat Hartman]

This is how you get a count of the last name.

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

 

[dlhappy]

This is how you get a count of the last name.

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

Thanks. I received an error message: Syntax error in FROM clause. I tried it with and without the "." at the end of From qryMembershipUnion
and I received the same error message with or without the ending "."

This is the complete code with your addition:

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

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

Select qryMembershipUnion.LastName, Count(*) As DupeLastName
From qryMembershipUnion
ORDER BY LastName, FirstName;

 

[cheekybuddha]

@dlhappy, did you try the suggestion in Post #16?