Report with Age groups data under each group

[jyellis]

I need my report to look like this:

Under 29	30-39	40-49	Over 50
11:30	10:29	11:00	10:01
10:09	10:01	10:33	9:45
9:45	9:50	10:01	9:01
8:45	8:52	9:44	8:41

Then either on top or bottom of report I need the average time by each age group and overall average time.

Avg time for:	Under 29	30-39	40-49	Over 50	By Dept
	avg(time)	avg(time)	avg(time)	avg(time)	overall avg

My query has 2 tables:

TblTime
KeyNumber
Time
Evaluation Date

TblProfiile
KeyNumber
Fname
Lanme
DOB


Gender

I created a query from those two tables to give me:
qryAge
Time
Age

But I am stuck on how to create the report.

[June7]

Is your Time field a text? Is that elapsed time value? Is that hours and minutes? Will it always be less than 24 hours?

Can't sum or average elapsed time in the HH:MM:SS form, must convert to decimal days or decimal hours or decimal minutes, so 9:45 is 9.75.

Assuming yes to the above questions, calculate decimal hours in your query:

DecHours: Val([Time]) + Val(Mid([Time],InStr([Time],":")+1))

Calculate the age group:

AgeGroup: Switch([Age]<30,"30 & Under", [Age]<40,"30 - 39", [Age]<50,"40 - 49", [Age]>49,"50 & Over")

Now try a CROSSTAB query.

Alternative to CROSSTAB - calculate each group field with:

[30 & Under]: IIf([Age]<30, [DecHours], Null)

[30 - 39]: IIf([Age]>29 AND [Age]<40, [DecHours], Null)

[40 - 49]: IIf([Age]>39 AND [Age]<50, [DecHours], Null)

[50 & Over]: IIf([Age]>49, [DecHours], Null)

Use report Grouping & Sorting and summary calcs in footers.