Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Obtaining greatest Y for each X (Access 2000)

    Hi

    I am creating a db that possesses 2 tables of interest: 1 table holds all items in a store (in this table, the itemID is unique). Another table, tbl_RL, holds all information about receiving logs for those items. When I join and run a select query, I am returned say 100 on 20 items. The reason is that I have received items multiple times under different receiving logs (ex. ordered item X on 2-15, reordered 3-18, reordered 5-5 > 3 records for item x). If I want the query ONLY to return the information from the row in the tbl_RL table corresponding to the latest date (ex I only want the records from the 5-5 order), how do i modify my query? I tried the Sort by Descending with Top/All = 1 on the received date field but then it only returns records for which the date is the greatest (spanning multiple items).

    Any help gurus?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Obtaining greatest Y for each X (Access 2000)

    You can do this in two steps.

    1. Create a query based on tbl_RL. Add only the ItemID and the date field to the query grid. Select View | Totals (or click the Totals button on the toolbar) to turn the query into a Totals query. Leave the Total option for ItemID as Group By, and set it to Max for the date field. Save this query as (for example) qryMaxDate.

    2. Create a query based on tbl_RL and qryMaxDate. Join them on ItemID and on the date field vs the max date (i.e. there must be two lines joining the tables). If you also need fields from the first table (holding the unique items), add it too and join it to tbl_RL on ItemID. Add al fields you want the query to display.

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtaining greatest Y for each X (Access 2000)

    Thanks Hans! Got it to work!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •