Query on count

[hinkwale]

Hello,
I am trying to update the “151-180 days” and “180+ days” fields in table CID Template Table by getting a count of “UniqueFindingID” in the E2E_Finding_and_Recommendations Table for the same buckets in the DateRange field then separating the count for each bucket by the RiskLevel i.e High or Medium.
I have inserted an excel file with sample data. My query is returning all records for a UniqueFindingID instead of 1 for a grouping of FindingID.



Thanks

[rpeare]

I don't see any link between the two sets of data in your spreadsheet (I am assuming this data is actually in an access database and you are trying to figure out a query).

There are no dates in the E2E_Finding_and_Recommendations visible, how do you expect to measure the days overdue if there's no date field involved?

What does your original data actually look like?
What do you want your END RESULT to look like?

Just give a simple example it doesn't actually have to be based on your tables as long as the structure elements are explained (if it's coming from more than one table).

[hinkwale]

The link between the two tables is on Function. The end result is on the CID Template table which I have manually populated the counts of the UniqueFimdingID for each function by risk level and DateRange. DateRange is only criteria to be used in the E2E table in order to get the count for a UniqueFindingID and then updating the CID Template table with that count in the respective buckets by Function and Risklevel. What you see in the CID Template needs to be populated by a query.
Thanks

[rpeare]

I hope you are not updating tables with calculated information, particularly where dates are concerned

Assuming you are not willing to give more information

SELECT Function, RiskLevel FROM E2E_Finding_and_Recommendations GROUP BY Function, RiskLevel

This is an aggregate query from here you can add formulas for the remaining columns like:

Outstanding180: iif([daterange] = '180+ days', 1, 0)

Make sure the TOTALS line of the query says SUM

Same calculation for the remaining three columns you just have to modify it for the description you're using in your DATERANGE field from the query E2E_Finding_and_Recommendations

[hinkwale]

Thanks for the first part ie aggregate query but I need the count of UniqueFindingID from E2E tbl. Place a filter on the E2E tbl on Function = Finance. You then have to update the UniqueFimdingID=3 for DateRange=180+ days and 0 for 151-180 days on the CID Template table as was already manually populated

[rpeare]

then modify the formula to account for uniquefindingID being a non null value

Outstanding180: iif([daterange] = '180+ days' and not isnull([UniqueFindingID]), 1, 0)

[hinkwale]

Here's an explanation in the excel file of what i'm trying to accomplish

[hinkwale]

I came up with this query but i'm having difficulties getting to work so could you please help debug.

UPDATE [CID Template] INNER JOIN [E2E Finding and Recommendations] ON [CID Template].Function = [E2E Finding and Recommendations].CIDFn SET [CID Template].[180+ Outstanding] = (SELECT COUNT([UniqueFindingID])
FROM [E2E Finding and Recommendations]
WHERE [E2E Finding and Recommendations].[CIDFn] = [CID Template].[Function]);

I'm getting an error messsage: Operation must use an updatable query.

[rpeare]

Try this.

First create this query:

SELECT RiskLevel, Function, FindingID, DateRange
FROM <INSERT YOUR TABLE NAME HERE>
GROUP BY RiskLevel, Function, FindingID, DateRange;

This will give you a summarized list of function, risk level, daterange and finding
Save this query as qryResultPre

Then run this query:

SELECT qryResultPre.RiskLevel, qryResultPre.Function, Sum(IIf([daterange]="151-180 days",1,0)) AS 150Outstanding, Sum(IIf([daterange]="180+ days",1,0)) AS 180Outstanding
FROM qryResultPre
GROUP BY qryResultPre.RiskLevel, qryResultPre.Function;

It's the same code I gave you before in terms of figuring out the column equations you just have to summarize the data first.