Prev: Modifying legend on graph on report
Next: Format duplicate date in report
From: Opal on 20 May 2010 16:25
So, the report is based on the Activity table?

Sorry a little confused by your response.

From: Duane Hookom on 20 May 2010 17:57
I am maybe more confused than you since I can't see your data or possibly
understand what you want.

Your earlier post with "My data looks like this" didn't have an Activity
field. Can you please explain your data, some table/query/field names, and
desired display?

--
Duane Hookom
Microsoft Access MVP


"Opal" wrote:

> So, the report is based on the Activity table?
>
> Sorry a little confused by your response.
>
> .
>
From: Opal on 21 May 2010 14:52
Okay, I hope that this helps you understand:

The activity table has 3 fields:

Indicator: Is a text field containing "Safety", "Quality", "Cost",
etc (6 indicators in all)
ActivityID: is the auto number field but no primary key assigned
Activity: Is a text field and there are 95 different activities
noted
but each classified as one of the 6 indicators above.

Indicator ActivityID Activity
Safety 267 Audit Process
Safety 193 Pedestrian separation
Quality 104 Service Fixture
.....

The hours tracking table has several fields but the ones of note are:

ID: Autonumber field with primary key assigned
InputDate: Date hours worked
RegHours: number field for regular hours worked
OTHours: number field for overtime hours worked
Activity: text field with activites drawn from the 95 above

ID InputDate RegHours OTHours Activity
1 05/01/10 2 0 Service
Fixture
2 05/02/10 0.5 0.5 Pedestrian
Separation
3 05/03/10 1 0 Service
Fixture

So the what I would want the query to return based on the above is:

Indicator Hours
Safety 1.0

Total Hours 4.0


and a pie chart with 1.0 slice for Safety out of the total 4.0 hours
worked.

Is that a little clearer?

From: Duane Hookom on 22 May 2010 00:53
My answer remains basically the same. Create a query [qselTracking] that
joins the Activity table and the tracking table so you have the fields
Indicator and TotHrs (RegHours+OTHours). Then create this query based on the
qselTracking to be used as the row source of the chart control:

SELECT Indicator, TotHrs As IndicatorHrs,
(SELECT Sum(TotHrs) FROM [qselTracking] ot
WHERE ot.Indicator <> [qselTracking].Indicator) As RemainderHrs
FROM qselTracking
GROUP BY Indicator;

Create report based on Select Distinct Indicator from the activity table.
Use the link master/child on the Indicator field.

--
Duane Hookom
MS Access MVP

"Opal" <tmwelton(a)hotmail.com> wrote in message
news:3da3dbd3-610d-44c6-b6ce-3f552bdaad8d(a)i31g2000vbt.googlegroups.com...
> Okay, I hope that this helps you understand:
>
> The activity table has 3 fields:
>
> Indicator: Is a text field containing "Safety", "Quality", "Cost",
> etc (6 indicators in all)
> ActivityID: is the auto number field but no primary key assigned
> Activity: Is a text field and there are 95 different activities
> noted
> but each classified as one of the 6 indicators above.
>
> Indicator ActivityID Activity
> Safety 267 Audit Process
> Safety 193 Pedestrian separation
> Quality 104 Service Fixture
> ....
>
> The hours tracking table has several fields but the ones of note are:
>
> ID: Autonumber field with primary key assigned
> InputDate: Date hours worked
> RegHours: number field for regular hours worked
> OTHours: number field for overtime hours worked
> Activity: text field with activites drawn from the 95 above
>
> ID InputDate RegHours OTHours Activity
> 1 05/01/10 2 0 Service
> Fixture
> 2 05/02/10 0.5 0.5 Pedestrian
> Separation
> 3 05/03/10 1 0 Service
> Fixture
>
> So the what I would want the query to return based on the above is:
>
> Indicator Hours
> Safety 1.0
>
> Total Hours 4.0
>
>
> and a pie chart with 1.0 slice for Safety out of the total 4.0 hours
> worked.
>
> Is that a little clearer?
>
From: Opal on 26 May 2010 14:35
Duane, thanks for your support....

I just went back through this and found the
cause of all my troubles.... the Hours worked
fields are "text" fields, not number fields....!?!?

I just went back to the requestor to ask why...

I hate inheriting databases

I have put this on hold until the requestor
decides why this is so and what he wants to
do.....there are other reports and charts based
on this data and I don't want to make any further
changes without their clearance first.
First  |  Prev  | 
Pages: 1 2
Prev: Modifying legend on graph on report
Next: Format duplicate date in report