If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > How to only show most recent record for a person with many table entries?? Help!

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Grand Poobah
 
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 ...
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: PA
Posts: 15
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!
Reply With Quote
  #4 (permalink)  
Old
Grand Poobah
 
Join Date: Sep 2003
Location: MI
Posts: 3,713
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
the query i gave you will do that

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Location: Toronto, Ont. Canada
Posts: 238
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #11 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #13 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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] )
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On