Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Thumbs up

    I am in the midst of creating an Access database to track and calculate the Relative Dose Intensity (RDI) for our chemotherapy patients in our Hem/Onc Clinic. I've created my tables, my input forms, and my queries. The problem I am running into is that that two of my queries, qryDI and qryPctIdealDI, are givign me 16 and 32 results, respectively, from the query instead of just 2 results (1 for each patient). I am clueless as to why (1) I am getting so many results and (2) why all but 2 are the correct results on the calculations.

    Anyone care to lend some aid or advice? I have attached the database as a zip file.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Sounds like a Many to Many Relationship between the linked tables if the number of records being generated is too many.

    Actually there are 4 key tables (Data, Drug, Patient and Regimen) and you need to look at the parent child relationship that exists between them when used together. The queries you are using have NO links between any tables or queries in the ones that are used together in the same query, and this is bound to generate extra records in the output, because it is creating a Cartesian product from the data sets. In other words if table A has 2 records and table B has 3 records and they are used together in a query with NO link line, then the query will generate 6 output rows.

    For example qryDI probably ought to have links a bit like this.

    [attachment=89628:qryDI.jpg]

    Also qryBSA needs the Patient ID in it or it cannot be linked in because the other related tables are using patient ID and qryBSA only has the patient name.

    I am also not sure that tblData is correctly structured because it has 7 Drugs and 7 Doses in it.
    This would imply that these ought to be taken of as a child table of tblData.
    Attached Images Attached Images
    Andrew

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks! I did as you illustrated, then did it in a similar fashion for the other queries and I'm now getting the right number of responses. Thanks for the insight. It is appreciated! Now the fun part, creating the reports!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •