Results 1 to 3 of 3
  1. #1
    Rocky D. is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2016
    Location
    Greenville, SC
    Posts
    2

    Relationships and/or Queries

    Novice here!



    I want to compare product sales by sales rep year over year ultimately seeing which products each rep is up/down individual product sales from two years back verses this past year.

    Here's the structure of each table:
    Tbl_1 - - Rep / SKU / QtyYrBack
    Tbl_2 - - Rep / SKU / QtyYrRecent

    I don't have a Primary Key for either table.


    Here's what I need in one table:

    Tbl_YrYr - - Rep / SKU / QtyYrBack / QtyYrRecent

    Thanks!

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Put both the tables into a query. Join the two tables on Rep and SKU.

    Then in the output field list include Rep (from either table), SKU (from either table), QtyYrBack from tbl_1 and QtyYrRecent from tbl_2.

    The result wil be correct only for Reps who sold the same SKU in both years (unless you include records for cases where a quantity is 0). Post back if you need suggestions on how to handle that scenario.

  3. #3
    Rocky D. is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2016
    Location
    Greenville, SC
    Posts
    2
    Thanks for quick response and great help - It works perfectly.

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

Similar Threads

  1. Queries and relationships with Linked Tables
    By stardust1979 in forum Queries
    Replies: 3
    Last Post: 08-04-2014, 09:53 AM
  2. Replies: 5
    Last Post: 05-24-2013, 11:55 AM
  3. Replies: 5
    Last Post: 04-03-2012, 12:08 PM
  4. Tables, Relationships, and queries.
    By Andyjones in forum Access
    Replies: 2
    Last Post: 01-22-2012, 05:23 AM
  5. Creating Relationships and Queries
    By nacho in forum Database Design
    Replies: 4
    Last Post: 04-16-2010, 03:22 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