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.
The information comes from two tables set up with a combo-box:
Fields: ID and Microbe
1 No Growth
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.
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.
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!
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.
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.