Sorting and Grouping: Field/Expression/Calculated Control Conumdrum!!

[proximity]

Hi,

I have searched high and low for a workable solution to this and nothing has come close, so apologies for raking over old ground. Hopefully, this little conundrum might inspire a solution!

Anyway, I have a text box on a report called txtPerformance, which is a calculation based on the underlying data in the reports query.

txtPerformance does not exist in the query, therefore it does not appear in the sorting and grouping options of the report.

The calculation returns a numerical value. I want to group this report using the text box values. So, I want it to group the data and show all values between 0 and 50, then between 51 and 70 then between 71 and 100.

Any ideas? I am using an Access 2002 database.

Incidentally, if I export the report data and import it back into a table and use that as the data source, the txtPerformance item becomes a field and all my troubles are over. However, there must a better solution out there!

Thanks,

--
Steven

 

[wtmckown]

Have you considered adding a field to calculate this performance value into the report datasource query and grouping on it there?

 

[proximity]

Hi,

Yes, I have tried that technique and have used it successfully on many occasions but not this time!

 

[wtmckown]

Can you paste the query text?

 

[wtmckown]

Also can you paste the textbox calculation logic?

 

[proximity]

Hi,

Here is the SQL:

SELECT tbl_trm_data.Employee, tbl_employees.Employee_Name, tbl_trm_data.[Date Picked], IIf([tbl_trm_data.Zone]<>"REPICK",[Act Qty Picked]) AS ItemsPicked, tblSMV.SMV, Round(IIf([tbl_trm_data.Zone]<>"REPICK",[Act Qty Picked]*[SMV]),2) AS BT, tbl_trm_data.Zone, tbl_trm_data.[Total time (mins)] AS TotalTime, tbl_employees.Status, DatePart("w",[Date Picked]) AS Dy, DatePart("yyyy",[Date Picked]) AS Yr, Format([Date Picked],'ww',1,2) AS Wk
FROM (tbl_trm_data INNER JOIN tbl_employees ON tbl_trm_data.Employee = tbl_employees.EmployeeNo) INNER JOIN tblSMV ON tbl_trm_data.Zone = tblSMV.Zone
WHERE (((tbl_employees.Status)="temp") AND ((Format([Date Picked],'ww',1,2))=4))
ORDER BY tbl_employees.Employee_Name;

Here is the txtPerformance calculation:
=IIf(Sum([BT])/([txtPickMins])*100 Is Null,0,Sum([BT])/([txtPickMins])*100)

And here is the txtPickMins referred to in the above:
=IIf(Sum(IIf([ItemsPicked]>0,([TotalTime]))) Is Null,0,Sum(IIf([ItemsPicked]>0,([TotalTime]))))

--
Steven

 

[wtmckown]

Well that's a little hairy to offer a good solution without the data but sometimes the use of a report table is the best solution when you need to sort at group levels but still need the detailed data. For example: In the report open event you could populate a report table with the results of your query above (include the field for performance). Then in the same event routine set the report datasource to a query that properly groups and summarizes the data from the report table. You should be able to get the sorting/grouping that you require in this way. Remember to dump the report table contents at the beginning of the open event code so you start fresh every time.

Incidentally, I notice that you use the iif function several times without the proper number of arguments.....I'm don't see how you avoid errors when doing this. IIF(condition met, true value,false value). You use for example: IIf([ItemsPicked]>0,([TotalTime]). What does this evaluate to if ItemsPicked <=0?

 

[proximity]

I'll give your ideas a whirl! As for the IIF thing, it works exactly as I want. I have found that in some cases IIF with all arguments doesn't work and sometimes without them it does. Very bizarre, but there you go!

Thanks,

--
Steven