For a little background, I am an embyrologist at a hospital in Canadaand I am (trying) to design an Access database for us to use. Our hospital wont fork over the cash so we are using MS Word for reports right now....
So, I have created several tables and forms for data entry:
What I have done so far is make the female patient ID the Primary key in the demographics table and created a one to many relationship with the IVF Cycle table (I added a patient ID field as the forigen key). In IVF Cycle, Oocyte Retrieal, Semen Prep etc etc I have defined the primary key as IVF Cycle ID and the relationship as one-to-one for all subsequent tables.
Basically, One Patient can have MANY IVF Cycles. Each IVF Cycle, however, can only have one retrival, one semen prep etc etc.
So far, that seems to make sense to me.
Now I am trying to figure out the reports. Basically what I want to do is be able to type in a cycle ID number and the report pulls all the fields from all the tables that have that Cycle ID defined so I can print off a cycle summary document. Patient name, cycle details, semen prep etc etc I have attached a blank Summary cycle form (pdf but we enter in word) so you can see what I want the report to look like.... each "boxed" section is a seperate table.
Is that possible??
Finally, one of the things I want to be able to do is run some staticsics from Access. For example, I want to be able to pull number of cycles, number of pregnances etc etc.
if you have one parent table with two child tables, each directly related to the parent ID field you will need to pull separate reports.
I.E Table 1 (Parent) ID
Table 2 (child of table 1) ID
Table 3 (Child of table 1) ID
Report 1, table 1 and 2
report 2 table 1 and table 3
i would have probably had all of the relevant data on table 2 with an extra field to store what the data meant. you could have this field look up values from another table. this approach would make it simple to query your tables and build a single report.