Problem with DISTINCT and GROUP BY clause (1 Viewer)

[catacaustic]

Hello all. I've got the query:

SELECT DISTINCT tblCustomer.Customer_id, tblCustomer.Customer_firstName, tblCustomer.Customer_lastName, tblTicketType.TicketType_subscriber
FROM (tblCustomer INNER JOIN tblAttendance ON tblCustomer.Customer_id = tblAttendance.Customer_id) LEFT JOIN tblTicketType ON tblAttendance.TicketType_id = tblTicketType.TicketType_id
WHERE (((tblTicketType.TicketType_subscriber)=True) AND ((Year([tblAttendance].[Attendance_date]))=2006));

and it's not working properly.

It gets the correct values fine, but I have problems where a single record in tblCustomer corresponds to two seperate records in tblTicketType that has the first record of TicketType_subscriber set to 'True' and the second to 'False'. This results in bringing up the same customer ID, etc twice in my report.

The only way that I know that I can get around this (in MySQL anyway) is to use a GROUP BY statement, that'd make it look like

SELECT DISTINCT tblCustomer.Customer_id, tblCustomer.Customer_firstName, tblCustomer.Customer_lastName, tblTicketType.TicketType_subscriber
FROM (tblCustomer INNER JOIN tblAttendance ON tblCustomer.Customer_id = tblAttendance.Customer_id) LEFT JOIN tblTicketType ON tblAttendance.TicketType_id = tblTicketType.TicketType_id
WHERE (((tblTicketType.TicketType_subscriber)=True) AND ((Year([tblAttendance].[Attendance_date]))=2006)) GROUP BY tblCustomer.Customer_id;

Unfortunately, Access won't allow a GROUP BY clause like this without every field that I'm selecting being included, and that means that because there's two distinct values for TicketType_subscriber that the record still comes up twice.

Anyone got any ideas of how to get around this?

 

[Moniker]

Instead of the WHERE statemwnt, you want to use a HAVING statment since you are using GROUP BY. Like the WHERE statement, the HAVING goes at the end of the SQL, after the GROUP BY.

Searching for the HAVING SQL statement in here or in Access help will provide examples.

 

[catacaustic]

Is it possible to use HAVING and WHERE together? What I've given there is an extremly cut-dwon version of a dynamically produced query that I have a field with around 17 different fields that could possibly be searched through. Unfortunately, the "TicketType_subscriber" is only one of those and the rest will still have be be done as a WHERE clause.

The other problem is that there are three different ways that the subscriber is taken for the search. Either as "yes there is a subscriber ticket type", "yes, there is a non-subscriber ticket type but there are no subscriber ticket type" and "there are no ticket types". The first two could be OK, but HAVING won't work when there's nothing for it to have will it?

As an example, this is what a small query looks like when I go searching for results...

SELECT DISTINCT tblCustomer.Customer_id AS id, tblCustomer.Customer_title AS title, tblCustomer.Customer_firstName AS first_name, tblCustomer.Customer_lastName AS last_name, tblCustomer.Customer_organisation AS organisation, tblCustomer.Customer_streetAddress1 AS address1, tblCustomer.Customer_streetAddress2 AS address2, tblCustomer.Customer_suburb AS suburb, tblCustomer.Customer_state AS state, tblCustomer.Customer_postCode AS postcode, tblCustomer.Customer_phoneNumber1 AS phone1, tblCustomer.Customer_phoneNumber2 AS phone2, tblCustomer.Customer_phoneNumber3 AS phone3, tblCustomer.Customer_phoneNumber4 AS phone4, tblCustomer.Customer_email AS email, tblTicketType.TicketType_subscriber AS subscriber, tblCustomer.Customer_active AS active FROM (tblCustomer LEFT JOIN tblAttendance ON tblAttendance.Customer_id = tblCustomer.Customer_id) LEFT JOIN tblTicketType ON tblTicketType.TicketType_id = tblAttendance.TicketType_id WHERE Customer_firstName LIKE '%mickey%' AND Customer_lastName LIKE '%rourke%' AND EXISTS (SELECT tblAttendance.Attendance_id FROM tblAttendance WHERE tblAttendance.Customer_id = tblCustomer.Customer_id) AND tblTicketType.TicketType_subscriber = True AND (Year(tblAttendance.Attendance_date) = 2006 OR Year(tblAttendance.Attendance_date) = 2007) ORDER BY Customer_lastname ASC, Customer_firstname ASC

 

[catacaustic]

Even with the "HAVING" set, I still can't get around the GROUP BY statement requireing that I group by tblTicketType.TicketType_subscriber and that is where the duplication issues with this query are coming into it. Not sure if that's a bit better explained or not.

 

[catacaustic]

Well, I've found what is as close to a solution as is possible here.

What I'ave had to do is set an aggregate function to choose the subscriber status, and hopefully this won't have to many bad side-effects later on down the track. This lets me group by the single table and not need ot worry about being required to group by a column that shouldn't be aprt of the GROUB BY statement.

It's a pretty poor implementation by Access to require queries to be grouped by everything. I can understand that GROUP BY should require some key, but most of the columns that I was forced to use in the grouping statement are merely information data and have nothing do do with a primary or even a foreign key.