«

Hi,

trying to retrieve postal codes from the db but only want the query to look
at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I don't
want the query to count individual post codes but instead look at an area
found in the first 3 digits e.g. HA0 3TD is for a particular house but HA)
is for the area Harrow.


"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS CountOfCOLL_POST_CODE,
COLL_POST_CODE FROM dbo.booking_form GROUP BY COLL_POST_CODE ORDER BY
CountOfCOLL_POST_CODE DESC"

Regards
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:

> Hi,
>
> trying to retrieve postal codes from the db but only want the query to
> look at the first 3 digits of the code tried using
> (LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
> don't want the query to count individual post codes but instead look
> at an area found in the first 3 digits e.g. HA0 3TD is for a
> particular house but HA) is for the area Harrow.
>
>
> "SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
> CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
> COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"

You should mention the db-engine used for a correct answer.

I use this with the Jet engine:

SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3)"

'''response.write SQL &"<hr>"
set mDATA=CONNECT.Execute(SQL)

Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Thanks Evertjan, having a problem though could you look below and suggest.

<%
Dim AreaColl
Dim AreaColl_numRows

Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING
AreaColl.Source = "SELECT (Left(Count(dbo.booking_form.COLL_POST_CODE),3))
AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open()

AreaColl_numRows = 0
%>

and in the body


<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetText"><%=(AreaColl.Fields.Item("COLL _POST_CODE").Value)%></td>
<td width="790"
class="DataSetText"><%=(AreaColl.Fields.Item("Coun tOfCOLL_POST_CODE").Value)
%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AreaColl.MoveNext()
Wend
%>

Thanks in advance


"Evertjan." <> wrote in message
news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
> Simon Gare wrote on 05 jan 2007 in
> microsoft.public.inetserver.asp.general:
>
> > Hi,
> >
> > trying to retrieve postal codes from the db but only want the query to
> > look at the first 3 digits of the code tried using
> > (LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
> > don't want the query to count individual post codes but instead look
> > at an area found in the first 3 digits e.g. HA0 3TD is for a
> > particular house but HA) is for the area Harrow.
> >
> >
> > "SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
> > CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
> > COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>
> You should mention the db-engine used for a correct answer.
>
> I use this with the Jet engine:
>
> SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
> " FROM myTbl GROUP BY left(postcode,3)"
>
> '''response.write SQL &"<hr>"
> set mDATA=CONNECT.Execute(SQL)
>
> Response.Write "<table border=1><tr>" & vbcrlf
> Do Until mDATA.Eof
> tal = mDATA("tal")
> pc = mDATA("pc")
> if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
> Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
> mDATA.MoveNext
> Loop
> Response.Write "</table>" & vbcrlf
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:

> "Evertjan." <> wrote in message
> news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
>> Simon Gare wrote on 05 jan 2007 in
>> microsoft.public.inetserver.asp.general:
>>
>> SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
>> " FROM myTbl GROUP BY left(postcode,3)"

[Please do not toppost on usenet]

> Thanks Evertjan, having a problem though could you look below and
> suggest.
>
> Set AreaColl = Server.CreateObject("ADODB.Recordset")

> <%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE"). Value)%>

I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those long
names with multiple _'s and unnecessary ()'s.

> .... having a problem though could you look below and
> suggest.

If you could test your code yourself, starting with the smallest and most
readable code that gives a problem, perhaps you could even come up with
explaining the kind of problem you have, Simon.

btw, did my code work with you?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

"Evertjan." <> wrote in message
news:Xns98B022E385DEeejj99@194.109.133.242...
> Simon Gare wrote on 05 jan 2007 in
> microsoft.public.inetserver.asp.general:
>
>> "Evertjan." <> wrote in message
>> news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
>>> Simon Gare wrote on 05 jan 2007 in
>>> microsoft.public.inetserver.asp.general:
>>>
>>> SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
>>> " FROM myTbl GROUP BY left(postcode,3)"
>
> [Please do not toppost on usenet]
>
>> Thanks Evertjan, having a problem though could you look below and
>> suggest.
>>
>> Set AreaColl = Server.CreateObject("ADODB.Recordset")
>
>> <%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE"). Value)%>
>
> I never use a Recordset [you can easily do without it]
> and the code you show is much to complex for me to read with all those
> long
> names with multiple _'s and unnecessary ()'s.

