Cross-tab help

[Opal]

I'm not sure how to proceed and was hoping to
get pointed in the right direction. I have a table
where data is collected from users on a daily
basis. Users provide a tally of unsafe activities
(of which there are 17) each day. They enter
this data by shift (day or afternoon). I want
to be able to chart this data in an Access report
using a column chart. I can't seem to get the
cross-tab query right.

The table (Access 2003) is laid out as follows:

AuditDate | Shift | Activity1 | Activity 2| ... and so on

I want the chart to show each activity (1 - 17) on the
x-axis, not in the legend and I want to be able to bring
up the data by date range selected by the user.

Everytime I try to create the chart, I get the Activities
in the legend and that's not how I want to portray the
data. Can anyone point me to a sample database
or give me some direction as to how to proceed?
Any help would be greatly appreciated. Thank you.

 

[Jerry Whittle]

The problem boils down to your basic table structure. The
Activity1 | Activity 2| ... and so on for 17 activities
will cause you all kinds of problems. You are just running into one of many.

For example what will happen to you forms, reports, and queries when you
need to add an 18th Activity? Many will not work unless you rebuild them.

What you need is are two more tables. One will be the Activities table that
lists the current 17 activites one per row plus any information about that
activity.

You also need a linking or bridging table between the other two tables as
more than one activity can happen in a day and an activity can happen on more
than one day (I'm assuming). This is known as a Many-to-Many relationship
which needs to be broken down to two One-to-Many relationships. You join the
two tables by their primary key fields in this linking table.

Yes this is rather complicated and not something readily explained. I highly
recommend getting some relational database training or reading "Database
Design for Mere Mortals" by Hernandez before proceeding any further on this
database.

 

[Opal]

Jerry,

I thought it might be the table structure that was
holding me up.....

Okay, so I have one table which lists the
17 activities in rows and a corresponding
autonumber primary key (1 - 17)

Now the users will want the simplest way to enter
their tally - that is why I set the table up the way
I did....so, I need to revisit the form set-up with
the table change.... Hmmmm

 

[John W. Vinson]

Jerry,

I thought it might be the table structure that was
holding me up.....

Okay, so I have one table which lists the
17 activities in rows and a corresponding
autonumber primary key (1 - 17)

Now the users will want the simplest way to enter
their tally - that is why I set the table up the way
I did....so, I need to revisit the form set-up with
the table change.... Hmmmm

As Jerry says you need another table: DailyActivities say. This would have
fields for UserID (who is tallying their activities), ActivityID (what
activity), ActivityDate (date/time, when the activity occurred) and a number
field (I presume) for their personal tally. Data entry would be done using a
Form for the user with a Subform based on DailyActivities, with a combo box to
select the activity. If someone had 17 activities to tally they'd enter 17
rows in the table (if they're unsafe activities I hope that will never occur!)

 

[Opal]

Unfortunately, they do occur and we need
to ensure that the auditors note them and bring
them to the attention of the employees performing
them. I will tackle this in the morning. Thank you
for the advice.