Results 1 to 3 of 3
  1. #1
    bs0d is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    32

    Question Populate Values if Match to Date

    Assume a database with 2 tables.


    Table A - contains daily values for every date of the year.
    Table B - contains periodic values on certain days of the year.

    The two tables are joined by a productID.

    I would like to show daily data (from TableA), and include a value from TableB for the appropriate date. For the dates where no value exists in table B, show blank "" or zero 0.

    Currently, my query will repeat the value from TableB for each date of TableA (even though it doesn't actually exist for that date).
    If I join by date and Product ID, then it only display where the data exists in both tables; so only data from TableA for the dates that also have data in TableB.

    How can I do this so it does not limit my records nor repeat the value where it actually doesn't exist? Thanks

    cross posted: http://www.mrexcel.com/forum/microso...atch-date.html

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The query must be outer join (LEFT or RIGHT) not inner - "Show all records from Table A and only those from Table B that match"

    Join on both ProductID and date fields - compound join.
    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.

  3. #3
    bs0d is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    32
    Thanks, that's definitely it. Completely forgot about the join types. If you don't use it, you lose it!

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

Similar Threads

  1. Replies: 2
    Last Post: 05-11-2014, 11:48 AM
  2. Match similar values
    By enjiel in forum Queries
    Replies: 6
    Last Post: 04-09-2014, 06:44 PM
  3. Replies: 3
    Last Post: 05-20-2013, 04:18 PM
  4. Replies: 0
    Last Post: 07-26-2011, 02:03 PM
  5. Replies: 1
    Last Post: 06-20-2007, 07:26 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