Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Newbie question

  1. #1
    Join Date
    Dec 2005
    Posts
    11

    Unanswered: Newbie question

    First off, very new to Access. I’ve built a health-related database to track central venous catheter (large IV’s) insertions, removals, and infection rates within a hospital. I’m attempting to build a report that will calculate the infection rate using a specific formula: (#infections / total line days)*1000

    My report is calculating total line days correctly.

    However, not every central-line that is inserted is cultured. Of those that are cultured, not all have bacterial growth. I designed the culture result field to contain “No growth” or to list a specific organism.

    Here’s my problem: I want the total number of infections. I want my report to count ONLY the culture results that have something OTHER THAN “No growth” in the field.

    Other than that I have no idea what the expression/code would look like. Thanks in advance to anyone who can lend a hand.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm a little vague on exactly what you're doing, but one generic possibility could be:

    Sum(iif([YourField] <> "No Growth", 1, 0))
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    11
    Thanks for the advice but I received a "data type mismatch" error. I tried something similar using the "count" function but still didn't get a working calculation. Any suggestions?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What, exactly, did you put in your textbox?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2005
    Posts
    11
    The information comes from two tables set up with a combo-box:

    Table: Culture
    Field: Cx_rslt
    combo-box

    Table: Cx_rslt
    Fields: ID and Microbe
    1 No Growth
    2 S.Aureus
    3 etc, etc

    My query uses Culture.Cx_result only for this specific information. Could this be the cause of the problem? After reading similar posts here in the forums, it appears that this is a text vs integer issue . . . not that that helps me solve the problem! Thanks again.
    Last edited by ThisThingOn?; 12-15-05 at 13:36.

  6. #6
    Join Date
    Dec 2005
    Posts
    11
    Just in case I misunderstood your question, I used this for the calculation:

    Sum(iif([Cx_rslt] <> "No Growth", 1, 0))

    I tried variations using the "count" function as well.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh...

    You need to put the FIELD name in there, not the table name. How can access know what field it's supposed to look at if you don't specify? If Microbe contains the text your validating, than that's the field name that has to go in your iif() statement.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Dec 2005
    Posts
    11
    Believe me, I'm 100% sure that it's me not explaining things well...sorry.

    Cx_rslt is a field in the table "Culture" - however, when entering data into this field, I allowed only a list of options from the table "Cx_rslt".

    This sort of naming made sense at the time - it reminds me where the list of options comes from in the pull-down list.

    I made a query using fields from the table "Culture" - including the field Cx_rslt. I did NOT include the table (Cx_rslt) in the query. The table view still shows the appropriate text in the field though.

    I then used that query as the source for the report I'm trying to run. Does that make things clearer? Sorry - I bet when you see Newbie it makes you cringe every time!

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Not at all...

    Post the underlying query, we'll get you sorted.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2005
    Posts
    11
    I made this query in design view. Is this what you're looking for?

    SELECT Procedure.Proc_ID, Procedure.Cath_insDate, Cath_disposition.Cath_dispo_date, Culture.Rslt_date, Culture.Cx_rslt
    FROM ([Procedure] INNER JOIN Cath_disposition ON Procedure.Proc_ID = Cath_disposition.Proc_ID) LEFT JOIN Culture ON Procedure.Proc_ID = Culture.Proc_ID;

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yup, that's it. What kind of values are you getting for Cx_rslt?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Dec 2005
    Posts
    11
    If I understand correctly, the Cx_rslt field has text appearing (as selected from the list in the field: microbe . . . I used the Look-Up Wizard to make that association. Technically, the Cx_rslt field has "Number" listed in the type of field description). I wanted to use a pulldown list for Cx_rslt for uniformity and to prevent typos of specific microbial organism names.

    The appropriate text appears if I'm viewing tables, queries, or reports. I haven't had to join Cx_rslt and Microbe other than once with the Look-Up wizard.

    Thanks for your time on this, Teddy, I appreciate it.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Just post a couple rows of sample data from the underlying query.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I used the Look-Up Wizard to make that association
    ...not a good idea.
    I don't know why the designers of Access allow you to use a lookup as part of the table defintion but it has a habot of bolluxing queries, forms & reports.

    indeed this could be your problem
    if its not too late I'd consider taking out the lookup, replace with a referential integrity link.
    then revisit the query design.

  15. #15
    Join Date
    Dec 2005
    Posts
    11
    These are the first few rows of data from the query:

    Proc_ID...Cath_insDate...Cath_dispo_date...Rslt_da te.....Cx_rslt
    1............10/14/2005.....10/24/2005
    2............10/17/2005.....10/20/2005
    3............10/17/2005.....10/24/2005..........10/27/2005..No Growth
    4............10/17/2005.....10/25/2005
    5............10/17/2005.....10/20/2005
    6............10/19/2005.....11/2/2005............11/5/2005...Coag-Neg-Staph

    (I used periods to allign the data in the fields)

    That's disappointing to hear about the Look-up wizard. It seems like such a logical tool. Unfortunately I used it elsewhere in the database (yikes!). I'd like to try and solve this issue as is before I start changing things.

Posting Permissions

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