Most recent record for each ID

[KingKea]

There are ton of ways to do this, but I am having difficulty find a way the I can marry with existing queries
that validate the data.

I need to find the most recent change for any customer ID
and then query those records for specific conditions.

I have a table with
Change_no (autonumber field),Customer_id,address,City,State,
Zipcode,phonenumber,Change_type,Change_date

in some cases I will need to filter based change_type and
in other tables I will need to merge fields to create a distinct customer_id.

 

[gmmastros]

Your question isn't very clear (to me at least). I think that if you post some sample data with expected results, we may be able to help a little better.

Do not post real names, address, or any other personal info. Instead, make up some dummy records.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I think something like this is what you are looking for...

[COLOR=blue]Select[/color] A.*
[COLOR=blue]From[/color]   [!]TableName[/!] As A
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] Customer_id, [COLOR=#FF00FF]Max[/color](Change_No) [COLOR=blue]As[/color] Change_No
         [COLOR=blue]From[/color]   [!]TableName[/!]
         [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Customer_id
         ) [COLOR=blue]As[/color] B
         [COLOR=blue]On[/color]  A.Customer_Id = B.Customer_Id
         And A.Change_No = B.Change_No

Let me know if this works for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[KingKea]

Thanks - I think that may do the trick.

Sorry about the wording of the original message, I was trying to limit the amount of information that was disclosed.