Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query problem (9.0.3821 SR-1)

    I know just enough about Access to be stupid. I'm pretty decent at Excel, but I'm stumped right now. I work for a hospital ** Possible SPAM post - please alert a Moderator (2)** and I'm trying to turn a mishmash of data from a mainframe report into something usable. I downloaded the report, cleaned it up with Excel using some VB macros, and got it to this point. I have two tables that have the patient's name and ID # in common. I want to turn it into some kind of report that shows the patient's name, ID #, Lab Data, and Prescription Data.

    Table 1 has:
    Name SSN Lab Date Lab Value
    X,MISTER 999-99-9999 6/8/2001 10.00%
    X,MISTER 999-99-9999 11/1/2000 8.40%

    Table 2 has:
    Name SSN Rx # Drug Filled date Doctor
    X,MISTER 999-99-9999 1001 INSULIN NPH 6/26/2000 Y,DOCTOR
    X,MISTER 999-99-9999 1002 INSULIN NPH 10/4/2000 Z,DOCTOR
    X,MISTER 999-99-9999 1002 INSULIN NPH 2/2/2001 Y,DOCTOR
    I'm sorry I can't get this list to spread out and make the data easier to read. I'd like a report to show something like:

    X,MISTER 999-99-9999
    Rx Data Lab Data
    or
    X,MISTER 999-99-9999
    Rx Data
    Lab Data

    The problem is, if I do a simple query, I end up with six rows, each lab value matched with each Rx value. That many records would unnecessarily clutter up any report. I'm sure there's a way to do what I want but can't figure it out. Does anyone have a suggestion?Thanks, Jim Whitt
    Jim Whitt
    Pharmacist
    Temple, Texas

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (9.0.3821 SR-1)

    Sounds like you are getting a Cartesian product in your query result. Make sure you have a join line in the design grid that joins both tables on the SSN. You might also want to open both tables up in design view and index the SSN field.

  3. #3
    TracyF
    Guest

    Re: Query problem (9.0.3821 SR-1)

    I don't think that your joins are set up wrong, I think that you should be using subreports. The problem is that you have more than one record in each table associated with the same person. The database engine can't choose which record in one table matches any record in the other, so it matches them all.

    I would create a query off one of the tables with only the name and ID, then set Unique Values to YES. Build the report from this query, then add a subreport for each table. Then you will get two independent lists for each name.

    Tracy

  4. #4
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (9.0.3821 SR-1)

    Preston and Tracy, thank you both for responding. Tracy, your suggestion on using subreports will work, I believe. I ran some tests yesterday and the combined report looks like what I was wanting. Thanks again!

    Jim
    Jim Whitt
    Pharmacist
    Temple, Texas

Posting Permissions

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