Access SQL Query where Join has no or wrong results

Question

I have some problems with a SQL statistics page, hope you can help me.

I have two tables. Table one: Customers (Sites) with their name and the machines they have. Looks like: Custname, MachineA, MachineB. Custname is text, MachineA and MachineB is a checkbox (Yes/No)

Table two: Visits (Visits) where we can add each visit we make to the customer for a machine. Looks Like: Date, Custname, Machine. Date is date of the visit, Custname the name, and in Machine we can choose A or B.

Now I want to make a query for some visit statistics per Machine (user can choose by combobox on a form). For example when I want to see statistics for machine A:

I always want to see all the customers who has machine A
For each customer I want to see the date of last visit for machine A and total visits for machine A. If there are no visits for machine A at that customer, it still has to show up (witch gives total visits for that customer: 0)

I already tried to make something like this (in this example for machine A):

SELECT Sites.Custname, Sites.MachineA, Sites.MachineB, Max(Visits.Date) AS MaxOfDate, Visits.Machine, Count(Visits.Machine) AS CountOfMachine
FROM Visits RIGHT JOIN Sites ON Visits.Custname = Sites.Custname
GROUP BY Sites.Custname, Sites.MachineA, Sites.MachineB, Visits.Machine
HAVING (((Sites.MachineA)=Yes) AND ((Visits.Machine) Is Null Or (Visits.Machine)='A'));

The problem with this query: When a customer has machine A AND B, and they have had a visit for machine B but not for machine A, it wont show up anymore in the statistics of machine A, because of the last part of the SQL:

HAVING (((Sites.MachineA)=Yes) AND ((Visits.Machine) Is Null Or (Visits.Machine)='A'));

They have Machine A, but Visits.Machine is not empty (its B)

I cant figure out how to solve this!

Oh I Use Access 2003 mode (mdb)

Hopefully you understand my issue and have some good SQL or VBA tag :)

Thanks in advance!!

Viktor Bardakov · Answer 1 · 2015-08-24T02:38:23.490

0

Try this one

    SELECT Sites.Custname, Sites.MachineA, Sites.MachineB, 
Max(IIF(Visits.Machine='A',Visits.Date,null)) AS MaxOfDate, Visits.Machine, 
sum(IIF(Visits.Machine='A',1,0)) AS CountOfMachine
    FROM Visits RIGHT JOIN Sites ON Visits.Custname = Sites.Custname)
    where Sites.Custname in (select Sites.Custname from Sites where (Sites.MachineA)=Yes)
    GROUP BY Sites.Custname, Sites.MachineA, Sites.MachineB, Visits.Machine

edited Aug 24 '15 at 02:38

answered Aug 21 '15 at 07:20

Viktor Bardakov

866
6
16

MS Access does not use the `CASE/WHEN` statement but I[IF()](http://www.techonthenet.com/access/functions/advanced/iif.php). – Parfait Aug 22 '15 at 15:50
@Parfait, Sorry, changed to IIF – Viktor Bardakov Aug 24 '15 at 02:38
Thanks a lot Viktor for helping! This one almost solved the case. Only one problem occurred: I'd like to show each Customer only once in the list. If the customer now has had a visit for machine A and for machine B, they show up twice in the list for machine A. Just one line per customer is enough :) I know you can do something with distinct, but I don't know where to put in query? – Lex Aug 25 '15 at 13:53
@Lex try to add condition to where (before group by) ... and Visits.Machine='A' ... – Viktor Bardakov Aug 26 '15 at 05:05
@Viktor. Im sorry but it didn't work. When I add like you told it will only show the customers who already has had a visit, and not the ones without a visit for machine A too. And when add DISTINCT to first row it will find DISTINCT for custname, machineA and machineB. Is there a way to distinct only Custname? – Lex Aug 27 '15 at 09:29

score 0 · Answer 2 · edited May 23 '17 at 12:29

First, use conditional statements without aggregates in WHERE clause and not HAVING. Usually, HAVING clause includes aggregates like HAVING Count(*) > 0. See WHERE vs HAVING post. Performance does not change but it is more human readable in its intention.

Consider using union queries to combine separate grouped categories. First mirrors your original and second returns customers who have machines A and B, but visited B not A. Interchange UNION with UNION ALL if you want to remove duplicates in case queries are not mutually exclusive.

  SELECT Sites.Custname, Sites.MachineA, Sites.MachineB, Max(Visits.Date) AS MaxOfDate,  
         Visits.Machine, Count(Visits.Machine) AS CountOfMachine
    FROM Visits RIGHT JOIN Sites ON Visits.Custname = Sites.Custname
   WHERE (((Sites.MachineA)=Yes) AND  
     AND ((Visits.Machine) Is Null OR (Visits.Machine)='A'))
GROUP BY Sites.Custname, Sites.MachineA, Sites.MachineB, Visits.Machine;

   UNION 

  SELECT Sites.Custname, Sites.MachineA, Sites.MachineB, Max(Visits.Date) AS MaxOfDate,  
         Visits.Machine, Count(Visits.Machine) AS CountOfMachine
    FROM Visits RIGHT JOIN Sites ON Visits.Custname = Sites.Custname
   WHERE (((Sites.MachineA)=Yes) AND ((Sites.MachineB)=Yes)  
     AND ((Visits.Machine)<>'A' AND (Visits.Machine)='B'))
GROUP BY Sites.Custname, Sites.MachineA, Sites.MachineB, Visits.Machine;

Access SQL Query where Join has no or wrong results

2 Answers2