Query Help

[derek-dc]

Alright, I have a db with three tables - tbl-emp which lists employees, tbl-pay which lists pay periods (id and rough dates) and tbl-details which records roster/availability/actual shifts worked etc (these are stored as check boxes i.e. Monday Week 1 of Pay period 1 AM has its own box, PM and NT have their own box.

Now, I need to work out which employees have available for certain shifts i.e. AM on Tuesday week 2, and alos generate a list of employees who have worked less that 'x' shifts.

I have been using the =sum formula in previous queries - making a query, then using the =sum in the report page footer to calculate the totals.

However, i cannot get these to work. Any help would be greatly appreciated.

Cheers

 

[Ziggy1]

You can't calculate in the Page Footer.

If not already present use the Report Footer ...found on "View Menu" or if you have Grouping defined open up the grouping dialoge( Sorting/Grouping also on View menu) and select the field that should have the sum look for Footer and say yes.

place your sum calc in those areas and it should work

 

[derek-dc]

thanks Ziggy, but i dont think i explained myself well.

What i need to do is calculate the sum of yes/no checkboxes on a report, but limit the records on that report by a value i.e. show all employees that worked less than 10 shifts. I need to sum the shifts, then not show the record if they work less than 10. So the 10 has to be compared with the sum value.. if that make ssense

Its kind of confusing, maybe there is a simpler way of doing it?

cheers

 

[Ziggy1]

its difficult to picture exactly what you currently have, if you could zip up and attach a sample then it would be more helpful

 

[derek-dc]

Here ya go