Evaluate the first record of a group

I have a database that has three columns: a date/time, a person's name and a ticket number.  I have grouped my report on the ticket number.  I need to evaluate only the first row of the group, based on the date/time, and count how many times each person's name shows up.   The database would look like this:

7/2/2010 11:35:25  JACK  12345
7/2/2010 11:40:25  JANE  12345
7/2/2010 11:45:25  JANE  12345

7/2/2010 11:50:25  EMILY  12346
7/2/2010 11:55:25  JACK  12346

7/2/2010 12:00:25  JACK  12347
7/2/2010 12:05:25  JANE  12347

7/2/2010 12:10:25  JANE  12348

and the results would look like this:
JACK  EMILY  JANE
  2          1           1


Any ideas?
rainfieldsAsked:
Who is Participating?
 
bischofb_CRNETConnect With a Mentor Commented:
Ok, I'm going to type this in off the top of my head since I don't have your report here. So there might be a syntax error, but for the most part it should work fine. Replace the dummy field names with yours where appropriate. Put this formula in the group header.

//create the array without losing older values
Global NumberVar Array NameArray;
Redim Preserve NameArray[100];  // I assume you wont' have more than 100 names?
Redim Preserve CountArray[100]
NumberVar SearchIndex;
NumberVar NameIndex;

StringVar NameToFind;
NameToFind := {yourtable.namefield};

SearchIndex := 1;
//Loop until we find a match
While NameIndex == 0
(
    If NameArray[SearchIndex] == NameToFind Then
        //Found the name!
        NameIndex=SearchIndex;
    If NameArray[SearchIndex] == "" Then
    (
        //We are at the end and didn't find it. So add it to the current postiion
        NameArray[SearchIndex] := NameToFind;
        NameIndex := SearchIndex;
    )
    SearchIndex := SearchIndex + 1;
)

//Increase the counter by one
CountArray[NameIndex] := CountArray[NameIndex] + 1;
0
 
mlmccCommented:
Do you need the "extra" records in the report?

I think you could use the TOP 1 option on the group

If you need all the data, is there a limit on the number of names?
You will need to do this with variables and arrays.

The other way would be to use a subreport that selects only the first record and do the table in the subreport

mlmcc
0
 
rainfieldsAuthor Commented:
I do not need the extra records, just the first one in each group.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
mlmccCommented:
I would use a database command as the source and use the TOP 1 option to select only the first record in each group

mlmcc
0
 
bischofb_CRNETCommented:
I've done this a hundred times. Use a command object and a simple SQL statement like this (insert your own field and table names):

SELECT person_name, Count(*) FROM your_table GROUP BY person_name

To print it out across the page, use a label format that goes from left to right. Should work fine.

If you find this helpful, please mark the question answered.  :-)
0
 
bischofb_CRNETCommented:
Oh - I forgot that you are grouping by ticket number.

SELECT Ticket_number, person_name, Count(*) FROM your_table GROUP BY ticket_number, person_name

0
 
rainfieldsAuthor Commented:
mlmmc: could you give me more information on how you would use the TOP 1 to get rid of all but the first record in each group?
0
 
mlmccCommented:
My SQL is rusty so I really can't

It is something like

SELECT TOP 1 name FROM database Group By somefield

mlmcc
0
 
VenkatgviCommented:
what database are you using, if it is oracle, just use this query

select ticket_no,name, count(name) over (partition by name) name_count from <table_name>
0
 
bischofb_CRNETCommented:
Here is how I would do it. First off, you only want to see the first record in each group by date. So you will need to create a formula in the group header b/c that will let you isolate only the first record. Second, you want to do it by date, so you will need to add a sort field based on the date.

Now that you have isolated the first record, and you know it is the most recent one, you need to create a formula to track how many of each item there is. To do this you will need two arrays. The first array tracks the name and the second array tracks the count. Create a formula that loops through the first array and checks if the name is in there or not. If not, then add it to the end of the array. If it is already there, then don't do anything. You will need to store the location of the index in where the name is found in the first array.

The second part of the formula uses the index from above and increases the count in the second array of the proper element. Thus, if the index for Emily is 2 (NumberVar NameIndex), then the second formula would be

CountArray[NameIndex] := CountArray[NameIndex} + 1;

When the report is finished, the first array will be filled with all the names and the second array will be filled with their total counts. Then in the report footer print each element in the arrays to show each name and its count.

-Brian
0
 
rainfieldsAuthor Commented:
Brian, thanks for the input.  That gets me most of the way, but I am still having problems with the formula that would isolate the first record.  Any help would be appreciated.  The TOP 1 suggestion that mlmcc had isn't working for me.
0
 
bischofb_CRNETCommented:
I forgot to declare the second array at the top. Here they are again:

Global NumberVar Array NameArray;
Global NumberVar Array CountArray;
0
 
James0628Connect With a Mentor Commented:
Brian's basic idea should work.  I would write the formula a bit differently, but I think his will work too, but there are a few syntax and other issues.

//create the array without losing older values
Global NumberVar Array NameArray;
Global NumberVar Array CountArray;
Redim Preserve NameArray[100];  // I assume you wont' have more than 100 names?
Redim Preserve CountArray[100];       // --- Added a ; here
NumberVar SearchIndex;
NumberVar NameIndex;

StringVar NameToFind;
NameToFind := {yourtable.namefield};

SearchIndex := 1;
//Loop until we find a match
While NameIndex = 0       // --- Replaced == with =
(
    If NameArray[SearchIndex] = NameToFind Then       // --- Replaced == with =
        //Found the name!
        NameIndex := SearchIndex;       // --- Replaced = with :=
    If NameArray[SearchIndex] = "" Then       // --- Replaced == with =
    (
        //We are at the end and didn't find it. So add it to the current postiion
        NameArray[SearchIndex] := NameToFind;
        NameIndex := SearchIndex;
    );       // --- Added a ; here
    SearchIndex := SearchIndex + 1;
);       // --- Added a ; here

//Increase the counter by one
CountArray[NameIndex] := CountArray[NameIndex] + 1;

// --- Added "" at the end, so that the formula doesn't produce
// --- any actual output on the report.
// --- That's really just a personal preference
""


 In addition to that, you should create a separate formula like the following and put it in the report header to initially create those variables.  It seems that global variables sometimes don't work properly if they're not declared first in the report header.

Global NumberVar Array NameArray;
Global NumberVar Array CountArray;


 If you could have more than 100 names on the report, you could increase the array sizes accordingly, or the formula could be changed to expand the arrays if necessary.  However, CR arrays are limited to 1000 elements, so if you could have more than 1000 names on a report, that will be a problem.

 James
0
 
James0628Commented:
Oops.  Forgot to end that second formula (the one that goes in the report header) with "".  Again, that's just a personal preference of mine.  You could also just suppress the formula, or the entire section, if there's nothing in it that you need to see.

 James
0
 
mlmccCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.