Weekly report

[ladybug via AccessMonster.com]

I need to create a report and have no idea how to start. If anyone can walk
me through how to set this I would be extemely greatful!

I have a table called tblDetail.
In this table are the following fields Autonumber, Date (=now), and other
fields that are not important to this question.

Then I have three other tables:
tblAdverseEvent
tblMedicalQuestion
tblProductQuality
All three of these have another Autonumber field as well as a field for the
original Autonumber from tblDetail. They also have checkboxes in each of
these three tables. On the corresponding form when the checkbox is selected
(for example Adverse Event) then the original autonumber from tbl Detail will
appear in tblAdverseEvent along with the new Autonumber for Adverse Event.

Now there is one more table that is called tblCategories. There are 5 Yes/No
fields in this table. These are called LiveAgent, IVR, WhiteMail, Web, and
Other. This table is linked to the tblDetail. Every entry from tblDetail
should have a Category selected in tblCategories.

Now I need to run a weekly report from this data. I need it to look like
this:

Adverse Event 11/04/07 11/05/07 11/06/07 11/07/07 11/08/07 11/09/07
11/10/07
LiveAgent 1 0 5 2
0 5 0
IVR 0 6 7 4
2 7 0
WhiteMail 0 2 4 0
8 5 0
Web 1 4 7 8
4 6 0
Other 0 4 6 2
7 3 1

Medical Question 11/04/07 11/05/07 11/06/07 11/07/07 11/08/07 11/09/07
11/10/07
LiveAgent 1 0 5 2
0 5 0
IVR 0 6 7 4
2 7 0
WhiteMail 0 2 4 0
8 5 0
Web 1 4 7 8
4 6 0
Other 0 4 6 2
7 3 1


Product Quality 11/04/07 11/05/07 11/06/07 11/07/07 11/08/07 11/09/07
11/10/07
LiveAgent 1 0 5 2
0 5 0
IVR 0 6 7 4
2 7 0
WhiteMail 0 2 4 0
8 5 0
Web 1 4 7 8
4 6 0
Other 0 4 6 2
7 3 1

 

[Duane Hookom]

It doesn't look like you have gotten much help yet. One possible reason is
your table structue is un-normalized and it isn't clear ho each table is
used.

I don't know why you need the three tables:
tblAdverseEvent
tblMedicalQuestion
tblProductQuality
and also why you have field names "LiveAgent, IVR, WhiteMail, Web, and
Other". These field names should be data values, not fields. Are these fields
currently in each of the three tables?

You will need to either normalize your table structure or create one or more
union queries to normalize. You can search google on "normalizing union
query" to find some discussions on this.

Once your data is normalized, you can create a crosstab query that would
display your results.