This is a multi-part message in MIME format.
------=_NextPart_000_0022_01C1B1C1.826EF9C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
RE: [sql_language] RE: keeping one record of multiple enteriesA thanks
to all who helped with my query. After several bottles(!) and late
nights I have managed to achieve the result I was looking for.
Not ideal , but due to the limitations of Access and problems with
aggregates a good compromise.
I enclose the coding and end result.
Thanks again
Charles
"Access Query"
PARAMETERS Class Text ( 255 ), Ranking Long;
SELECT MIN(indoor.Perf) AS MinOfPerf, indoor.Athfnam, indoor.Athsurnam,
indoor.Club
FROM indoor
WHERE (((indoor.Event)=3D'U13G 60m'))
GROUP BY indoor.Athfnam, indoor.Athsurnam, indoor.Club
ORDER BY Min(indoor.Perf);
"ASP to link to access query"
<HTML>
<HEAD>
<body>
Try out U13g query
<%
On Error Resume Next
Set objConn =3D Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=3Dindoortrial"
If objConn.Errors.Count > 0 Then
Set objErr =3D Server.CreateObject("Error")
Dim blnCriticalError
For Each objErr In objConn.Errors
If objErr.Number <> 0 Then
Response.Write "Number: " & objErr.Number & "<P>"
Response.Write "Description: " & objErr.Description & "<P>"
Response.Write "Source: " & objErr.Source & "<P>"
Response.Write "SQLState: " & objErr.SQLState & "<P>"
Response.Write "NativeError: " & objErr.NativeError & "<P>"
blnCriticalError =3D True
End If
Next
Set objErr =3D Nothing
If blnCriticalError Then
Response.End
End if
End if
Dim adOpenForwardOnly
Dim adCmdStoredProc
Dim strMinOfPerf
Dim lngRank
adOpenForwardOnly
adCmdStoredProc =3D 4
strMinOfPerf =3D "12"
lngRank =3D 20
Set objRS =3D Server.CreateObject("ADODB.Recordset")
strSQL =3D "indquery'" & CStr(strMinOfPerf) & "'," & CLng(lngRank)
objRS.Open strSQL, objConn, adOpenForwardOnly, , adCmdStoredProc
If objConn.Errors.Count > 0 Then
Set objErr =3D Server.CreateObject ("Error")
For Each objErr In objConn.Errors
If objErr.Number <> 0 Then
Response.Write "Number: " & objErr.Number & "<P>"
Response.Write "Description: " & objErr.Description & "<P>"
Response.Write "Source: " & objErr.Source & "<P>"
Response.Write "SQLState: " & objErr.SQLState & "<P>"
Response.Write "NativeError: " & objErr.NativeError & "<P>"
blnCriticalError =3D True
End If
Next
Set objErr =3D Nothing
If blnCriticalError Then
Response.End
End If
End If
%>
<TABLE BORDER=3D1 CELLSPACING=3D1>
<TR>
<TD colspan=3D5>MinOfPerf <%=3DobjRS("MinOfPerf")%>, Top <%=3DlngRank%>
Rankings</TD>
</TR>
<TR>
<TH BGCOLOR=3Dnavy><FONT COLOUR=3Dwhite>Perf</FONT></TH>
<TH BGCOLOR=3Dnavy><FONT COLOUR=3Dwhite>First Name</FONT></TH>
<TH BGCOLOR=3Dnavy><FONT COLOUR=3Dwhite>SurName</FONT></TH>
<TH BGCOLOR=3Dnavy><FONT COLOUR=3Dwhite>Club</FONT></TH>
<%
Do While Not objRS.EOF
%>
<TR>
<TD><%=3DobjRS("MinOfPerf")%></TD>
<TD><%=3DobjRS("Athfnam")%></TD>
<TD><%=3DobjRS("Athsurnam")%></TD>
<TD><%=3DobjRS("Club")%></TD>
</TR>
<%
objRS.MoveNext
Loop
objRS.Close
Set objRS =3D Nothing
objConn.Close
Set objConn =3D Nothing
%>
</body>
"WEb page result-appears as table"
Try out U13g query MinOfPerf 8.58, Top 20 Rankings
Perf First Name SurName Club
8.58 Kirstyn Suttie Pitreavie AAC
8.63 Emily Mitchell Carmarthen
8.68 Julie Keenlyside Elswick Harriers
8.74 Jennifer Taker Trafford
8.75 Alix Wilson Falkirk Victoria Harriers
8.78 Rebecca Riches Helensburgh AAC
8.8 Alyshea Reader Cwmbran
8.8 Llio Fflur Evans Menai
8.83 Hannah Harris Neath
8.85 Kerry Steel Ayr Seaforth AAC
8.91 A Wilson Falkirk VH
8.98 Lorna Fraser Airdrie Harriers
9.02 L Fraser Airdrie H
9.04 Rachel Fairbairn Ayr Seaforth AAC
9.04 Suzanne Begg City of Glasgow AC
9.06 Colette Keenan Cumbernauld AAC
9.08 Ailsa Doige Dunfermline & West Fife AC
9.13 Joanne Davidson Stewartry AC
9.15 H Belch Kilbarchan
9.15 Maxime Ladley Cranford AC
9.15 Lucy Ansell Tipton
9.16 Krysha Izatt Dunfermline & West Fife AC
9.18 Neisha Kilner Harmeny AC
9.2 Clare Beattie Carnoustie
9.21 Rebecca Gatehouse Aberaeron Sch
9.26 Laura James Cwmbran
9.28 Sophie Fithern Tipton
9.3 Amber Vincent Cardiff
9.3 Lauren Jeavons Tipton
9.3 Rebecca Gatehouse Carmarthen
9.32 Rebecca Nuttall Deeside
9.35 Carla Bourne Letterkenny AC
9.35 Samantha Humphrey Bridgend
9.37 Sarah Dacey Swansea
9.38 Holly Belch Kilbarchan AAC
9.4 Toni Bird Glasgow School of Sport
9.41 Lauren Jeavens Tipton
9.43 Sophie Fithekin Tipton
9.44 Rachel Roberts Bridgend
9.45 Heather McCammon City of Glasgow AC
9.46 Katherine Ogden Kg Edward S
9.68 Chloe Foote New Forest
9.68 Katie Strickland Newport
9.71 Liddy Williamson Stroud
9.73 Sandra Seaton Belgrave
9.75 Stephanie Innes 0
9.8 Katie Stickland Newport
9.82 Denise Beirne Finn Valley AC
9.82 Sarah Rosie Livingston & District AC
9.84 Clare McGrogan Mid Ulster AC
9.87 Charlotte James Tipton
9.87 Kim Grant Falkirk Victoria Harriers
10.07 Anna Turner Poole
10.1 Keely Golden Cardiff
10.24 Victoria Cowden Mid Ulster AC
10.26 Cherry Morris Swansea
10.3 Santina Phillips Belgrave
10.4 Danielle Gray New Forest
----- Original Message -----
From: David Cameron
To: sql language
Sent: Thursday, February 07, 2002 10:51 PM
Subject: [sql_language] RE: keeping one record of multiple enteries
I built an Access table similar to yours and tested it. I don't think
you can do an order by perf because it is aggregated. You could use 2
queries. Here is the code for the two queries I used.
SELECT [Athlete], [Event], MAX([Perf]) AS MaxPerf, [Club]
FROM indoor
GROUP BY [Athlete], [Event], [Club];
SELECT * FROM Query1 ORDER BY MaxPerf
This ran inside Access, so don't apply this as is to your problem. You
best option might be a query inside Access that has that does:
SELECT Athlete, Event, MAX(Perf), Club
FROM indoor
GROUP BY Athlete, Event, Club
and query that.
regards
David Cameron
nOw.b2b
dcameron@i...
> -----Original Message-----
> From: charles@l...
[mailto:charles@l...]
> Sent: Thursday, 7 February 2002 10:34 PM
> To: sql language
> Subject: [sql_language] RE: keeping one record of multiple
enteries
>
> Dear David
>
> Tried below but still having a problem. Could be I am using Access
and not allowed to mix non-aggregate and aggregate functions in a single
string.
>
> I created an alais "maxperf" and that did produce the best
performance. However it seems that the code is restricting to one
performance-(I also only gor the perf) I am looking to bring up the best
performance for each athlete in the database- in this case all athletes
in the UK.
>
> The end result I am looking for is
>
> Mitchell 13.4 100m Carm U13G
> Bloggss 13.5 100m Cardiff U13G
> etc
> and then be able to do the same for all age groups
> .(Ican cope with this)
>
> Sorry !!
>
> Charles
>
> ----- Original Message -----
>
> From: David Cameron <mailto:dcameron@i...>
> To: sql language <mailto:sql_language@p...>
> Sent: Wednesday, February 06, 2002 10:38 PM
> Subject: [sql_language] RE: keeping one record of multiple enteries
>
>
> Change your SQL string to:
>
> sSQL =3D"SELECT Athlete, Event, MAX(Perf), Club FROM indoor GROUP BY
Athlete, Event, Club WHERE (Event=3D'U13G 60m') AND (Perf <9.5) ORDER BY
Perf ASC;"
>
> watch for wrapping
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i... <mailto:dcameron@i...>
>
> > -----Original Message-----
> > From: charles@l...
<mailto:charles@l...> [ <mailto:charles@l...>]
> > Sent: Wednesday, 6 February 2002 10:11 PM
> > To: sql language
> > Subject: [sql_language] RE: keeping one record of multiple
enteries
> >
> > Thanks for the code which works and will list all athletes by name
once.
> >
> > However I cannot incorporate it with the other sql statement which
will list the athelete, club.event and performance.
>
> >
> > I am well out of my depth , I guess.
> >
> > The code I have is as follows:
> >
> >
> > --
> > Dim oRS
> > Set oRS=3DServer.CreateObject ("ADODB.recordset")
> > sSQL =3D"SELECT Athlete, Event, Perf, Club FROM indoor WHERE
(Event=3D'U13G 60m') AND (Perf <9.5) ORDER BY Perf ASC;"
> > oRS.Open sSQL, "DSN=3Dindoortrial"
> > oRS.MoveFirst
> > Response.Write "<TABLE BORDER=3D1>"
> > Do While NOT oRS.EOF
> > Response.Write "<TR><TD>" & oRS ("Athlete") & "</TD>"
> > Response.Write "<TD>" & oRS ("Event") & "</TD>"
> > Response.Write "<TD>" & oRS ("Perf") & "</TD>"
> > Response.Write "<TD>" & oRS ("Club") & "<</TD></TR>"
> > oRS.MoveNext
> > Loop
> > Response.Write "</TABLE>"
> > oRS.Close
> > Set oRS=3Dnothing
> >
> >
> >
> >
> > This produces a table with athlete , event(i.e.60m) Perf(time) and
Club. This is where I am getting the duplications of athletes with
different times over the same event. All I want is the Athlete , Event
and Best perofmance , Club to come up.
>
> >
> > Obviously the code you sent works , but how do I incorporate it
into the above
> >
> > Sorry- I am a beginner
> >
> > Charles
> >
> > --- Original Message -----
> > From: David Cameron < <mailto:dcameron@i...>>
> > To: sql language < <mailto:sql_language@p...>>
> > Sent: Tuesday, February 05, 2002 10:55 PM
> > Subject: [sql_language] RE: keeping one record of multiple
enteries
> >
> >
> > Assuming table structure is:
> >
> > Performances
> > PerformanceID (PK)
> > Athlete
> > Score
> >
> > SELECT Athlete, MAX(Score) AS Score
> > FROM Performances
> > GROUP BY Athlete
> >
> > regards
> > David Cameron
> > nOw.b2b
> > dcameron@i... < <mailto:dcameron@i...>>
> >
> > -----Original Message-----
> > From: charles mitchell [ < <mailto:charles@l...>>]
> > Sent: Wednesday, 6 February 2002 8:04 AM >
> > To: sql language
> > Subject: [sql_language] keeping one record of multiple enteries
> >
> >
> > I am struggling a bit. I am trying to set up an athletics
results/rankings
> > page. No problem getting data by age , distance etc. However I am
> > struggling with rankings , as obviously the same athletes tend to
be up
> > there in the top twenty times. I want to write an sql statement
that will
> > eliminate duplicate enteries and allow only the best performance
for each
> > athlete to appear.
> >
> > I.e.
> >
> > ejm 8.5
> > cdm 8.7
> > ejm 8.8
> > cdm 8.9
> >
> > I would like to show only the best times for ejm and cdm.
> >
> > ---
> > Change your mail options at < <http://p2p.wrox.com/manager.asp>>
or
$subst('Email.Unsub').
> >
> > ---
> > Change your mail options at <http://p2p.wrox.com/manager.asp> or
$subst('Email.Unsub').
> >
> > ---
> > Change your mail options at <http://p2p.wrox.com/manager.asp> or
$subst('Email.Unsub').
>
$subst('Email.Unsub').
>
$subst('Email.Unsub').
$subst('Email.Unsub').
View Cart
