Autonumber - Help

[#1]

Hi,

I am trying to create an autonumber that groups by a reference and date.  Howerver I want to to go backwards.

Current Result:

Ref	Amount	Date	Count
ABC	100	01/01/2012	1
ABC	100	01/01/2013	2
ABC	150	01/01/2014	3
BCD	200	15/01/2013	1
BCD	225	15/01/2014	2
CDE	300	20/01/2014	1
DEF	300	06/04/2012	1
DEF	275	06/04/2013	2
EFG	200	08/08/2013	1
FGH	225	10/12/2013	1

Result Needed:

Ref	Amount	Date	Count
ABC	100	01/01/2012	3
ABC	100	01/01/2013	2
ABC	150	01/01/2014	1
BCD	200	15/01/2013	2
BCD	225	15/01/2014	1
CDE	300	20/01/2014	1
DEF	300	06/04/2012	2
DEF	275	06/04/2013	1
EFG	200	08/08/2013	1
FGH	225	10/12/2013	1

Essesntially I am trying to identify the most current line of data attached to a reference.  My view is that if I can get this to work in the load script I can then just use a where statement to select everything that has count = 1.

I have been looking through the forums and I am struggling to find anything that I can get to work.

If anyone can help or point be in the right direction I would eb very grateful.

[Gysbert_Wassenaar · Accepted solution]

All you need to do is sort by Date in descending order:

TEST2:

LOAD

Ref,

Amount,

Date,

AutoNumber(recno(),Ref) as Count

RESIDENT TEST

ORDER BY Ref, Date desc;

[#3]

Thanks so much for this.......I feel quite stupid for not knowing that.

Cheers

[Badzreyes00]

Hi,

This solution works perfectly! however, is it expected that the reload time will become significantly affected? before I applied this I load the sections I needed for less than 5mins but after applying this it took almost 40mins. So I was wondering if there is an alternative solution now? Thank you so much!