Results 1 to 6 of 6
  1. #1
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255

    Counting Data

    I am currently running a pivot table report in excel but I have quite a few processes I have to run to create the data for the report. I feel Access will be able to run these processes for me much faster. I have to reassign the time to a "code" for instance any day between 0-30= 1. My excel table looks something like this:

    LOB 0-30 days 31-60 days
    Retail 5 4
    Commercial 6 2



    In Access I have created a column for each range which is: "0-30: IIF(codefordays='1', count(codefordays))"..

    This assigns a 1 next to each days <=30.

    I need to create a report that looks similar to the excel table.. Is there a way to sum each range based on LOB?

    I put this is the query section because I thought I would have to do something within the query to make the report run correctly.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Summing based on the LOB value is an aggregate totals query with a Group By clause. Build a report using Grouping & Sorting features with aggregate calcs in group header/footer section.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    That was what I thought I needed to do.. Ill just need to make the details invisible correct?

  4. #4
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    I got my table looking right... How can i get a default value of zero for any blank rows?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Yes, if you don't want to show details, set the Detail section visible property to no.

    By table do you mean query? Blanks rows where - in report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    I figured it out.. I had to make the query data insert a '0' for any value not equal to the code assigned for each range.

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

Similar Threads

  1. Counting
    By rfs in forum Forms
    Replies: 0
    Last Post: 03-15-2011, 03:20 PM
  2. Summing/Counting data in rows
    By jgelpi16 in forum Access
    Replies: 6
    Last Post: 01-20-2011, 07:24 AM
  3. Distinctive counting, can I do it?
    By Schon731 in forum Queries
    Replies: 5
    Last Post: 12-30-2010, 02:52 PM
  4. Help with counting Query
    By metalhead22 in forum Queries
    Replies: 8
    Last Post: 04-29-2009, 02:07 AM
  5. Counting distinct id's
    By jqljql in forum Access
    Replies: 1
    Last Post: 09-01-2006, 07:28 AM

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