Formula???

[markham]

Hi everyone....

This isn't for me but for a friend... really.

One table that reads:

Dept.
DayIn
DayOut
# of days (day in - day out)

Question is: A query that will create field names that read:

dept. 0 days 1-3 days 4-7 days >7

....looks up the # of days and counts them under the corresponding field.

Can anybody help with this.... thanks a lot.

 

[Andrew Fergus]

Hi

You can do this with a crosstab query - make a new crosstab query, add the table, add the department and make it the "row heading" (must have at least one row heading) of the crosstab query. Add the NumDays field (being the difference between the Day In and Day Out fields), change 'Group By' to 'Count', make it the "value" field of the crosstab query and change the field from "NumDays" (or whatever it is called) to "Incidences : nz([NumDays])" (without the quotes). Add the following expression into the next available field :

ColHeading: IIf([NumDays]=0 Or IsNull([NumDays]),0,IIf([NumDays]<4,3,IIf([NumDays]<8,7,8)))

make it the column heading, and leave 'Group by' as is. Right click this field and select properties, change the column headings to 0,3,7,8. Save and run.

The SQL for the query looks like this :

TRANSFORM Count( nz([NumDays])) AS Incidences
SELECT MyTableName.Dept
FROM MyTableName
GROUP BY MyTableName.Dept
PIVOT IIf([NumDays]=0 Or IsNull([NumDays]), 0, IIf([NumDays]<4, 3, IIf([NumDays]<8, 7, 8))) In (0,3,7,8);

This won't give you the exact column headings you are seeking but they will be pretty close, plus if you are using these values on a report, then you can change the column headings to whatever you like.

HTH, Andrew.

 

[Norie]

Why is there a column in the table for no of days?

This calculation should really be done in a query.

You could then use Andrew's idea but based on the query rather than the table.