Max query not working properly

[hgus393]

Hi All,

I have a query that gets accountnumbers the account numbers are renamed and consequently they get a date stamp when the account number is changed. I only want the most recent accountnumber. I have tried using a max on the total row in an aggregate query and it is not working - it still retrieves all the accountnumber.....any clues anyone?
Bob

 

[hgus393]

And also I forgot to mention the accountnumber in the is joined to an account number table that contains all the information. Ah this is messy. I have two tables one called accountnumber that contains static information about the accountnumber, account name, last changed. The second table shows the accountnumber and the balance on that account. What I am trying to do is to do a query that gets the Accountnumber from the second table,account name from the first table, last changed from the first table and finally balance from the second table. Hope this clarifies what I mean.
Bob

 

[Brianwarnock]

First run an aggregate query on table1 group on account number and max on date.
in query2 join q1 back to table1 on date and extract all 3 fields.
in query3 join q2 to table2 to extract the balance.

This is the simple approach, maybe 2 and 3 can be joined, or subqueries used.

Brian

 

[Brianwarnock]

Sorry obviously the join in q2 should be on date and account number.

brian

 

[hgus393]

Ah thx I'll do that!
Cheers
BOb