Results 1 to 7 of 7
  1. #1
    tarhim47 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    57

    Unhappy Seperating data on reports

    Hi,

    Im trying to put together a report which shows the total clients by 'type of workk.' However the problem is that for each client, the user can select multiple types of work so when i go to total the types of works, they are misleading.



    Take a look at the attached dbase. The 'client results - summary' report shows that we acquired 3 clients during the year, which is correct. However, the 'results by type of work' report shows that we acquired 6 clients during the year, which is incorrect. The reason that the report is counting 6 is b/c there are two types of work acquired for each client and it counts them individually.

    How can i get the totals on 'results by type of work' to show the correct amount of clients closed during the year - 3. Any ideas on how to fix it?

    Also, when multiple work is acquired for clients, the 'results by type of work' report shows them all together. For example, we can acquire any combination of audit, review and tax work. I want the report to show each of these types of work individually and count them that way as well. However, as youll see in the dbase, for clients with multiple work, the report groups them together and provides a total for all, not individual work. Any ideas how to go about fixing this.

    Help is greatly appreciated! Thanks!

    Thanks!

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    I see you posted this issue a little while ago, before I take a look... Has it been resolved or do you still need some help?

  3. #3
    tarhim47 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    57
    Hi Gina,

    It has not been solved. Sure could use your help!

  4. #4
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Okay, now I will download and have alook!

  5. #5
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    My observations...

    1. You cannot use...
    Code:
    =Count(*)
    ...because that is saying count ALL the records in that section.

    2. You cannot use the...
    Code:
    =Count(*)
    ...function in the Group Header and expect it to know the difference between *Open* records and *Closed* records. You might get away with...
    Code:
    =DCount("[Status]","[Lead Activity Report]",[Status]="Closed")
    ..in the query and then use the Sum() function to get the results to show on your report.

    3. I'm also a little concerned that there is only one table. Are there more tables? Perhaps you should take a look at...

    Jeff Conrad's resources page...
    http://www.accessmvp.com/JConrad/acc...resources.html
    The Access Web resources page...
    http://www.mvps.org/access/resources/index.html
    A free tutorial written by Crystal (MS Access MVP)...
    http://allenbrowne.com/casu-22.html
    MVP Allen Browne's tutorials...
    http://allenbrowne.com/links.html#Tutorials
    Sample data models...
    http://www.databasedev.co.uk/table-of-contents.html

  6. #6
    tarhim47 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    57
    Thanks for the tips.

    I used the same codes =count(*) for the other reports and it seems to work just fine. I think thats because I have the reports sorted and grouped by "status" and then "score" and then "..." (whatever else I need). So this should work just fine. How do I make the count distinctive so that it picks up totals of all different "types of work."

    As for the tables, this dbase will strictly be used to record potential clients and their respective information (as you can see from the form). Its not meant to be used in form of a retail operation where separate tables are required for customers, vendors, pricing info, etc.

    Thanks!

  7. #7
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Try using the DCount() function in my prior post... Though I have never attempted directly on the report it *might* work.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reports not pulling data correctly
    By yes sir in forum Access
    Replies: 13
    Last Post: 04-01-2011, 09:13 PM
  2. Date and data error in reports
    By indira in forum Reports
    Replies: 0
    Last Post: 03-12-2011, 03:18 PM
  3. Getting Reports to show only certain data
    By CR08RTS in forum Reports
    Replies: 3
    Last Post: 03-03-2011, 05:43 AM
  4. Using Reports to input data?
    By yes sir in forum Access
    Replies: 2
    Last Post: 09-03-2010, 10:50 AM
  5. Data Not Showing in Reports
    By Delin in forum Reports
    Replies: 1
    Last Post: 01-26-2006, 08:53 PM

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
  •  
Other Forums: Microsoft Office Forums