Calculated Field in subreport

Question

TL:DR I want a whole column of SQL equivalent of excel's COUNTIFS() Function.

I'm still quite new to MS Access, but I'm quite proficient with Excel. I've inherited an Access Database that tracks reasons for delays in a large logistics group, and am trying to build a report showing which reasons come up most.

So, I can output an SQL report showing all the reasons (they're in a table called 'Reasons', so that bit's easy). What I want is a calculated field next to that column, showing how many times each reason has been cited on the [Master Data] Table (field called 'Lateness Reason') in a given date range. And for double extra bonus points, the percentage they come up would be extremely handy.

I've looked online and found COUNTIFS equivalents for a single set of criteria, but in this case I want it calculating on each row of a report. I've also tried a few things myself, but the closest I could figure was:

SELECT Reasons.Reason, Count([Master Data].[ID]) AS Num
FROM Reasons INNER JOIN [Master Data] ON Reasons.[ID] = [Master Data].[Lateness Reason]
WHERE ((([Master Data].[Lateness Reason])=[Reasons].[Reason]));

which has incorrect syntax and possibly a few other problems (that WHERE clause has to apply equally to all lines, doesn't it?). My only other option might be to do a separate calculation for each line and 'union' them together, but that is likely to cause other problems in the future if more reasons get added (and there are quite a lot already).

Firstly, Is it Possible?
Secondly, If so, How??

Many Thanks in advance

EDIT: In response to comments, table structure is as follows;

Table "Reasons" has two columns; "Reason" and "ID"
Table "Master Data" has many columns, the ones I'd be bothered about are "Date", "ID" and "Lateness Reason" (Lateness reason is equivalent to an ID from table 'Reasons')

Table Reasons

ID     Reason
___________________
1   |  Stock Shortage
2   |  Courier Problems
etc |  etc

Master Data

ID   Date          Reason
__________________
1 | 01/01/1980  |  2
2 | 03/05/2020  |  2
etc

what's your result when you remove the `WHERE` clause completely and add a `GROUP BY`? So running only `SELECT Reasons.Reason, Count([Master Data].[ID]) AS Num FROM Reasons INNER JOIN [Master Data] ON Reasons.[ID] = [Master Data].[Lateness Reason] GROUP BY Reasons.Reason` — jbud, Oct 07 '20 at 15:40
you'll probably need to post your table structure also, so you can get a complete answer — jbud, Oct 07 '20 at 15:43
Build a report and use its Sorting & Grouping feature with aggregate calcs in textboxes. This allows display of detail data as well as summary info. The percentage can also be calculated on report. Have a textbox in report header or footer with `=Count(*)` then reference that textbox in group section: `=Count(*)/[tbxCnt]`. Apply data range filter criteria to report when opening. — June7, Oct 07 '20 at 17:16
@jbud I tried your suggestion, but it's now asking for 'Reason' as a parameter when attempting to open the report. Can you explain why this would be? — Spencer Barnes, Oct 08 '20 at 13:59

score 0 · Answer 1 · edited Oct 09 '20 at 08:31

This is how I think your query should look like based on the information you provided.

SELECT Reasons.Reason, Count([Master Data].ID) AS Num
FROM Reasons INNER JOIN [Master Data] ON Reasons.ID = [Master Data].[Lateness Reason]
WHERE ((([Master Data].Date) Between [BeginDate] And [EndDate]))
GROUP BY Reasons.Reason;

Replace [BeginDate] and [EndDate] if you're using form control references.

As for the percentages based on the total, you can use text boxes as @June7 has suggested:

Use a total count in the header or footer of the report =Count(*) and then in the detail add a text box with the following control source: =[Num]/Count(*), where Num is the name of the textbox in your report which holds the counted values per reason. In this case, the control source for Num will be Num based on the query given.

Just a side note, naming your field Date is not advised since it's a reserved keyword in MS Access. It can cause unintended issues along the road.

thanks - I've changed the `Date` field to `Delivery Date` as per your advice. It now asks for ID as a parameter?? — Spencer Barnes, Oct 09 '20 at 06:40
You're probably getting the parameter popup from the report, please confirm that your report fields control sources match the query. You can also create the query separately and confirm that you're getting the correct results. Then attach it to the report and make the necessary adjustments. — jbud, Oct 09 '20 at 08:34

Calculated Field in subreport

1 Answers1