Results 1 to 3 of 3
  1. #1
    marwlo is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    1

    Unhappy Unique values

    Hi All

    I'm trying to connect two tables ( in reality there are more than 100k rows, this is just example):
    1st table is a table with the budget for each product


    2nd table is a table with the cost by the product (price may vary for the same product category as we don't specify the brand, if discount applies etc).

    I created relationship between the tables based on Product name. Then, once query is created I would like to extract the data to Excel and create pivot table showing total spend per product in 2016 vs 2016 budget.
    Unfortunately Access is multiplying budget x no of products in the report. So for "Pen_red" final budget in the output file is 850 instead of 50. I was trying to select "unique values" or modify the pivot using eg. average but it doesn't work.
    Could you please help me with building proper query? Thank you

    1st table:
    Product Budget 2016
    Pen_red 50
    Pen_green 45

    2nd table:
    Business Area Product Cost 2016
    Pens Pen_red 10
    Pens Pen_green 5
    Pens Pen_green 6
    Pens Pen_red 17

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the nature of the 2 tables can produce duplicates, (even tho they are not)
    To reduce the records to single values, run a sum on the numeric field.
    or
    remove uneeded extraneous fields that produce the duplicate because the extra field creates a unique record.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    a query something like

    Code:
    select businessarea, product, budget, sum(cost) as costs
    from table1 inner join table2 on table1.product=table2.product
    group by businessarea, product, budget

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

Similar Threads

  1. Unique Values in Query
    By genedi in forum Access
    Replies: 2
    Last Post: 09-09-2015, 04:51 PM
  2. Sum of double values with unique ID
    By javeko in forum Access
    Replies: 1
    Last Post: 03-06-2014, 03:01 AM
  3. Unique values
    By helpaccess in forum Queries
    Replies: 3
    Last Post: 09-19-2011, 03:46 PM
  4. Creating a unique ID for a new set of values
    By slaterino in forum Programming
    Replies: 1
    Last Post: 08-24-2010, 09:35 AM
  5. Count of Unique Values
    By vsmk in forum Queries
    Replies: 2
    Last Post: 03-14-2010, 12:07 AM

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