Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filtering results based on a running total

Status
Not open for further replies.

n2nuk

IS-IT--Management
Dec 24, 2002
190
GB
Hi there,

I am reporting on a database that returns the membership cards that have given to users, where its possible that some users have had more than one membership card if they have lost the one they have been allocated. its also possible for users to have multiple cards of different types.

on my report have the following rows

from the users table : user id, username, start_date, department

from the cards table : card_id, activation_date, card_type, card_lost.

I have joined both tables on user id and am able to report on all the users and the cards allocated to them.

What i want to restrict my report so that it only returns those with more than one card.

I thought this would be simple to do by creating a running total and then using this field to only return rows where the count is > 1 but Crystal does not seem to support that. is there anyway else i can get this work?

 
Group on user ID and then go to report->selection formula->GROUP and enter:

Distinctcount({cards.cardID},{users.userID})>1

If you want to do any summaries across users, you will need to use running totals since non-selected records are still IN the report, just not displayed.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top