Results 1 to 4 of 4
  1. #1
    eclat73 is offline Novice
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    2

    how to extract totals based on a criteria

    Hi am a newbie. My database is single table. Each record has a bill no , a date , several accounting heads from which relevant heads for one record have some figures, a text field which indicates the status of the bill. The data pertains to last 9 years and is updated daily. I need to extract information on year to year basis and cumulative as on date as to what is the total amounts in the bill under various sub heads and overall. The fiscal year is 01 apr to 31 mar of next year. I know how to get the total of a column after limiting my query to a range of date. However I am not able to figure out to use this total thereafter.
    Basically I am looking at a output which is two columns one listing each year and the next one having corresponding totals either for a particular subhead or all together

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Please show us what you have tried. SQL of query(ies).

  3. #3
    eclat73 is offline Novice
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    2
    So I have a query which asks me for the start date and end date. Using this as parameters in the date field I limit the records to one fiscal year . Then in the datasheet view I add the totals row and choose the sum option in the rquired field (s). So I can run the query once for each year and note the result in the totals row. However I want to generate this output of totals matched to the year in one place

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    "output which is two columns one listing each year and the next one having corresponding totals either for a particular subhead or all together"

    Makes me think you want an aggregate query with or without filter for subhead.

    Maybe build a report and use Grouping & Sorting features with aggregate calcs in group header/footer sections
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-14-2014, 10:51 AM
  2. Totals Query - Independent criteria
    By scampbell in forum Queries
    Replies: 2
    Last Post: 11-14-2013, 03:25 PM
  3. Query - using Max Totals with Criteria
    By mslieder in forum Access
    Replies: 1
    Last Post: 06-19-2013, 06:02 AM
  4. Totals Query Record Count with Criteria
    By rmoreno in forum Queries
    Replies: 3
    Last Post: 06-07-2013, 09:16 AM
  5. Totals in a query-based report
    By babylikesburgh in forum Reports
    Replies: 4
    Last Post: 02-24-2010, 03:08 PM

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