Opposite of Running Count

[aqif]

Hi

I am trying to figure out how many people are left in query. My problem is something like that.

ID Name DOB
1 A 1/1/01
2 B 2/2/01
3 C 3/2/01
4 D 4/4/01

The query I want to write will give an output like

ID Name DOB NumberLeft
1 A 1/1/01 4-1=3
2 B 2/2/01 4-2=2 or 3-1=2
3 C 3/2/01 4-3=1
4 D 4/4/01 4-4=0

*Where 4 is the total count of the query.

Is there anyway that I could work myself backward?

Cheers!
Aqif

 

[Rich]

Use report, in the header add an unbound control with =Count on say the ID field in your report,add an unbound hidden text box in the detail section set the control source to =1, set running sum over group, add another text box set control source to =[NameOfFirstTextBox]-[NameOfSecondTextBox], set running sum to No.

 

[aqif]

Hi

Thanxx for ur reply.....my problem is that I've to export the data for staistical anylysis purposes so thats y i want a query to handle that, they'll access the query through ODBC.

Cheers!
Aqif

 

[Rich]

Running sums in a query are a nightmare, you need a sequential ID and depending on the size of the recordset the output can be very slow, can you not export the report, EMail it? Do you have a sequential ID field?