Results 1 to 3 of 3
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Display total sales and this year sales to a specific customer

    Hello,

    I have a query with a list of my customers and there is a field with TOTAL SALES with that customer (sum of order items). I would like to display sales only realized this year next to it.

    I tried this:

    First column in design mode:
    TotalSales: Price


    tblOrderDetails
    Sum

    This correctly shows total sales realized with that customer.

    The second column looks like this:
    SalesThisYear: Price
    tblOrderDetails
    Sum
    Criteria: Year([tblOrders.OrderDate])=Year(Date())

    What happens is that it shows only THIS YEAR sales in both the columns. And it doesn't show customer with ZERO sales this year. How can I fix this please?

    Thanks,
    Tomas

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    take the query that has the total sales,Q1
    make a new query ,Q2,
    pull in Q1 and the Customer table, join them
    make an OUTER join: dbl-click the join line, set it to ALL RECORDS from tCustomer, some from Q1
    bring down CustomerName from both (and any other fields from Q1)
    run query.

    all customers will show, even with zero sales.

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thanks, but it didn't work for me. And how do I display the sales from this year?

    Sorry

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

Similar Threads

  1. Replies: 1
    Last Post: 02-22-2018, 11:35 PM
  2. Replies: 16
    Last Post: 12-28-2017, 02:04 PM
  3. Replies: 26
    Last Post: 11-02-2016, 10:38 AM
  4. Replies: 3
    Last Post: 12-16-2013, 01:43 PM
  5. Replies: 0
    Last Post: 12-13-2012, 03:18 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