Results 1 to 2 of 2
  1. #1
    markcrobinson is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    New Hampshire, US
    Posts
    31

    To use the contents of table 2 to decide what field to output

    This one has me stumped.
    I am trying to use the Data in the month field of Table 2 to determine which months projection from Table1 to export. Just not seeing the method to use in the query. As a last resort I can do it programmatically, but, because this is a vastly simplified example, would be very handy. Suggestions appreciated. THANK YOU!

    Table 1 (An SKU and monthly Projections)

    First row is fieldnames, 2nd row is data



    SKU 0119 0219 0319 0419
    Part12 15 17 19 21
    Part13 12 15 19 22
    Part15 4 8 11 12

    Table2 (The months to include in the export)

    MONTH
    0319
    0419

    Desired Output (To import projections into QB)
    0319 Part12 19
    0319 Part13 19
    0319 Part15 11
    0419 Part12 21
    0419 Part13 22
    0419 Part15 12

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Table1 (ForecastProjections) design could be designed (normalized) with fields

    SKU
    ForecastPeriod
    ForecastAmount and
    valued such as

    Part12,0119,15
    Part12,0219,17
    Part12,0319,19
    ....
    ...
    Part15,0319,11
    Part15,0419,12


    I have changed name of your Month table to tblExportPeriod (since Month is a reserved word)
    Code:
    SELECT ForecastProjections.ForecastPeriod
    , ForecastProjections.SKU
    , ForecastProjections.ForecastAmount
    FROM ForecastProjections INNER JOIN tblExportPeriod 
    ON ForecastProjections.ForecastPeriod = tblExportPeriod.exportMonth
    ORDER BY ForecastProjections.ForecastPeriod,ForecastProjections.SKU

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

Similar Threads

  1. Narrowing down field contents in table
    By Melrose in forum Access
    Replies: 1
    Last Post: 11-18-2015, 09:30 PM
  2. Replies: 6
    Last Post: 02-14-2015, 07:09 PM
  3. Replies: 1
    Last Post: 04-21-2013, 03:20 PM
  4. Replies: 5
    Last Post: 04-18-2012, 12:04 PM
  5. Replies: 0
    Last Post: 03-29-2011, 04:11 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