Repeating group in report, because of check box

[magnys]

I've got a number of records under a "group" classification in my table. A field in each record is a yes/no check box that can vary from record to record.

When wanting to identify the group in a report by the group name, and also wanting to capture if any of the records within the group contain a yes in the check box I get a duplicate of the group name. In the report one of the group names headers has a check box with a check in it representing the records containing a check, and one of the group name headers has a check box without a check in it representing the records not containing a check within the group.

Is there any way to prevent this duplication, and only display one check box that displays a check if any of the records within the group have that criteria, and no check if all the records within the group don't have the criteria?

 

[PaulBricker]

I'm not sure I completely understand what you need, but you can try this. You can set the Hide Duplicates property for your Checkbox so that only the first checkbox shows and none of the rest. Is that closer to what you want.

Sorry if I'm not understanding the issue.

Paul

 

[GoDawgs]

I would use "Min" instead of "Group By" for the check box field...the value of a checked field is -1, and the value of unchecked is 0. So if you use Min, then -1 will return if there's a checked record at any point, otherwise 0 will return.

Hope that helps.

Kevin

 

[magnys]

Thanks, but neither of those ideas work. Let me break down the explanation a little more.

My database contains assembly instructions for an automotive chassis.

In a Job Group table I have a these fields:
Process Family
Job Group
Job Group Description (A general description of what the group of processes contained within this family group does i.e. Install The Shock)

In another Process table I have these fields:
Process Family
Job Group
Process Number
Process Description (A detailed description of the processes necessary to install a part i.e. Obtain the shock... Position the shock... Loose install the shock...)
Delta (The check box field that identifies a critical operation)

The tables are linked with the Process Family+Job Group fields. A job group is a group of process numbers from the Process table. Having these in groups allows me to move bunches of processes in our assembly process without leaving any processes behind.

I have a detailed process report in which each individual process step has a Delta box in which it lets the operator know if it is a critical operation that needs to be verified. I have no problem with this report.

In my "short process report" that just contains the general job group description it pulls the Delta box info from the individual Process Numbers in the Process table. The problem occurs in that some of the Processes within a Job Group are critical operations, and some aren't.

In my report I get one line that has the Job Group Description with the Delta box checked, and another line that repeats the Job Group Description with the Delta box not checked.

I want the report to only show the Job Group Description once, and show the Delta box checked if any of the Processes within the group are critical operations, or show the box unchecked if none of the processes are critical operations.

Thanks in advance for any help!

 

[GoDawgs]

Maybe I'm missing something, but I'm pretty certain the way I suggested earlier will work. The SQL should be something like this:

SELECT Process.[Job Group], Min(Process.Delta) AS MinOfDelta
FROM Process
GROUP BY Process.[Job Group];


Let me know what I missed.

Kevin

 

[magnys]

Here is the entire SQL for the querry. As you can see it contains other information from the tables, but this information is not what I'm having problems with:

SELECT [F53-12 JPH-Operator-TBL].Line, [F53-12 JPH-Operator-TBL].Station, [F53-8 JPH-Job Group To Process-TBL].Operator, [F53-8 JPH-Job Group To Process-TBL].Order, [F53-8 JPH-Job Group To Process-TBL].[SOS Order], [F53-ALL-Job Group-TBL].[Process Family], [F53-ALL-Job Group-TBL].[Job Group], [F53-ALL-Job Group-TBL].[SOS Description], [F53-ALL-Job Group-TBL].[SOS Description Cont], [F53-ALL-Job Group-TBL].[Group Description], Sum([F53-ALL-Process-TBL].[Process Time]) AS [SumOfProcess Time], Sum([F53-ALL-Process-TBL].[Option Based Duplicated Time]) AS [SumOfOption Based Duplicated Time], Min([F53-ALL-Process-TBL].Delta) AS MinOfDelta, [F53-8 JPH-Job Group To Process-TBL].EffectiveDate, [F53-8 JPH-Job Group To Process-TBL].EndDate, [F53-ALL-Job Group-TBL].[Safety & Quality]
FROM ([F53-ALL-Job Group-TBL] INNER JOIN ([F53-12 JPH-Operator-TBL] INNER JOIN [F53-8 JPH-Job Group To Process-TBL] ON [F53-12 JPH-Operator-TBL].Operator = [F53-8 JPH-Job Group To Process-TBL].Operator) ON ([F53-ALL-Job Group-TBL].[Job Group] = [F53-8 JPH-Job Group To Process-TBL].[Job Group]) AND ([F53-ALL-Job Group-TBL].[Process Family] = [F53-8 JPH-Job Group To Process-TBL].[Process Family])) INNER JOIN [F53-ALL-Process-TBL] ON ([F53-ALL-Job Group-TBL].[Job Group] = [F53-ALL-Process-TBL].[Job Group]) AND ([F53-ALL-Job Group-TBL].[Process Family] = [F53-ALL-Process-TBL].[Process Family])
GROUP BY [F53-12 JPH-Operator-TBL].Line, [F53-12 JPH-Operator-TBL].Station, [F53-8 JPH-Job Group To Process-TBL].Operator, [F53-8 JPH-Job Group To Process-TBL].Order, [F53-8 JPH-Job Group To Process-TBL].[SOS Order], [F53-ALL-Job Group-TBL].[Process Family], [F53-ALL-Job Group-TBL].[Job Group], [F53-ALL-Job Group-TBL].[SOS Description], [F53-ALL-Job Group-TBL].[SOS Description Cont], [F53-ALL-Job Group-TBL].[Group Description], [F53-8 JPH-Job Group To Process-TBL].EffectiveDate, [F53-8 JPH-Job Group To Process-TBL].EndDate, [F53-ALL-Job Group-TBL].[Safety & Quality]
HAVING ((([F53-8 JPH-Job Group To Process-TBL].Operator)="ax-10-1e") AND (([F53-8 JPH-Job Group To Process-TBL].EffectiveDate) Is Null Or ([F53-8 JPH-Job Group To Process-TBL].EffectiveDate)<=Date()) AND (([F53-8 JPH-Job Group To Process-TBL].EndDate) Is Null Or ([F53-8 JPH-Job Group To Process-TBL].EndDate)>Date()))
ORDER BY [F53-12 JPH-Operator-TBL].Line, [F53-12 JPH-Operator-TBL].Station, [F53-8 JPH-Job Group To Process-TBL].Operator, [F53-8 JPH-Job Group To Process-TBL].[SOS Order];


When I use min all I get is all of my boxes greyed in, no check marks shown. If I enter -1 for the criteria when running the report all of the boxes are automatically checked, and just the opposite if I enter 0.