reports: need info for counting field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

reports: need info for counting field

Author

Topic

evert1803
Starting Member

6 Posts

Posted - 2004-01-13 : 13:15:53

Sorry for my bad english....

I need help how to put a field into a report that I can control myself.

It is for a swimming contest program where I have to be able to calculate the place off a swimmer. An example:

Place Swimmer Time
1 Pietje 01:05:33
2 Jantje 01:05:45
2 Klaasje 01:05:45
4 Henk 01:06:13
5 Herman 01:07:82
5 Jozef 01:07:82
7 Willem 01:08:51

Each line is a detail record from a query which uses the report. I want to control the field Place myself, but this field Place is not part of the query.

Please help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 14:01:22

What do you mean control yourself?

Using the sample data that you posted, what would the result set look like?

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-13 : 15:19:40

If your underlying query is something simple, like this:

select Swimmer, Time from t;

then you may add here the field Place:

select Swimmer, Time,
(select count(*) from (select distinct Time from t as z) as tt where tt.Time<=t.Time)
as Place
from t;

evert1803
Starting Member

6 Posts

Posted - 2004-01-13 : 15:38:36

The program itself is more complicated, but I keep this question simple with only two fields from the database, swimmer and time.
It is sorted on time. What I need in the report is a field that I can calculate myself (I think that is neccesary) to indicate the place of the swimmer. The fastest swimmer is place 1, etc.

But if two (or more) swimmers have the same time, then they also have the same place. As shown in the example.

Ok?

quote:
Originally posted by tduggan

What do you mean control yourself?

Using the sample data that you posted, what would the result set look like?

Tara

evert1803
Starting Member

6 Posts

Posted - 2004-01-13 : 15:41:39

Wow, I think this is way over my head. I'm not sure if I can fit this into the sql statement that MS Access has generated for me:
-------------
SELECT Wedstrijd.Wedstr_kode, Wedstrijd.Datum_van, Wedstrijd.Datum_tm, Wedstrijd.Clubkode, Programma.Programma_nr, Programma.Datum, Programma.Zwemslagkode, Programma.Afstand, Programma.[Vrije tekst], Programma.[E deelnemers], Programma.Geslacht, Serie.[Zwemmer ID], Zwemmer.Achternaam, Zwemmer.Voornaam, Serie.Serie_nr, Serie.Baan_nr, Serie.Volgnummer, Serie.[Klasse kode], Serie.Verw_tijd, Serie.Jun_Sen, Serie.Gezw_tijd, Serie.Dk_kode, Zwemmer.Startnummer, Zwemslag.Omschrijving, Zwemmer.Landkode, Serie.Vrijetekst
FROM Zwemslag INNER JOIN (Zwemmer INNER JOIN (Programma INNER JOIN (Wedstrijd INNER JOIN Serie ON Wedstrijd.Wedstr_kode = Serie.Wedstr_kode) ON (Wedstrijd.Wedstr_kode = Programma.Wedstr_kode) AND (Programma.Programma_nr = Serie.Programma_nr) AND (Programma.Wedstr_kode = Serie.Wedstr_kode)) ON Zwemmer.[Zwemmer ID] = Serie.[Zwemmer ID]) ON Zwemslag.Zwemslagkode = Programma.Zwemslagkode
ORDER BY Wedstrijd.Wedstr_kode, Programma.Programma_nr, Serie.Dk_kode, Serie.[Klasse kode], Serie.Gezw_tijd;

-------------

quote:
Originally posted by Stoad

If your underlying query is something simple, like this:

select Swimmer, Time from t;

then you may add here the field Place:

select Swimmer, Time,
(select count(*) from (select distinct Time from t as z) as tt where tt.Time<=t.Time)
as Place
from t;

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-13 : 16:03:58

Better,
(select count(*) from t as tt where tt.Time<t.Time)+1 as Place

I expected this. Means you need some the report VBA module trick.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 16:08:35

select swimmer, time, (select count(*) from swimmers s where
s.time<= swimmers.time) as place
from swimmers
order by time

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-13 : 16:39:02

This is for your report module (leave textbox Place unbound):

Option Compare Database
Option Explicit
Dim i, j, g

Private Sub DetailSection_Format(Cancel As Integer, FormatCount As Integer)
i = i + 1
If g <> Me.Time Then
g = Me.Time
j = i
Me.Place = i
Else
Me.Place = j
End If

End Sub

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 16:44:34

I had to make an adjustment on the code since it was returning:


Place Swimmer Time
1 Pietje 01:05:33
3 Jantje 01:05:45
3 Klaasje 01:05:45
4 Henk 01:06:13
6 Herman 01:07:82
6 Jozef 01:07:82
7 Willem 01:08:51

It works now.

select
(select count(*) + 1 from swimmers s where s.time < swimmers.time) as place, swimmer, time
from swimmers
order by time



Place Swimmer Time
1 Pietje 01:05:33
2 Jantje 01:05:45
2 Klaasje 01:05:45
4 Henk 01:06:13
5 Herman 01:07:82
5 Jozef 01:07:82
7 Willem 01:08:51

This was tested on a SQL Server box. It'll probably work for Access too.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-13 : 17:18:50

LOL.. Tara.. of course it works fine.. and in ms access too..

What is the weather in San Diego? Plz, in Celsius degrees.. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 18:05:36

Blue skies at 21 degrees Celsius. Awesome weather. Shorts and t-shirt possibly during the day, switch to pants at night with a light jacket.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-13 : 20:29:01

put a textbox on your report. set it's controlsource to "=1"

then set "running total" to "over all" or "over group."

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-13 : 21:50:46

quote:
Blue skies at 21 degrees Celsius.

Incredible!! This explains much :) On the other hand, I could not
live anywhere without the real winter. Nothing can compare to it.

jsmith8858--
Jeff, tertium non datur: or you overlooked the question or I am a
stupidest ass.

evert1803
Starting Member

6 Posts

Posted - 2004-01-14 : 07:31:38

I'm sorry, Jeff, but that's too easy thinking.
Watch the example again please. The second and third swimmer have the same time, therefore they both have Place 2. In your solutions they wolud have place 2 and 3!

quote:
Originally posted by jsmith8858

put a textbox on your report. set it's controlsource to "=1"

then set "running total" to "over all" or "over group."

- Jeff

evert1803
Starting Member

6 Posts

Posted - 2004-01-14 : 07:35:06

Yeah, I was looking for this and found it. Tried to do something yesterday evening. Do I have to declare variables somewhere global?

quote:
Originally posted by Stoad

This is for your report module (leave textbox Place unbound):

Option Compare Database
Option Explicit
Dim i, j, g

Private Sub DetailSection_Format(Cancel As Integer, FormatCount As Integer)
i = i + 1
If g <> Me.Time Then
g = Me.Time
j = i
Me.Place = i
Else
Me.Place = j
End If

End Sub

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-14 : 07:45:52

ooopss .. didn't notice the "ties" part ... Nice job, stoad.

evert -- the code Stoad has shown you includes the variable declarations at the report level.

- Jeff

evert1803
Starting Member

6 Posts

Posted - 2004-01-15 : 14:53:34

OK, everybody thanks a lot

I managed to solve the problem with your help!

Stoad, you're my hero!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources