Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totaling Fields in Report slightly different (A2K)

    I also have a report which I need to count the number of occurrences based on a floor or location number. I need to count the number of disciplinary hearings based on the floor the incident happened. and print out in different boxes the results.

    For example, on the 2nd floor ([Floor]) there were 8 incidents, on the 4th floor there were 6 all in the 06/01/03 to 06/30/03 date range ([DateIncident]) field.

    Ther report needs to total the number by floor and place the number in a separate box under the column heading for the correct location. Is there a way to apply a filter to a text box along with the code? I have written some code, based on the answer given by Hans, as =Abs(Count([Floor]). This when run asks for the floor, but if I put in "02" as the response it totals all the text boxes as if the filter was to "02" I need the report to total for each floor without any input if possible. I have already restricted the data to selecting the previous month but can't figure out how to filter to the floor as well.

    Please help.

    [Floor] is a text field with two digits length. It is restricted to 02, 04, 06, 08, 10, 12, 13, 01, KI, BA for the floors in a jail that house inmates.

    Jail Administrator Medical

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

    Re: Totaling Fields in Report slightly different (A2K)

    What is the record source of your report?
    - What type (table, stored query, SQL string)?
    - If a query or SQL string, post the SQL.
    And how do you restrict it to a certain period in time?

    We will need to know this to be able to give a specific answer.

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totaling Fields in Report slightly different (A2K)

    I am using a query (qryLine1) with the date selected based on the date of incident, Floor and an expression for floor which counts the number of entries by floor between dates. Three fields to select.

    I am using one stored query, I don't want to write a different query for each box when all I need to do is to change the filter for the different floor. I don't know how to make an SQL string. I can write the query and view the SQL but how do I move that to the report? There will be 88 boxes on the report when done and I don't want to make a query for each one. I just believe access has a way to change the filter option alone for the text boxes in the report but I don't have any idea on how to do it. Maybe I am wrong.

    SELECT Count(DateSerial(Year(Date()),Month(Date()-1),0)) AS Numbers
    FROM tblViolation
    WHERE (((tblViolation.[Date of Incident]) Between DateSerial(Year(Date()),Month(Date()-1),0) And DateSerial(Year(Date()),Month(Date())-1,0)))
    GROUP BY tblViolation.Floor
    HAVING (((tblViolation.Floor)="13"))
    ORDER BY tblViolation.Floor DESC;

    I can copy and paste this into the data control source but it only lists the first line and gives me an error: Extra ) in query expression...

    I have counted, carefully, the brackets and there are the same number of opening and closing, plus I copied the expression straight from the SQL view and pasted it in.

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

    Re: Totaling Fields in Report slightly different (A2K)

    Hi JailAdmin,

    You may be able to use a crosstab query, but the situation still isn't clear to me. In the first post in this thread, you mention 10 floors (02, 04, 06, 08, 10, 12, 13, 01, KI, BA). How does this lead to 88 boxes?

    BTW, you can't just enter a SQL string into the control source of a text box.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Totaling Fields in Report slightly different (A2K)

    Maybe you could do this via a crosstab query, with rows as dates and columns as the floors.
    When creating such a query use the wizard, then base your report on this query.
    Let me know if this fits the bill.

Posting Permissions

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