Counting Within A Group

[Ohioste]

This is probably the simplest of problems...however, I'm trying to show data from each "detail" of a group.

For example I'm grouping by @location:

So my return will be

Location
Qty Expd Qty Count Date Counted Time Counted
Qty Expd Qty Count Date Counted Time Counted
etc...

I want to be able to hide the details section and show Qty Counted for each record.

I've tried to think this through and believe the best way to do this is to create a cross tab. The problem is, there are so many "Time Counted" record that I get an error saying "Memory Full".

Is there an easier way to do this or is there a way to count the number of records within a group?

Your help is much appreciated!

Thanks,
Ryan

 

[lbass]

I'm not following what the advantage is of showing the same data that is in the detail section in the group section.

-LB

 

[Ohioste]

I agree...however it's a management request! It's easier to read for them, ha.

It's during an inventory and they want to be able to see the 1st count, second count and third count in columns instead of the way it is above.

I agree...not much difference, but a request none the less.

Thanks.

 

[lbass]

So what would the report look like?

-LB

 

[Ohioste]

@ Location 1st Count 2nd Count 3rd Count 4th Count



and etc to however many different records there are. The details can be below, but this is probably going to be exported to Excel with just the above information.

Thanks.

 

[lbass]

Yes, inserting a crosstab into a group section would be the simplest. You could add an ID field as the column --or something unique to the count which might be time counted. Then you could suppress the column label and remove the grid. Not sure it would export that nicely though, but it would be quick to try.

-LB

 

[Ohioste]

Sorry...pretty new at this...how would I add an ID field. I've tried different things with no such luck.

Thanks!

 

[lbass]

I just meant some field that was unique to each row of data. That could be some ID field in the table or it might be the time field--if two records in the same group can't have the same time.

-LB

 

[Ohioste]

Unfortunately, time cannot be used. It's the only unique identifier in the table, however, there are too many time records and I keep getting the error "Memory Full". I was hoping there was a way to count the records inside the group and label them. So if there are three records, then the lowest amount of time is count 1, middle is count 2, and highest count is 3.

I know it's an odd request. I've tried the formula you used to label everything a,b,c for like 27,000 records, however, that just continues through the group, where as I need it to break within each group. So instead of going a, b, c in the first detail section, and then start with d in the next, I need it to go a, b, c, then start over with a after that.

Any ideas?

Thanks.

 

[Ohioste]

Ok, so I feel I have made it a little further...I've learned about the running totals field and got it to number properly. However, come to find out, you can't use that in a crosstab. Any chance there is a way around that?

Thanks.

 

[lbass]

You need to solve the memory full problem. You might need to dump temp files or something. Not using the time field doesn't mean you are limiting the records, so that's not really a solution. I have no idea what formula you are talking about with the a,b,c.

-LB

 

[Ohioste]

whileprintingrecords;
stringVar array Letter := ["a","b","c","d","e","f",
"g","h","i","j","k","l","m","n","o","p","q","r",
"s","t","u","v","w","x","y","z"];
stringvar x;
numbervar j;
numbervar k;
numbervar m;

if recordnumber <= 26 then
(j := j + 1;
x := Letter[j] + ". ") else

if recordnumber in 27 to 702 then
(if remainder(recordnumber,26) = 1 then
k := k + 1;
if j = 26 then
j := 1 else
j := j + 1;
x := Letter[k] + Letter[j]+". ") else

if recordnumber in 703 to 17602 then
(if remainder(recordnumber,676) = 27 then
m := m + 1;
if k = 26 then
k := 1 else
if remainder(recordnumber,26) = 1 then
k := k + 1;
if j = 26 then
j := 1 else
j := j + 1;
x := Letter[m] + Letter[k] + Letter[j]+". ");

 

[lbass]

I have no context for this.

-LB

 

[Ohioste]

Here is the original thread:

http://www.tek-tips.com/viewthread.cfm?qid=1087842

Pulled it out from what looks like '05. I've tried to solve the problem myself, but just can't seem to get what I want.

I just need to find a way that I can number each row of a detail section and use that field as the top of a crosstab. The time may work, but it records in seconds, so through the whole week, we could have a record for each second of that week. I assume that's why it errors out.

The Count A, B, C thing would work, but I need to find a way to make it reset after each group.

Again, sorry for such a trivial request. I know the data is already there, just not how the higher ups want to see it!

 

[lbass]

Please show sample data at the detail level for multiple rows and include the date and time fields. Are the qty counts duplicated? Then show what you would want to see at the group level for the same sample.

-LB