Solved Grouping in Reports

[KINGOFCHAOS17]

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]

Hi. You could try creating a calculated column in a query to assign a group you want to use in the report.

 

[KINGOFCHAOS17]

Hi. So the group would be created using field SNOW, how would I create the calculation?

 

[theDBguy]

Hi. So the group would be created using field SNOW, how would I create the calculation?

I was thinking of using DCount(), although that would be a little slow.

 

[plog]

You need a sub-query to look at the SNOW as a whole:

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]

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.

 

[KINGOFCHAOS17]

You need a sub-query to look at the SNOW as a whole:

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.