Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Redesigning Access 2007 report

    I have an Access 2007 Db that displays how many telephone calls were sent and received from several counties. I am now trying to redesign the report to also display a breakdown of the number of calls based on which part of the work shifts they occurred. We're trying to determine when its busy and when its not busy. The Table is named: Msys_obj And all I want a person to enter to get the information is a Start and End Date. This is the SQL for that:

    PARAMETERS [Start Date] DateTime, [End Date] DateTime;
    SELECT MSys_obj.Date, MSys_obj.Location, MSys_obj.[Incoming/Outgoing], MSys_obj.Duration, MSys_obj.D, MSys_obj.A, MSys_obj.B, MSys_obj.C, MSys_obj.D, MSys_obj.E
    FROM MSys_obj
    WHERE (((MSys_obj.Date) Between [Start Date] And [End Date]));

    This is the SQL I have but I can't get it to function nor display the time break down per shifts:

    SELECT "4:30 PM TO 6:29 PM" AS TimeCalc, Count(Q.Call_ID) AS TotalCalls
    FROM Msys_obj AS Q
    WHERE Q.Time >=#4:30 PM#
    AND Q.Time <=#6:29 PM#
    UNION
    SELECT "6:30 PM TO 8:29 PM" AS TimeCalc, Count(Q.Call_ID) AS TotalCalls
    FROM Msys_obj AS Q
    WHERE Q.Time >=#6:30 PM#
    AND Q.Time <=#8:29 PM#
    UNION SELECT "8:30 PM TO 10:30 PM" AS TimeCalc, Count(Q.Call_ID) AS TotalCalls
    FROM Msys_obj AS Q
    WHERE Q.Time >=#8:30 PM#
    AND Q.Time <=#10:30 PM#;

    I just cannot get it to recognize the table or run properly.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    You will have to send a zipped database with test data so people can take a look at it.

  3. #3
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    As you can see in the Db of the attached zip file, the report and first query works fine. I can't get the second one to work and have little knowledge of how to imbed a query within a query or run a report using two queries. I just want people to be able to continue opening the report by entering a date range and seeing all the info, including the call breakdown, without having to perform any other tasks.

    Since the Db is not password protected, I had to rename the queries and table in order to confuse people who prefer to enter information directly into the table instead of using the form. It's a long story, I work with mentally disabled individuals who can't always follow orders.

    Any help???
    Attached Files Attached Files
    Last edited by Kimmy; 2014-06-08 at 23:47.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Where is Call_id in msys_obj?

    All the times in your table are am times, so the union query won't find a thing, I also changed Count(Q.Call_ID) to Count(*)
    Last edited by patt; 2014-06-09 at 03:23.

  5. #5
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Someone tried to help me design the second query and put in the Caller_Id. No, its not in the Msys_obj table, never was and I don 't know why they did it, sorry. The person helping me stopped responding.

    As for time in the table, we operate in the late afternoon and begin at 4:30 PM. Staff record information onto paper forms as they speak to callers and another group transfers the information to the database.

    I changed Caller_Id to Count(*) on my copy of Delco Warm Line as you suggested. As for time entry, I cannot use 24-hour time, workers just can't handle it.
    Last edited by Kimmy; 2014-06-09 at 09:56.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    you will have to change the times to PM then, as the times currently in the table are AM times, if you want access to take care of them, else you need to change your query to look for AM times only.

  7. The Following User Says Thank You to patt For This Useful Post:

    Kimmy (2014-06-09)

  8. #7
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I told my boss yesterday: "The answer to this SQL problem is probably looking me right in the face." It was! After reading your reply, which left me still perplexed, I took a long, hard look at the SQL and then looked at the Times field in the table. That's when I realized what you were talking about. The table just recorded the numerical time w/o the PM included; the SQL included, incorrectly, the PM! SQL now works, on to getting the additional SQL (Query1) to properly display in the report. Thanks, if I have a problem with it, I'll keep in touch! Kimmy

    BTW ... we are a non-profit organization which rehabilitates the mentally disabled and we operate off of grants.

  9. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Well done, my English is not too clear at times

  10. #9
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm still having difficulty in getting the report to display the time broken down into segments.
    I already have the Db grouped by location and the report runs perfectly.

    This is an example of the expressions I am trying to insert into the report. I don't know how to use '<' and '>' in expressions.

    =Count(IIf([Time] =>#4:30# and =<#6:29#,0))

    This is the Query SQL I am using:

    SELECT "4:30 TO 6:29" AS TimeCalc, Count(*) AS TotalCalls
    FROM Msys_obj AS Q
    WHERE Q.Time >=#4:30#
    AND Q.Time <=#6:29#
    UNION
    SELECT "6:30 TO 8:29" AS TimeCalc, Count(*) AS TotalCalls
    FROM Msys_obj AS Q
    WHERE Q.Time >=#6:30#
    AND Q.Time <=#8:29#
    UNION
    SELECT "8:30 TO 10:29" AS TimeCalc, Count(*) AS TotalCalls
    FROM Msys_obj AS Q
    WHERE Q.Time >=#8:30#
    AND Q.Time <=#10:29#
    UNION SELECT "10:30 TO 11:30" AS TimeCalc, Count(*) AS TotalCalls
    FROM Msys_obj AS Q
    WHERE Q.Time >=#10:30#
    AND Q.Time <=#11:30#;
    Last edited by Kimmy; 2014-06-18 at 22:58. Reason: Typo

  11. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    What are you trying to do with the =Count command?

    The command needs to be like:
    =Count(IIf([Time] =>#4:30# and [Time]=<#6:29#,0))

  12. #11
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    In the report, I am trying to add a new feature which takes its information from the Time column in the table (MSys_obj.

    As the posted SQL suggests, I want to break down the total count of calls within the selected date range into 4 segments: Calls from 4:30 to 6:29, Calls between 6:30 and 8:29, Calls between 8:30 and 10:29, and calls between 10:30 and 11:30.

    I have made 4 text boxes on the form in design view. In their respective Unbound boxes I tried entering =Count(IIf([Time]=>#4:30#and=<#6:29#,0)) For the first box.
    I attempted to use the Access 2007 Drag down feature to place the boxes in my report but it warns me it will delete the other query. I don't think I want to do that.

    As I mentioned, I want to have four items additionally listed on the report:

    Calls between 4:30 and 6:29 [Call count displayed here]
    Calls between 6:30 and 8:29 [Call count displayed here]
    Calls between 8:30 and 10:29 [Call count displayed here]
    Calls between 10:30 and 11:30 [Call count displayed here]

  13. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Try using Dcount rather than count

  14. #13
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I must be on the right track. When I place the following expression in my report, I get an #Error for the Calls between 4:30 AND 6:29: =DCount("=>#4:30#and=<#6:29#)","Time")

  15. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    try:
    =DCount("*", "table name or query name", "[Time]>=#4:30# and [Time]<=#6:30#")

  16. #15
    New Lounger
    Join Date
    Apr 2014
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Being a novice, some of the simplest things can-really confuse you when working in Access 2007.

    I tried every suggestion to no avail. Then, I remembered to check something I hadn't given much thought to and vala, it worked. All this time I was setting my properties to my expressions to Short Time when I should have been setting them to Standard Integer. Everything working fine now, thanks!

Page 1 of 3 123 LastLast

Tags for this Thread

Posting Permissions

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