Results 1 to 2 of 2
  1. #1
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    51

    Grabbing Record with a Matching Date or if no match Most Recent Match

    I have:



    tblSalesHeaders joined with tblSalesItems

    I also have

    tblCosting

    Obviously sales get entered into sales and all COGS get entered into tblCosting

    We sell commodity items so the prices are constantly changing.

    I want to create a report that shows the customer and what the customer purchased. I want to show the price the customer was charged next to the cost we paid for the item. I can't use the most recent cost because it would not be the same cost as when the item was sold say a month ago.

    I need a way to say, if in the costing table the purchase date is <= the sell date then grab the cost from the costing table and include only that cost in the query results.

    Any idea how I would do this? Keep in mind I'm far from an expert developer.

    Many many thx in advance!!!

    Jason

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use a subquery. you can adapt this aircode by changing field and table names to suit

    Code:
    SELECT *, (SELECT TOP 1 Cost FROM tblCosting WHERE tblCosting.ProductID=tblSalesItems.ProductID AND tblCosting.PurchDate<=tblSalesItem.SaleDate ORDER BY  tblCosting.PurchDate DESC) AS Cost
    FROM tblSalesItems
    You may need to add in your sales header table for the sales date

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

Similar Threads

  1. Find Match by Date Range
    By soldat452002 in forum Queries
    Replies: 2
    Last Post: 07-31-2016, 03:54 PM
  2. If no match after find first record, do stuff
    By boboivan in forum Access
    Replies: 3
    Last Post: 02-26-2016, 04:07 AM
  3. Populate Values if Match to Date
    By bs0d in forum Queries
    Replies: 2
    Last Post: 10-10-2014, 12:59 PM
  4. Replies: 16
    Last Post: 03-13-2012, 03:47 PM
  5. Match List to Master Record
    By mediaguy28 in forum Queries
    Replies: 2
    Last Post: 10-17-2011, 05:48 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