3 Table Report

[Guest]

I have an access database with three tables:
<pre>
[Master Table] [Drug Table] [Action Table]
*Protocol ---- Protocol ----- Protocol
Approval Date Drug Action
Author Dose Reason
Day Day
</pre>

Master Table has a primary key (*protocol) and the drug table and
action table are related to it,

Each protocol has only one author and approval date.

Each protocol could have up to 6 drugs, and 12 actions.

When I combine them with a query I seem to be getting each Drug listed
against each action, so the same drug is listed 12 times.

What I am tring to achieve in the end is a report that looks like this

<pre>
REPORT
======
Protocol (grouped by)
- DrugTable-Day (grouped by)
Drug, Dose

~ Next DrugTable-Day group

- ActionTable-Day (grouped by)
Action, Reason

~ Next ActionTable-Day group

- Author, Approval Date

~ Next Protocol group
</pre>

Can this be done?

 

[BabyATX13 via AccessMonster.com]

Sounds like a grouping issue to be addressed in the report.
You can group on Protocol, then by Drug. Group headers would be set to yes
on both
Hope this helps
KB

I have an access database with three tables:
<pre>
[Master Table] [Drug Table] [Action Table]
*Protocol ---- Protocol ----- Protocol
Approval Date Drug Action
Author Dose Reason
Day Day
</pre>

Master Table has a primary key (*protocol) and the drug table and
action table are related to it,

Each protocol has only one author and approval date.

Each protocol could have up to 6 drugs, and 12 actions.

When I combine them with a query I seem to be getting each Drug listed
against each action, so the same drug is listed 12 times.

What I am tring to achieve in the end is a report that looks like this

<pre>
REPORT
======
Protocol (grouped by)
- DrugTable-Day (grouped by)
Drug, Dose

~ Next DrugTable-Day group

- ActionTable-Day (grouped by)
Action, Reason

~ Next ActionTable-Day group

- Author, Approval Date

~ Next Protocol group
</pre>

Can this be done?

 

[Duane Hookom]

Since the Action Table isn't related directly as a child or parent of the
Drug Table, you will need to use subreports for either Drugs or Actions or
Both.