Trouble Grouping by a Maximum Value

[MellowAl]

I'm trying to produce a report where workstations are grouped by Operating System. What used to be a simple report under the old database structure is now the report from H*ll!!! I searched the Forum for an answer but didn't find any, so I'm posting for help.

In our new system, the only place to find the Operating System for each workstation is in the Audit file. The Audit file contains multiple audits for each workstation. The highest AuditID denotes the most recent audit. The Audit File has the fields WS_Num, AuditID, ComponentID and ComponentValue. Operating System is ComponentID=43.

I want to group by Operating System. To get the Operating system for any workstation, I need to find:

Maximum(AuditID, WS_Num) and ComponentID=43

I have tried umpteen ways to define my group(s). I've tried formulas and hidden groups. Either I don't get the correct grouping, or I get duplicates, or I get the error "The summary/running total field could not be created."

I also have to link to a workstation file and a dept file to get names to put in the report, but I don't think that is part of the problem.

Is there some way I can do this, either with groups or sort the Audit file and not use groups, or is this just impossible? Very frustrating!

Alice

 

[chelseatech]

Almost there.

You need a group selection formula of
{Table.AuditId} = Maximum({table.AuditID}, {Table.WS_Num})

and keep your record selection as
{Table.ComponentID}=43

You need to be a bit careful with summaries as they are calculated when the Maximum is calculated, so if you are trying to summarise anything, use a running total field as they are calculated after Group Selection. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[MellowAl]

What should I group on? Thanks for the help.

 

[MellowAl]

If I group only by ComponentValue (which is the field containing the name of the Operating System), I get the error "The summary/running total field could not be created."

If I add a group by WS_Num (and hide it), I get multiple occurrences of the workstation: once in each group where different audits of the workstation had a different Operating system.

For example, WS_Num 105 appears in the Windows 95 group and in the Windows XP group, because the earlier audit was done when that workstation had Windows 95 on it.