Arg81
Well-known member
This may not be possible in one hit....
I want to have a grid on a form that splits my customer into it's sites, and then displays counts from another table.
Now...it sounds easy.
So far I can do the following;
That works well so I get a total count of all DWR's in the DWR table that match the CustomerSiteID.
...now what I want to do...
I have a StatusID field (where 1 = Active and 2 = Complete). I want to add these counts to the query.
For a count of Active DWRs
For a count of Complete DWRs
What I don't know how to do is put it all together.
What I want to achieve is something like;
CustomerSiteName ---- TotalDWR ---- ActiveDWR ---- CompleteDWR
CustomerSiteA ---------------10------------ 8----------------- 2
CustomerSiteB ---------------29------------ 9----------------- 20
etc etc.
Any advice greatly appriciated as always!
Regards,
I want to have a grid on a form that splits my customer into it's sites, and then displays counts from another table.
Now...it sounds easy.
So far I can do the following;
VB.NET:
SELECT CustomerSite.CustomerSiteName, COUNT(DWR.DWRNumber) AS TotalDWR
FROM CustomerSite INNER JOIN
DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID
GROUP BY CustomerSite.CustomerSiteName
That works well so I get a total count of all DWR's in the DWR table that match the CustomerSiteID.
...now what I want to do...
I have a StatusID field (where 1 = Active and 2 = Complete). I want to add these counts to the query.
For a count of Active DWRs
VB.NET:
SELECT CustomerSite.CustomerSiteName, COUNT(DWR.DWRNumber) AS ActiveDWR
FROM CustomerSite INNER JOIN
DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID
WHERE DWR.StatusID = 1
GROUP BY CustomerSite.CustomerSiteName
For a count of Complete DWRs
VB.NET:
SELECT CustomerSite.CustomerSiteName, COUNT(DWR.DWRNumber) AS CompleteDWR
FROM CustomerSite INNER JOIN
DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID
WHERE DWR.StatusID = 2
GROUP BY CustomerSite.CustomerSiteName
What I don't know how to do is put it all together.
What I want to achieve is something like;
CustomerSiteName ---- TotalDWR ---- ActiveDWR ---- CompleteDWR
CustomerSiteA ---------------10------------ 8----------------- 2
CustomerSiteB ---------------29------------ 9----------------- 20
etc etc.
Any advice greatly appriciated as always!
Regards,
Last edited: