Need to get the first record from the third group

[reacha]

In my report i have group by username,ScannedDate,AccountNumber.

So i need to get the only one account number per user that is oldest account associated with that user

for EX:

Username ScannedDate AccountNumber
--------- ----------- -------------
tom 11/24/2010 1000

George 11/6/2008 2000
4/15/2009 2001
5/28/2009 2002

peter 11/10/2010 3000
11/15/2010 3010

Output should be:
------------------

tom 11/24/2010 1000
George 11/6/2008 2000
peter 11/10/2010 3000

Please help me out!!

Thanks,
reacha

 

[MCuthill]

reacha,

In your example, the lowest account number happens to correlate to the earliest date - is this always the case?

Or could data appear as follows:
George -- 11/06/2008 -- 2001
George -- 04/15/2009 -- 2000
George -- 05/28/2009 -- 2002

I would think a formula that evaluates the current record scanned date to a "current minimum" acanned date stored and then house the related account number if a minimum date is found would be the approach to take if the latter is true.

Please advise and I am certain a solution can be ciphered out.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

what about sorting by user then by date?
display the info in the header and suppress the details

 

[reacha]

Actually i have first grouped by user than by scandate and then by account number

if a user has 3 dates and 3 account number correspondence to that 3 dates..

i need to get the account number of the oldest date

means i grouped the date by ascending order the oldest date comes first i need to get the account number correponding to that oldest date

Thanks,
reacha

 

[MCuthill]

reacha,

If you already have sorted as Fishermacse has suggested, simply place your "Account Number" field in the "User" group level... in my test report, it returned the account number which appeared first within the group (which of course, had the earliest date).

I think Fishermacse's solution should work.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[reacha]

No i need the username plus oldest date and the account number associated with that date and all others need to be supressed

Username ScannedDate AccountNumber
--------- ----------- -------------
tom 11/24/2010 1000

George 11/6/2008 2000
4/15/2009 2001
5/28/2009 2002

peter 11/10/2010 3000
11/15/2010 3010

Output should be:
------------------

tom 11/24/2010 1000
George 11/6/2008 2000
peter 11/10/2010 3000


like in the above ex last 2 records for george needs to be supressed and last record for peter needs to be supressed

Thanks,
reacha

Thanks,
reacha

 

[MCuthill]

reacha,

I have setup a report as follows:

Group 1: User
Group 2: Scanned Date (suppressed)
Details: (supressed)

Sort: by Account Number (Ascending)

In the Group 1 Header, I have the following fields:
[User], [Account Number], & [Scanned Date]

The report returns what you have displayed above (using your examples as test data). What results are you getting when following this method?

Please advise, hopefully get this one sorted out for you.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[reacha]

Thanks very much it worked for me....


I have grouped by user,Scanneddate,accountnumber.

Placed the group headers of scanneddate and accountnumber in the groupheader of user and suppressed everything


Thanks,
reacha

 

[MCuthill]

Props to Fisheromacse on this one!

I was going to make it far more complex than needed had I gone down the path of coding a "running" comparative formula.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

MCuthill, don't you start doing that, that is what i end up doing most of the time....i got lucky on this one...or the spirit of lbass musta come upon me. LOL!!!

 

[MCuthill]

*chuckles*

True enough Fisheromacse, I think *most* briliant/simple solutions are the result of luck! I never even contemplated such a simple approach - figured the programming method was the way that would inevitably be suggested (and perhaps (read: "likely") by LB lol).

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."