What do you use instead?


-Mark



>> .... having a problem though could you look below and
>> suggest.
>
> If you could test your code yourself, starting with the smallest and most
> readable code that gives a problem, perhaps you could even come up with
> explaining the kind of problem you have, Simon.
>
> btw, did my code work with you?
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

"Simon Gare" <> wrote in message
news:%...
> Thanks Evertjan, having a problem though could you look below and suggest.

What is the problem?

[more comments inline...]


> <%
> Dim AreaColl
> Dim AreaColl_numRows
>
> Set AreaColl = Server.CreateObject("ADODB.Recordset")
> AreaColl.ActiveConnection = MM_TobiasNET_STRING

You should create an explicit connection object, rather than relying on ADO
to create one for you implicitly.

> AreaColl.Source = "SELECT (Left(Count(dbo.booking_form.COLL_POST_CODE),3))

I doubt this is legal, LEFT expects varchar or text, COUNT returns int. I
think what you want is:

SELECT COUNT(*), LEFT(COLL_POST_CODE, 3) FROM dbo.booking_form GROUP BY
LEFT(COLL_POST_CODE, 3) ORDER BY COUNT(*) DESC

You might want to consider defining a computed column for the left 3 of the
postal code, for both ease of reference and db server efficiency.


-Mark



> AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
> COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
> AreaColl.CursorType = 0
> AreaColl.CursorLocation = 2
> AreaColl.LockType = 1
> AreaColl.Open()
>
> AreaColl_numRows = 0
> %>
>
> and in the body
>
>
> <td colspan=2>Top 10 collection post codes</td>
> </tr>
> <%
> While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
> %>
> <tr>
> <td width="100"
> class="DataSetText"><%=(AreaColl.Fields.Item("COLL _POST_CODE").Value)%></td>
> <td width="790"
> class="DataSetText"><%=(AreaColl.Fields.Item("Coun tOfCOLL_POST_CODE").Value)
> %></td>
> </tr>
> <%
> Repeat1__index=Repeat1__index+1
> Repeat1__numRows=Repeat1__numRows-1
> AreaColl.MoveNext()
> Wend
> %>
>
> Thanks in advance
>
>
> "Evertjan." <> wrote in message
> news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
>> Simon Gare wrote on 05 jan 2007 in
>> microsoft.public.inetserver.asp.general:
>>
>> > Hi,
>> >
>> > trying to retrieve postal codes from the db but only want the query to
>> > look at the first 3 digits of the code tried using
>> > (LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
>> > don't want the query to count individual post codes but instead look
>> > at an area found in the first 3 digits e.g. HA0 3TD is for a
>> > particular house but HA) is for the area Harrow.
>> >
>> >
>> > "SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
>> > CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
>> > COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>>
>> You should mention the db-engine used for a correct answer.
>>
>> I use this with the Jet engine:
>>
>> SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
>> " FROM myTbl GROUP BY left(postcode,3)"
>>
>> '''response.write SQL &"<hr>"
>> set mDATA=CONNECT.Execute(SQL)
>>
>> Response.Write "<table border=1><tr>" & vbcrlf
>> Do Until mDATA.Eof
>> tal = mDATA("tal")
>> pc = mDATA("pc")
>> if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
>> Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
>> mDATA.MoveNext
>> Loop
>> Response.Write "</table>" & vbcrlf
>>
>> --
>> Evertjan.
>> The Netherlands.
>> (Please change the x'es to dots in my emailaddress)
>
>

Good idea. For a direct marketing app, I created a column which took the
first 2 letters of the postcode, which made more localised selections
easier. For a higher level of granularity, I would suggest all those
characters to the left of the space. The first 3 won't always work. BS2 is
in the centre of Bristol and BS21 is Clevedon - over 20 miles away for
instance, but would both be included in a search for Left(PostCode,3) =
"BS2"

--
Mike Brind

"Mark McGinty" <> wrote in message
news:...
>

>
> You might want to consider defining a computed column for the left 3 of
> the postal code, for both ease of reference and db server efficiency.
>
>
> -Mark
>
>
>
>> AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
>> COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>> AreaColl.CursorType = 0
>> AreaColl.CursorLocation = 2
>> AreaColl.LockType = 1
>> AreaColl.Open()
>>
>> AreaColl_numRows = 0
>> %>
>>
>> and in the body
>>
>>
>> <td colspan=2>Top 10 collection post codes</td>
>> </tr>
>> <%
>> While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
>> %>
>> <tr>
>> <td width="100"
>> class="DataSetText"><%=(AreaColl.Fields.Item("COLL _POST_CODE").Value)%></td>
>> <td width="790"
>> class="DataSetText"><%=(AreaColl.Fields.Item("Coun tOfCOLL_POST_CODE").Value)
>> %></td>
>> </tr>
>> <%
>> Repeat1__index=Repeat1__index+1
>> Repeat1__numRows=Repeat1__numRows-1
>> AreaColl.MoveNext()
>> Wend
>> %>
>>
>> Thanks in advance
>>
>>
>> "Evertjan." <> wrote in message
>> news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
>>> Simon Gare wrote on 05 jan 2007 in
>>> microsoft.public.inetserver.asp.general:
>>>
>>> > Hi,
>>> >
>>> > trying to retrieve postal codes from the db but only want the query to
>>> > look at the first 3 digits of the code tried using
>>> > (LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
>>> > don't want the query to count individual post codes but instead look
>>> > at an area found in the first 3 digits e.g. HA0 3TD is for a
>>> > particular house but HA) is for the area Harrow.
>>> >
>>> >
>>> > "SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
>>> > CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
>>> > COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>>>
>>> You should mention the db-engine used for a correct answer.
>>>
>>> I use this with the Jet engine:
>>>
>>> SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
>>> " FROM myTbl GROUP BY left(postcode,3)"
>>>
>>> '''response.write SQL &"<hr>"
>>> set mDATA=CONNECT.Execute(SQL)
>>>
>>> Response.Write "<table border=1><tr>" & vbcrlf
>>> Do Until mDATA.Eof
>>> tal = mDATA("tal")
>>> pc = mDATA("pc")
>>> if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
>>> Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
>>> mDATA.MoveNext
>>> Loop
>>> Response.Write "</table>" & vbcrlf
>>>
>>> --
>>> Evertjan.
>>> The Netherlands.
>>> (Please change the x'es to dots in my emailaddress)
>>
>>
>
>

Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general:

>> I never use a Recordset [you can easily do without it]
>> and the code you show is much to complex for me to read with all those
>> long
>> names with multiple _'s and unnecessary ()'s.
>
> What do you use instead?

Of what?

The multiple _'s or unnecessary ()'s?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

"Evertjan." <> wrote in message
news:Xns98B067CC9A574eejj99@194.109.133.242...
> Mark McGinty wrote on 06 jan 2007 in
> microsoft.public.inetserver.asp.general:
>
>>> I never use a Recordset [you can easily do without it]
>>> and the code you show is much to complex for me to read with all those
>>> long
>>> names with multiple _'s and unnecessary ()'s.
>>
>> What do you use instead?
>
> Of what?
>
> The multiple _'s or unnecessary ()'s?

Instead of Recordset.


-Mark


> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general:

>
> "Evertjan." <> wrote in message
> news:Xns98B067CC9A574eejj99@194.109.133.242...
>> Mark McGinty wrote on 06 jan 2007 in
>> microsoft.public.inetserver.asp.general:
>>
>>>> I never use a Recordset [you can easily do without it]
>>>> and the code you show is much to complex for me to read with all those
>>>> long names with multiple _'s and unnecessary ()'s.
>>>
>>> What do you use instead?
>>
>> Of what?
>>
>> The multiple _'s or unnecessary ()'s?
>
> Instead of Recordset.

Ah, that's what you mean. [I would never have guessed]

Well nothing.

The execute() command already gives me the info I nead when reading with
sql SELECT, and with the UPDATE and INSERT SQL string it does a good job
for writing to the db.


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)