Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Rickmansworth, Hertfordshire, United Kingdom
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2003: query criteria need refining

    I have an 'Addresses' table and a 'Categories' table in which people are classified as, eg, 'member', ' past ticket purchaser'. I have a query that pulls out email addresses for both these categories which often results in duplicate email addresses as a person may be both a member and a past ticket purchaser. I need to refine the query to eliminate returns from the 'past ticket purchaser' criterion when a matching return also occurs due to the 'member' criterion. Advice needed please.

    The SQL of the query reads:
    SELECT Categories.ID, Categories.Title, Categories.Forenames, Categories.Surname, Addresses.Email, Categories.Category, Categories.Organisation
    FROM Addresses LEFT JOIN Categories ON Addresses.ID = Categories.ID
    WHERE (((Addresses.Email) Is Not Null) AND ((Categories.Category)="Past ticket purchaser" Or (Categories.Category)="member"))
    ORDER BY Categories.Surname;

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Something doesn't seem right with your query. You show that the Title, Forename, Surname, and Organisation fields are in the Category table. Really?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Rickmansworth, Hertfordshire, United Kingdom
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've changed the source of Title, Forename, Surname, and Organisation fields to the Addresses table. The query still runs and gives the same number of hits.
    SQL now reads:
    SELECT Addresses.Title, Addresses.Forenames, Addresses.Surname, Addresses.Email, Categories.Category
    FROM Addresses LEFT JOIN Categories ON Addresses.ID = Categories.ID
    WHERE (((Addresses.Email) Is Not Null) AND ((Categories.Category)="Past ticket purchaser" Or (Categories.Category)="member"))
    ORDER BY Addresses.Surname;

    To recap on what I'm trying to achieve: I want to eliminate returns due to 'member' where there's a return for the same person under 'past ticket purchaser'.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    Add a GROUP BY Addresses.Email

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    As caveman144 says, group by something. Access has a rule that every field in a grouped query must be either an aggregate function or in the group by clause:
    Code:
    SELECT Addresses.Title, Addresses.Forenames, Addresses.Surname, Addresses.Email, Min(Categories.Category)
    FROM Addresses LEFT JOIN Categories ON Addresses.ID = Categories.ID
    WHERE (((Addresses.Email) Is Not Null) AND ((Categories.Category)="Past ticket purchaser" Or (Categories.Category)="member"))
    ORDER BY Addresses.Surname;
    GROUP BY Addresses.Title, Addresses.Forenames, Addresses.Surname, Addresses.Email
    The above should also prioritise "Member" above "Ticket Purchaser" because it is earlier in alphabetical order (min())

    In the visual editor you get a group by clause by enabling the "Totals" (sigma) button on the ribbon. In that view you must have the category field in twice, one shown with the Min function and the other not shown with Where and the criteria. Every other field is in the group by.

    And you really must stick with the personal data in the addresses table (or better another linked "person" table), not the categories table, else some very bad things can happen down the road with wrong names etc.

  6. #6
    Star Lounger
    Join Date
    Dec 2001
    Location
    Rickmansworth, Hertfordshire, United Kingdom
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That did it. Many thanks Ian. I added another field from the Addresses table 'no email' and used the visual editor. The generated SQL reads:

    SELECT DISTINCT Addresses.ID, Addresses.Title, Addresses.Forenames, Addresses.Surname, Addresses.Email, Min(Categories.Category) AS MinOfCategory, Addresses.[No email]
    FROM Addresses LEFT JOIN Categories ON Addresses.ID = Categories.ID
    GROUP BY Addresses.ID, Addresses.Title, Addresses.Forenames, Addresses.Surname, Addresses.Email, Addresses.[No email]
    HAVING (((Addresses.Email) Is Not Null) AND ((Min(Categories.Category))="Past ticket purchaser") AND ((Addresses.[No email])=No))
    ORDER BY Addresses.Surname;

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Are you sure that works?

    The SQL now has no "Where" clause but instead has a "Having" clause. A "Where" clause controls which records are pulled out of the database. Those records are then put into groups where all the "Group By" fields are equal and the other fields are then aggregated according to the aggregate functions (e.g. MIN). That gives a new (smaller) set of records which are then filtered by the "Having" clause.

    Generally Access generates a "Having" clause when you put criteria against fields that are included in the result - i.e. the group by fields and the aggregated fields. To instead (or additionally) generate a "Where" clause you have to add the fields in additional columns, untick the "Show" box, and select "Where" in the totals row.

    Without replicating your database and testing, it looks to me as if your query will miss a load of people. The query will initially pull all the data out of the database (no "Where"), then "Having" will filter the results to just a subset of people. Those who have an email address ((Addresses.Email) Is Not Null), aren't flagged as no email ((Addresses.[No email])=No), and importantly are not members ((Min(Categories.Category))="Past ticket purchaser"). If they were members that MIN would yield "member" (because "m"<"p") and they would be excluded. Was that what you wanted?

    My SQL example should do what I thought you wanted, except for excluding people with no email.

    Ian.

  8. #8
    Star Lounger
    Join Date
    Dec 2001
    Location
    Rickmansworth, Hertfordshire, United Kingdom
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The SQL I posted equates to the design view which I created from your instructions at #5. I need to exclude returns where there are email addresses for a past ticket purchaser and member with the same ID ('email addresses for members' is the subject of a separate query).

    When I exchanged my SQL with yours I got an error message 'The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect'. I'm just out of my comfort zone here so couldn't troubleshoot the error.

    I'm happy with the result I'm getting - no member emails are shown and none of the past ticket purchasers returned are members. The St Albans International Organ Festival Society in the UK is very grateful for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •