Solved Grouping in Reports (1 Viewer)

KINGOFCHAOS17

Member
Local time
Today, 07:58
Joined
Mar 20, 2020
Messages
31
In need of some assistance with creating a report. I know what I want, but not sure where to begin. I've five fields in my table (see sample data) and I would like to group by field 'SNOW' and then check the status of field 'LINE STATUS'. So for example SNOW (or order) 1, 24, 200, and 338 at the group level would show as COMPLETE, whereas 167, 199, and 4416 would show as INCOMPLETE as either one or all of all the lines against the order have not been received from the supplier.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:58
Joined
Oct 29, 2018
Messages
21,357
Hi. You could try creating a calculated column in a query to assign a group you want to use in the report.
 

KINGOFCHAOS17

Member
Local time
Today, 07:58
Joined
Mar 20, 2020
Messages
31
Hi. So the group would be created using field SNOW, how would I create the calculation?
 

plog

Banishment Pending
Local time
Today, 02:58
Joined
May 11, 2011
Messages
11,611
You need a sub-query to look at the SNOW as a whole:

Code:
SELECT TNBA.SNOW, Max(IIf([Line Status]="Awaiting Stock","Incomplete","Complete")) AS SNOW_STATUS
FROM TNBA
GROUP BY TNBA.SNOW;

Run that and it categorizes every SNOW into a complete/incomplete status. Then use it and TNBA in a new query on which your report will be based.
 

bob fitz

AWF VIP
Local time
Today, 07:58
Joined
May 23, 2011
Messages
4,717
Hi. So the group would be created using field SNOW, how would I create the calculation?
I'm not sure that I understand the requirement but does the attached db help.
 

Attachments

  • Sample Data01.accdb
    472 KB · Views: 343

KINGOFCHAOS17

Member
Local time
Today, 07:58
Joined
Mar 20, 2020
Messages
31
You need a sub-query to look at the SNOW as a whole:

Code:
SELECT TNBA.SNOW, Max(IIf([Line Status]="Awaiting Stock","Incomplete","Complete")) AS SNOW_STATUS
FROM TNBA
GROUP BY TNBA.SNOW;

Run that and it categorizes every SNOW into a complete/incomplete status. Then use it and TNBA in a new query on which your report will be based.

Thank you, that worked perfectly, much appreciated.
 

Users who are viewing this thread

Top Bottom