Results 1 to 7 of 7
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Filter of query


    If a table stores price changes against an inventory item. Over time there may be many prices against this inventory item whether up or down in price. Lets say the date in the t01Price table is "AdjustDate" and there is a "Purchaseprice" field. I would like to set up a query that returns one row each which is the latest price on every single inventory item. I.e. if there is a 1000 records in the table but 80 inventory items, the query must return 80 records with the latest price.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi. I used the Last Value suggestion you gave me. As you may see on the attachments there are 6 records in query q02InvPurchaseSub. The result in the first query you suggest should be 4 records since ENT000194 occur 3 times, but the result is still 6 records.
    Click image for larger version. 

Name:	1.jpg 
Views:	10 
Size:	250.2 KB 
ID:	33157Click image for larger version. 

Name:	2.jpg 
Views:	9 
Size:	202.5 KB 
ID:	33158

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Sorry my fault. It works now, Thanks.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    looks like we've all been wasting our time on this thread

    https://www.accessforums.net/showthr...608#post391608

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Both the subqueries is not updatable. I managed to get the price into the Inventory query where I want it, through expression. I would have liked to be able to join these queries with the Inventory query and use the needed fields.

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

Similar Threads

  1. Filter Query
    By romi in forum Queries
    Replies: 4
    Last Post: 12-27-2017, 02:00 AM
  2. Replies: 2
    Last Post: 02-20-2017, 11:28 PM
  3. Replies: 7
    Last Post: 05-27-2015, 12:45 PM
  4. Query, Look-up with filter?
    By CocoPuente in forum Queries
    Replies: 1
    Last Post: 10-08-2014, 11:00 PM
  5. Replies: 2
    Last Post: 01-30-2013, 07:34 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