Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Posts
    101

    Unanswered: Help needed for count on report

    Hi, new to the forum but looking for help.
    i have a system in place where i type information into an access table based on source, name, tel no, date
    the source field has a 4 letter word where the info came from
    the name and tel no is the customers details and the date is when they would like a callback.
    but i need a report to count how many of the same sources we have i.e. 4 from the internet, 2 from face to face.
    can someone please help as im pulling my hair out trying to figure it out.
    every report im running is bringing back all of the data, but i need it to show the 4 letter word once and the number next to it showing how many times its in the table.
    As a bonus i would like a parameter query running to ask for a date between and to to calculate some stats.
    any help appreciated as its a nightmare at the moment.
    thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    alter the reports sorting and grouping levels..... group by source
    add a count in the groups footer... set the controls source to =count(<mycolumnname>), where <mycolumnname> is a column in the report
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2008
    Posts
    101
    Thanks for the quick reply..
    But i'm pretty new at this stuff could you explain it a little more?
    my report should look something like this based on a date i specify between and to.

    Source Total
    Inte 3
    Face 1
    Tel 6

    rather than actually showing 6 records on the report for tel seperately.

    thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok an alternative is to do what you want in SQL

    SELECT mytable.source, count(mytable.source) as NoContactsBySource
    FROM mytable
    group by mytable.source

    create a new query
    switch to the SQL view (clcik under the file mmenu item and select SQL view)

    paste the code above
    change the column names to the appropriate value in your table
    mytable.source and mytable
    the run the query
    save it
    use it wherever you need to
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2008
    Posts
    101
    heladem your a legend, thanks for that.
    one more question
    i need a parameter box showing up asking dates but for the query to not show individual dates.....because when running the query without the parameter dates it runs perfect but as soon as i modify the query and run a parameter on the dates it shows every individual record between that date and counts them as 1
    thanks
    Last edited by gmc2k2; 10-02-08 at 10:11.

  6. #6
    Join Date
    Sep 2008
    Posts
    101
    Can someone help?

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm not sure what you're asking.... tbh.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by gmc2k2
    Can someone help?
    Hi gmc2k2,

    Place this in your query and that should do it for you:

    Between [EnterBeginningDate] And [EnterEndingDate]

    Of course you base your Report on this Query, then on your form you click on the CommandButton to open the Report. Before it opens you are prompted for the date range listed above.

    have a nice one,
    BUD

Posting Permissions

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