Dynamic report generating

[sstasiak]

I have a database to record patients treatment regimens.

So, each patient has a Medical record number[MEDRECNO] in tblOncReg, which can have multiple treatment regimens[RegID] in tblRegimen. Each treatment regimen can have multiple cycles[CycleID] in tblCycle. Each cycle has multiple drugs[CycleMedID] associated with it.

This seems like it'll be pretty complicated to do, but the way I want it to look is:

Regimen 1
Cycle 1
Drug1
Drug2
.....
Cycle 2
Drug 1
Drug 2
.....
Cycle X
.....

Regimen 2
Cycle 1
Drug 1
Drug 2
.....

The number of regimens and cycles will vary, so it'll need to look at the first regimen associated with the [MEDRECNO], display the first cycle with all it's associated drugs, then drop to the next cycle and do the same thing till it reaches the last cycle for that regimen. Then at the end of that regimen, it should go to the next regimen associated with that same [MEDRECNO] and do the same series. I have no idea how to do this in a report, or if it's even possible.

Any ideas?

 

[llkhoutx]

Use appropriate Grouping in your report with the Report Wizard.

Adding grouping after the report is initially built is a pain.

 

[sstasiak]

So I got the report almost working. Right now, when I click to view the patients report, it brings up the appropriate demographic info, but then for the regimens and subsequent cycles, it outputs data from other patients in the order they were entered.

A more detailed explanation in the exact order records were entered:

I input John Smiths demographic info along with a regimen that has 3 cycles, and another regimen with only 1 cycle.

I input a new record, Robert Johnson, with demographics, and a regimen with 2 cycles

I input Jane Doe with demographics, and a regimen with 1 cycle.

Now when I view Jane Does' report, I get her correct demographics. Below that where the regimen listing starts I get John Smiths first regimen with the 3 cycles under it, then his second regimen with subsequent cycles.

After John Smith's last cycle(all in the same report) I get Jane Does' demographic info AGAIN. This is followed by Robert Johnsons regimens and cycles.

After Robert Johnsons last cycle is listed, I get Jane Does demographics for the third time, finally followed by her regimen and cycles.

I think I set something up incorrectly when I did my grouping. when I only have one test patient in the database, the report works fine. When I click the button "View Report" from within my form, can I set the filter on the "OpenReport" macro command to output only the current record instead of outputting all of them in the database?

 

[shudini]

Would you be able to be a little more specific on the primary keys and foreign keys of your tables so i could get a better picture of you data?

Thanks

 

[sstasiak]

shudini

I figured it out. On the button I use to open the form, I had the action set to "OpenReport". What I needed to do was set the "Where clause" so it opened the form with only the data for the current record being viewed.

Here's what I had for the "Where Condition":
[Forms]![OncRegMain]![Text91]=[tblOncReg]![MEDRECNO]

This takes Text91, which is my text box for my primary key[MEDRECNO] and only outputs data to the form where the value in the text box matches the PK of the table.

Before the condition statement, it was just outputting data from all records in my main table.

 

[shudini]

Glad it worked out!