Results 1 to 3 of 3
  1. #1
    sberti is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    33

    Find max 'Random' Date in tbl1 before Active Record Date in tbl2

    I'm reworking a db to make it web compatible.



    Right now I'm working on my Price and Sales tables.There are about 900 Sales records, 450 Price records (for about 45 Items).

    I have re-done my Price table with an Autonumber Key field. (It had a multi-key which I understand web db does not support.) Each autonumber key represents a Date with new Price for a Company/Item. The Price change Dates are random.

    I have put a Foreign Field in my Sales table for the Price key field.

    My dilemma is matching the Sales with the Prices.

    When the Price Date and Sales Date do not match (at least half of them don't match), I need to look back in the Price table to the max Date BEFORE the Sales Date in the Sales table for that Company/Item in order to select the correct Price key.

    Does anyone have any ideas on how I can do that? Any ideas would be welcome.

  2. #2
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    SubSelect for Max Date in a second alias of the same table

    The general subject to google is "subselect". Here's how it might look in a standard SQL subselect query. Note that, since we're using the tblPrices table twice, it has to have two different aliases.
    Code:
    SELECT 
       tS.Company, 
       tS.Itemno, 
       tS.SaleDate, 
       tP.NewPrice
    FROM 
       tblSales AS tS, 
       tblPrices AS tP
    WHERE 
      (   (tP.Company = tS.Company)
      AND (tP.ItemNo = tS.ItemNo) 
      AND (tP.PriceDate = 
               (SELECT Max(tP2.PriceDate)
                FROM tblPrices AS tP2
                WHERE 
                 (  (tP2.Company = tS.Company)
                AND (tP2.ItemNo = tS.ItemNo) 
                AND (tP2.PriceDate <= tS.SaleDate)
                  )
                )
           )
       );

  3. #3
    sberti is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    33
    Dal Jeanis,
    Thanks I'll give it a try.

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

Similar Threads

  1. Replies: 5
    Last Post: 07-05-2013, 11:16 AM
  2. Tbl1 field * tbl2 field calculation
    By davesexcel in forum Access
    Replies: 5
    Last Post: 07-01-2013, 08:44 AM
  3. Replies: 8
    Last Post: 08-16-2012, 09:04 AM
  4. Replies: 4
    Last Post: 12-02-2011, 06:52 PM
  5. Creating A Table of Random Date
    By kimimaro_kiddo in forum Access
    Replies: 1
    Last Post: 01-01-2010, 10:20 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