jeudi 30 juin 2016

SELECT DISTINCT count() in Microsoft Access

I've created a database where we can track bugs we have raised with our developers (Table: ApplixCalls) and track any correspondence related to the logged bugs (Table: Correspondence).

I'm trying to create a count where we can see the number of bugs which have no correspondence or only correspondence from us. This should give us the visibility to see where we should be chasing our developers for updates etc.

So far I have this SQL:

SELECT DISTINCT Count(ApplixCalls.OurRef) AS CountOfOurRef
    FROM ApplixCalls LEFT JOIN Correspondence ON ApplixCalls.OurRef = Correspondence.OurRef
    HAVING (((Correspondence.OurRef) Is Null) 
        AND ((ApplixCalls.Position)<>'Closed')) 
    OR ((ApplixCalls.Position)<>'Closed') 
        AND ((Correspondence.[SBSUpdate?])=True);

I'm finding that this part is counting every occasion we have sent an update, when I need it to count 1 where OurRef is unique and it only has updates from us:

  OR ((ApplixCalls.Position)<>'Closed') 
        AND ((Correspondence.[SBSUpdate?])=True);

Hopefully that makes sense...

Is there a way around this?

Aucun commentaire:

Enregistrer un commentaire