Results 1 to 2 of 2
  1. #1
    Alexpi is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    May 2011
    Posts
    3

    Total by product

    I am new to access and trying to convert an excel model to access.

    Is it possible to create a calculated field in a query that would give the total by month for a particluar product?

    For example, Item 1 sold 35 units in January, 45 in Feb, 50 in March.

    I would want to see something like this
    Date Qty Total for Month
    1/1 20 35


    1/5 10 35
    1/31 5 35
    2/2 20 45
    2/8 15 45
    2/2 10 45
    3/1 20 50
    3/4 20 50
    3/25 10 50


    Any ideas would be greatly appreciated. Thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Would probably be more meaningful in a report. Build a report using Grouping & Sorting features with summary calcs in footers.

    Otherwise, in query, if not wanting to do a Totals but instead show record detail, will need to use DSum in an expression to construct field. Do you need this limited to a particular year?
    TotalForMonth: DSum("Qty", "tablename", "Month([Date]=" & Month([Date]) & " AND Year([Date])=" & Year([Date]))
    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. Update product parts
    By honey2wood in forum Forms
    Replies: 17
    Last Post: 04-15-2011, 02:20 AM
  2. Avoiding a cartesian product
    By johnmerlino in forum Queries
    Replies: 0
    Last Post: 10-25-2010, 07:52 AM
  3. Product / Stock Management
    By dale.90 in forum Access
    Replies: 1
    Last Post: 09-30-2010, 11:08 AM
  4. Help in generating product group and Combobox
    By lm_lopes in forum Access
    Replies: 0
    Last Post: 02-28-2010, 05:44 PM
  5. Product in a Summary Query
    By Fletch in forum Queries
    Replies: 0
    Last Post: 12-11-2008, 03:14 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