Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Dec 2001
    Location
    Glenelg, MD
    Posts
    2

    Unhappy Unanswered: Having problems with DCount function

    I'll try to keep this simple, here's my dilemma. I have a specific table called Support_Calls, with a field called Call_Type. There are 5 possible values for the Call_Type field. My query needs to come up with the total number of records for each call type given a user specified date. I tried to use calculated controls on a report, using the dcount function for each control. My problem is that when I parameterize my query for user supplied dates (inside a SELECT statement used as a control source), all of my calculated controls Error out. If I take out the parameterized date from the query, the calculated controls work (in other words, the dcount function works for all records but not when using the "...between [Start Date] and [End Date]... portion of the query. I tried to utilize the filter function for the parameterized date but my controls still errored out!! HELP!!

  2. #2
    Join Date
    Nov 2001
    Posts
    4
    I understand why you might want to use calculated controls in the Access report, but I try to avoid doing any calculation in an Access report. Instead, my suggestion is to use Access as the presenter for the data, which will come from a SQL statement:

    SELECT Call_Type, COUNT(*)
    FROM Support_Calls
    WHERE Call_Date BETWEEN [Start Date] AND [End Date]
    GROUP BY Call_Type;

    What this query will give you is a list of the call types, along with their counts for the date range specified. Good luck!

  3. #3
    Join Date
    Dec 2001
    Location
    Glenelg, MD
    Posts
    2
    Your SQL statement worked great... I'm kinda new at the SQL thing, but the Count function within SQL worked like a charm... thanks for taking time out of your hectic day to help!!

  4. #4
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39

    Re Support Call Query

    Quote Originally Posted by sivadycart
    I understand why you might want to use calculated controls in the Access report, but I try to avoid doing any calculation in an Access report. Instead, my suggestion is to use Access as the presenter for the data, which will come from a SQL statement:

    SELECT Call_Type, COUNT(*)
    FROM Support_Calls
    WHERE Call_Date BETWEEN [Start Date] AND [End Date]
    GROUP BY Call_Type;

    What this query will give you is a list of the call types, along with their counts for the date range specified. Good luck!
    Re the above mentioned SQL statement:

    I have just adapted this to a query of my own (newby at this), however I also have another column in my victim support access 2003 database table which is a column titled 'Letter Sent' which relies on a checkbox y/n. Is it possible to add further columns to the above SQL statement to include an additonal count of letters sent as well as support calls made between a given date range?

    Apologies for butting in here but this SQL looks like something I might be able to build on, just not sure how to do it.

    Timmeh

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,422
    Provided Answers: 10
    Timmeh,

    Could you clarify your requirements here?
    You have a boolean field (yes/no, true/false 1/0) in your table called "Letter Sent". What results would you like to achieve from this?
    At a guess, try this:
    Code:
    SELECT [Letter Sent]
         , Count(*)
    FROM   myTable
    WHERE  callDate BETWEEN [Start Date] AND [End Date]
    GROUP
        BY [Letter Sent]
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by georgev
    Timmeh,

    Could you clarify your requirements here?
    You have a boolean field (yes/no, true/false 1/0) in your table called "Letter Sent". What results would you like to achieve from this?
    At a guess, try this:
    Code:
    SELECT [Letter Sent]
         , Count(*)
    FROM   myTable
    WHERE  callDate BETWEEN [Start Date] AND [End Date]
    GROUP
        BY [Letter Sent]
    Hi Georgev,

    The table I have is called 'Treatment' and it contains several boolean fields (yes/no)'s in the one table. (I didn't even known that's what they are called )

    I would like to try to enter a date range which returns the count of how many letters were sent within the date range, but also shows how many people were contacted too (in addition) not just if the person has received a letter and has also been contacted. I would like these to be separate totals. I have been trying different count(*) queries but I am having trouble getting the count of more than just the one type of 'treatment'

    Timmeh

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,422
    Provided Answers: 10
    Boolean simply means a value of True or False. Depending on your DBMS different terms (and values that are stored) are used.

    For example MS SQL Server uses bit fields (a value of 1, 0 or NULL) to represent these data types.

    Anyhow, on to the question in hand.
    Code:
    SELECT Count(CASE WHEN [Letter Sent] = True THEN 1 ELSE 0 END) As [name1]
         , Count(CASE WHEN [Other Field] = True THEN 1 ELSE 0 END) As [name2]
    FROM   Treatment
    WHERE  callDate BETWEEN [Start Date] AND [End Date]
    Try that and let us know how you do

    Oh and see if you understand the logic - if you don't just ask!
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Thankyou for the feedback,

    In relation to the SQL statement, I am receiving a syntax error message which reads:
    syntax error (missing operator) in query expression 'Count(CASE WHEN[Letter Sent]=TRUE THEN 1 ELSE 0 END'.

    I have been getting a total to work in a report based on:
    =DCount("[Letter Sent]","Treatment1","[Letter Sent]=-1")
    which individually I can apply to the other fields i.e Phone Contact.

    This returns an overall total of 'Letter Sent' counts from the treatment table, however in my case, I have to be difficult and as you know, I'm looking to design the report to filter on that total by entering a Date range. I really haven't totally grasped the SQL language, I can see what is being achieved by others who know how to write these statements, however I am yet to develop the ability to properly construct a statement myself.

    Cheers,
    Timmeh, Timmeh, Timmeh

  9. #9
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by georgev
    Boolean simply means a value of True or False. Depending on your DBMS different terms (and values that are stored) are used.

    For example MS SQL Server uses bit fields (a value of 1, 0 or NULL) to represent these data types.

    Anyhow, on to the question in hand.
    Code:
    SELECT Count(CASE WHEN [Letter Sent] = True THEN 1 ELSE 0 END) As [name1]
         , Count(CASE WHEN [Other Field] = True THEN 1 ELSE 0 END) As [name2]
    FROM   Treatment
    WHERE  callDate BETWEEN [Start Date] AND [End Date]
    Try that and let us know how you do

    Oh and see if you understand the logic - if you don't just ask!
    I have posted a reply, it didn't work.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,422
    Provided Answers: 10
    Quote Originally Posted by Timmeh
    syntax error (missing operator) in query expression 'Count(CASE WHEN[Letter Sent]=TRUE THEN 1 ELSE 0 END'.
    You missed off the closing bracket...
    And we actually want Sum() not Count() as previously posted (sorry!)

    Try work your way through this tutorial by the w3c. It's a great lpace to start providing simple exmples etc and the starting blocks of future learning
    George
    Home | Blog

  11. #11
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by georgev
    You missed off the closing bracket...
    And we actually want Sum() not Count() as previously posted (sorry!)

    Try work your way through this tutorial by the w3c. It's a great lpace to start providing simple exmples etc and the starting blocks of future learning
    Georgev,

    I joost can't do it captain, I don't have the power. I've tried to put that closing bracket in every position and I keep getting an error. I did enjoy reading the tutorial though.

    Timmeh

  12. #12
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39

    Still Counting

    Quote Originally Posted by georgev
    You missed off the closing bracket...
    And we actually want Sum() not Count() as previously posted (sorry!)

    Try work your way through this tutorial by the w3c. It's a great lpace to start providing simple exmples etc and the starting blocks of future learning
    Hi again,

    Just wondered, with this statement:

    SELECT Treatment.CallDate, Sum(Treatment.PhoneContact) AS SumOfPhoneContact, Sum(Treatment.LetterSent) AS SumOfLetterSent
    FROM Treatment
    WHERE (((Treatment.CallDate) Between [Enter Start Date] And [Enter End Date]))
    GROUP BY Treatment.CallDate, Treatment.CallDate;

    My query ( shows results displayed as the number of "-1" fields for each calldate, if I have ticked either a phone call being made or a letter sent.
    How would I be able to display these as a single TOTAL for example to provide stats in a report of how many calls were made and how many letters were sent.


    Cheers,

    Timmeh
    Attached Files Attached Files

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,422
    Provided Answers: 10
    Hey Timmeh, I appear to have missed this topic, apologies for not responding.

    This is an interesting one now, because what we reeally want to do is Count() the number of records that match the criteria you'er asking for. However if we simply swap the Sum() for Cout) then you may notice you get a higher answer than expected, can you tell me why?

    Perhaps what is needed to use a Count() and a further filter?
    George
    Home | Blog

  14. #14
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by georgev
    Hey Timmeh, I appear to have missed this topic, apologies for not responding.

    This is an interesting one now, because what we reeally want to do is Count() the number of records that match the criteria you'er asking for. However if we simply swap the Sum() for Cout) then you may notice you get a higher answer than expected, can you tell me why?

    Perhaps what is needed to use a Count() and a further filter?
    Good to see you back Georgev,

    I thought you might have dismissed my appeals due to my very basic ability to work these things out. Does the answer have something to do with the Count(*) function counting all the records, whereas I really only want it to count particular records. Is there a need for a HAVING here?

    Timmeh

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,422
    Provided Answers: 10
    Quote Originally Posted by Timmeh
    I really only want it to count particular records
    If you only wanted to display particular records, what would you do?
    For example, I want to count all people with the surname of "Smith", what would I need to add to limit my results?
    George
    Home | Blog

Posting Permissions

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