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

supersuit99 · #1 (**permalink**) 01-20-04, 12:37

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

M Owen · #2 (**permalink**) 01-20-04, 13:27

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 ...

supersuit99 · #3 (**permalink**) 01-20-04, 13:31

Quote:

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!

M Owen · #4 (**permalink**) 01-20-04, 13:41

Quote:

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.

hammbakka · #5 (**permalink**) 01-21-04, 02:54

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

r937 · #6 (**permalink**) 01-21-04, 06:44

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

supersuit99 · #7 (**permalink**) 01-22-04, 13:27

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

r937 · #8 (**permalink**) 01-22-04, 13:53

the query i gave you will do that

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

Trudi · #9 (**permalink**) 01-22-04, 14:04

Quote:

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

supersuit99 · #10 (**permalink**) 01-22-04, 15:24

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

r937 · #11 (**permalink**) 01-22-04, 16:34

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

supersuit99 · #12 (**permalink**) 01-23-04, 11:05

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

r937 · #13 (**permalink**) 01-23-04, 11:26

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] )

Internet Services

Web Development

Digital Marketing

Consumer Tech