Combining data from the same column in a query

[daved]

I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.

Tables: (only relevant fields shown)
SavingsClub - AccountNumber (PK), CustomerID, FlagAccountClosed, FlagDelete
SavingsClubAccounts - Account, Reservation, Redeemed (PK=another field)
SavingsClubReservations - Reservation (PK), FlagSpecialOrder

The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).

I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).

(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)

SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder,
Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete
FROM Suppliers
INNER JOIN (OrderDetails
INNER JOIN (((Customers
INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID)
INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account)
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation)
ON OrderDetails.Index = SavingsClubReservations.OrderID)
ON Suppliers.Index = OrderDetails.Supplier
WHERE (SavingsClubAccounts.Redeemed=False)
ORDER BY Customers.Name

I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.

SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False)
And SavingsClubAccounts.Account Not IN
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False))
UNION
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));

Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...

 

[neileg]

Yes is stored as -1 and No as 0. So if you add them up and the total isn't 0, then you have at least one Yes. Does this help?

 

[daved]

Thanks for that, Neil - it's so obvious that I hadn't thought of it and it will certainly be the way to combine the Yes/No fields once I get the query right. However, there is still the problem of checking each account against any other similar accounts before the combine can be done. I can't see any other ways of doing it other than nested loops (which you'd use if you were doing it in code) and the method I've shown in the 2nd example, which returns only the account number instead of all the fields I need.
Given your clue, maybe I can use an aggregate function? I'll have a play...