Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculations in Report or Query (Access 2003)

    Edited by HansV to add <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags in order to preserve spacing.

    Hi all,
    I am trying to do a query that I didn't expect to be so difficult (or maybe it isn't).

    I have the following tables for a "Sitter" database for a hospital.
    A "Sitter is someone who sits with a patient that cannot be left alone -in addition to the care givers

    tblPatient
    -LName
    -FName
    -Suicide Precautions - y/n
    -etc....

    tblDetail
    Hours (entered daily)
    FTE (a calculation of a hours / fulltime employee equivalent hours
    Hospital Unit (7 Tower, 6 Tower etc)
    -etc...

    In a report I need:<pre> Jan Feb March April ...
    7 Tower FTE and % Suicide Hours
    6 Tower
    5 Tower</pre>


    I can get the FTE calculation for each of the [Hours] entries but the % Suicide is a y/n field.

    I can do a total of the "Yes" % Suicide by using ABS - but I need the % of hours on each floor that was for suicide precautions.
    The [Suicide] is counted by patient - and the [Hours] are entered daily on the patient in the detail so I get chekboxes with y/n.
    Thank You
    Vicky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculations in Report or Query (Access 2003)

    You'll probably need a crosstab query or two, and a report based on those. It would help if you could post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations in Report or Query (Access 2003)

    Thank you,
    I am attaching a stripped version.
    I did try Crosstab queries but I am not very good at it. I could only get one [FTEs] to appear in it.
    When I save it as a Report, it was not easily read.

    I also attached the format that was requested for the report - although they are flexible.
    I really appreciate the help - I'm becoming stagnant in the way I approach reports and am looking forward to seeing this.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculations in Report or Query (Access 2003)

    See attached version (warning: I removed items not essential to this problem).

    I created a crosstab query for the suicide percentages (I hope I dod the calculations correctly), then created a query to cmbine the two crosstab queries. Finally, I created a report based on this query.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations in Report or Query (Access 2003)

    This was so helpful.
    Thank you.
    I didn't know about combining crosstab queries and a couple of the calculations in the query was new to me as well.

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations in Report or Query (Access 2003)

    Hans,
    When looking at the report and incorporating into my database, I was trying to show the FTEs based on the hours - which works out great in the report from the -qrySuicideFTE_Crosstab.

    But, I am having trouble with the other query qrySuicidePerc_Crosstab.

    The qrySuicidePerc_Crosstab counts the number of "Yes" but counts them more than once because of more than one entry for each patient - which is okay because it was not really what I was trying to get - I may not have explained very well - although I'm glad I got a chance to see how you did it.

    The first Crosstab is great (qrySuicideFTE_Crosstab) and gives me what I need.
    The second one (qrySuicidePerc_Crosstab) should be the percent of the [Hours] that were suicide (Yes). Not the count of the "Yes".

    So, if 3T had a total of 153 Sitter [Hours] and 88 of those hours were "Yes" (Suicide) - then for each month, I need to know that 58% of the hours were suicide.
    The report should tell them that their Sitter hours add up to .9FTE and of those hours, 58% were suicide.

    I've been trying to figure out how to get this "% Hours" together with the "FTE "on the report but am not getting anywhere after 3 days <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I can do it in sub-report - but then it will list everything twice (floor, date etc).

    Thank You,
    Vicky

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculations in Report or Query (Access 2003)

    Try changing the SQL for qrySuicideCount to

    SELECT qrySuicideFTE.Floor, Format([Date],"mmm") AS M, Sum(IIf([ForSuisidePrecauions],[Hours],0)) AS SuicideCount, Sum(qrySuicideFTE.Hours) AS TotalCount
    FROM qrySuicideFTE
    GROUP BY qrySuicideFTE.Floor, Format([Date],"mmm");

    (Actually, the names SuicideCount and TotalCount are inappropriate now, since they are sums, not counts any more, but if you change them, you'll have to change qrySuicidePerc_Crosstab too.)

Posting Permissions

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