Using counts in queries

[Wiley]

Hi,

I have the following scenario for which I cannot get the count to work
right. I know just enough Access to be dangerous, and have inherited this
design which I need to get performance metrics out of.

I have DR Details table which has DR_ID and Release Name
I have BR Details table which has BR_ID ,DR_ID and Resource_ID
I have List table- Resources which has Resource_ID and Short (which is the
resource’s short name)

DR_ID to BR_ID can be a one to many
BR_ID to Resource_ID is a one resource per BR_ID

The SQL below gives me the # of BR_ID’s per Resource_ID. However, I want to
know how many DR_ID’s with at least one BR_ID per Resource_ID. I’ve tried
putting the count on different fields and using different joins, but with no
success.

SELECT Count([DR Details].[DR_ ID]) AS [CountOfDR_ ID], [List Table -
Resources].Short, [DR Details].[Release Name]
FROM ([DR Details] RIGHT JOIN [BR Details] ON [DR Details].[DR_ ID] = [BR
Details].DR_ID) LEFT JOIN [List Table - Resources] ON [BR
Details].Resource_ID = [List Table - Resources].Resource_ID
GROUP BY [List Table - Resources].Short, [DR Details].[Release Name], [DR
Details].[Release Name]
HAVING ((([DR Details].[Release Name])=[Which Release?]) AND (([DR
Details].[Release Name])=[Which Release?]));

Pleae let me know if there is more info you need. I tried to keep it concise.

Thanks.

 

[Wiley]

I have modified the SQL that I put in my original post as it had an obvious
duplication in ht eHAVING clause. It should read:

SELECT Count([DR Details].[DR_ ID]) AS [CountOfDR_ ID], [List Table -
Resources].Short
FROM ([DR Details] RIGHT JOIN [BR Details] ON [DR Details].[DR_ ID] = [BR
Details].DR_ID) LEFT JOIN [List Table - Resources] ON [BR
Details].Accountable_Resource = [List Table - Resources].Resource_ID
GROUP BY [List Table - Resources].Short, [DR Details].[Release Name]
HAVING ((([DR Details].[Release Name])=[Whch Release?]));

 

[Wiley]

This post can be closed. I resolved it myself by using 2 queried.

I have modified the SQL that I put in my original post as it had an obvious
duplication in ht eHAVING clause. It should read:

SELECT Count([DR Details].[DR_ ID]) AS [CountOfDR_ ID], [List Table -
Resources].Short
FROM ([DR Details] RIGHT JOIN [BR Details] ON [DR Details].[DR_ ID] = [BR
Details].DR_ID) LEFT JOIN [List Table - Resources] ON [BR
Details].Accountable_Resource = [List Table - Resources].Resource_ID
GROUP BY [List Table - Resources].Short, [DR Details].[Release Name]
HAVING ((([DR Details].[Release Name])=[Whch Release?]));

Hi,

I have the following scenario for which I cannot get the count to work
right. I know just enough Access to be dangerous, and have inherited this
design which I need to get performance metrics out of.

I have DR Details table which has DR_ID and Release Name
I have BR Details table which has BR_ID ,DR_ID and Resource_ID
I have List table- Resources which has Resource_ID and Short (which is the
resource’s short name)

DR_ID to BR_ID can be a one to many
BR_ID to Resource_ID is a one resource per BR_ID

The SQL below gives me the # of BR_ID’s per Resource_ID. However, I want to
know how many DR_ID’s with at least one BR_ID per Resource_ID. I’ve tried
putting the count on different fields and using different joins, but with no
success.

SELECT Count([DR Details].[DR_ ID]) AS [CountOfDR_ ID], [List Table -
Resources].Short, [DR Details].[Release Name]
FROM ([DR Details] RIGHT JOIN [BR Details] ON [DR Details].[DR_ ID] = [BR
Details].DR_ID) LEFT JOIN [List Table - Resources] ON [BR
Details].Resource_ID = [List Table - Resources].Resource_ID
GROUP BY [List Table - Resources].Short, [DR Details].[Release Name], [DR
Details].[Release Name]
HAVING ((([DR Details].[Release Name])=[Which Release?]) AND (([DR
Details].[Release Name])=[Which Release?]));

Pleae let me know if there is more info you need. I tried to keep it concise.

Thanks.