Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    PA
    Posts
    15

    How to only show most recent record for a person with many table entries?? Help!

    Hi,

    I am importing tables from an external program and have a question. I need to pull only the most recent record from a table for a given customer. For example: the table I am importing has 4 fields: the Customer ID, the date in which the record was entered, and two numerical values. Now, everytime that the numerical values change in the other program, it makes a new record in this table. Therefore, there are numerous records for only one Customer ID. I need to say something like, "If the date in which the record was entered is the most recent, then display the 2 numerical values for that specific date and customer ID." Any ideas?

    Thanks!
    Joe

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    When you query either select only the TOP 1 or pop off the the 1st record where you order the records by entry date DESC ...

  3. #3
    Join Date
    Jan 2004
    Location
    PA
    Posts
    15
    Originally posted by M Owen
    When you query either select only the TOP 1 or pop off the the 1st record where you order the records by entry date DESC ...
    I'm afraid that I'm sort of new to Access...any chance you could give me a little more detail on how to do that? Thanks!

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by supersuit99
    I'm afraid that I'm sort of new to Access...any chance you could give me a little more detail on how to do that? Thanks!
    I was afraid you're gonna say that ... Let's see what you have for code.

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Another Suggestion:
    Import the whole table, then make a CreateTableQuery selecting only the recent records according to any date field.
    Something like that:

    SELECT * FROM
    yourImportedTable
       INTO myRecent
       WHERE (((YourAnyDateField) Between Date() And Date()-30));

    will Select all records since last month

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    latest row for each customer is a found using a correlated subquery:
    Code:
    select CustomerID
         , EnteredDate
         , Number1
         , Number2
      from yourtable XX
     where EnteredDate =
           ( select max(EnteredDate)
               from yourtable
              where CustomerID = XX.CustomerID )
    the subquery finds the latest date for each CustomerID, and the outer query selects only those rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    PA
    Posts
    15
    I've posted some screen shots of the table and the report. I can't alter the tables because they are linked to another program, and must stay that way. I need to pull the Copayment Amount and Deductible that correlates with the latest date. In the example on the screen, it would be 0 for the copayment and 1000 for the deductible, since 12/23/03 is the last date.

    Screen shots: http://joehuth.8m.com/

    Joe

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query i gave you will do that

    of course, you have to use your own column and table names, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by r937
    the query i gave you will do that

    of course, you have to use your own column and table names, eh



    Ohhhhh mannnnn.... You did it!! ... You went and used the dreaded EH word!!!

    I keep trying to tell people that we're not all like Bob and Doug up here, but you had to go and do it!! LOL

  10. #10
    Join Date
    Jan 2004
    Location
    PA
    Posts
    15
    r937,

    I forgot to mention that the four fields are coming from three different tables.

    [Chart Number] comes from the table "mwpat"
    [Date From] comes from the table "mwtrn"
    [Annual Deductible] and [Copayment Amount] both come from the table "mwcas".

    Also, where in my normal query do I put this? I know it's under Criteria, but does this go under the [Chart Number] field in the query?

    Thanks for your patience!
    Joe

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hi trudi, pleased to meetcha

    i am like bob and doug, though

    are you involved in web development and/or web standards at all? do you know what the WaSP is? see http://webstandards.TO and subscribe to our mailing list

    joe, you are not allowed to do that, pose a question by saying "the table I am importing has 4 fields" and then turn around and say it's actually three tables

    how are we supposed to know how those tables are related, and what your "normal query" looks like?

    you will have to add the subquery into the SQL View window somehow --
    Code:
    where [Date From] =
           ( select max([Date From])
               from mwtrn
              where [Chart Number]= XX.[Chart Number])
    put the correlation variable XX after the mxtrn table name in the outer query

    no guarantees this will work because i can't see how the tables are joined
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2004
    Location
    PA
    Posts
    15
    r937:

    Sorry for the misunderstanding. I posted some screen shots of my query here: http://joehuth.8m.com/

    mwpat and mwcas are connected by Chart Number
    mwcas and mwtrn are connected by Chart Number as well.

    I've tried the code you suggested above; perhaps I am doing something wrong but I can't get it to work - I'm a newbie at this.

    Thanks,
    Joe

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select P.[Chart Number]
         , T.[Date From]
         , C.[Copayment Amount]
         , C.[Annual Deductible]
      from (
           mwcas C
    inner 
      join mwpat P
        on C.[Chart Number] 
         = P.[Chart Number]
           ) 
    inner 
      join mwtrn T
        on C.[Case Number] 
         = T.[Case Number]
    order 
        by P.[Chart Number]
     where T.[Date From]
         = ( select max([Date From])
               from mwtrn
              where [Case Number]
                  = T.[Case Number] )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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