Alphabet Paging Menu - ping Aaron :0)

[Laphan]

Hi Guys

Wonder if anybody has found a way round this one.

First show a menu linking system as follows:

0 - 9 A B C D E F G H I J .... etc to Z

Now I've already done it on MS Access driven sites, whereby you click on one
of the above links and it displays all 'data lines' that start with that
letter. This is fine, but the problem is that all these links are active so
there is a possibility that a visitor would click through all 27 links
(alphabet + 1 number link) and find that only a few of these actually
contained 'data lines'.

Is thee a practical way that I can still display the above menu options, but
if they don't have any data then just display them as a non-link item, eg I
still show all of the above, but only C and E are actually underlined links
because they contain data.

In Access as well?????

Thanks

Laphan (reformed x-poster)

[Boris Nikolaevich]

I don't know if this is the 'best' way, but instead of hard-coding the links
on the top of the page, I have used a 2-column view to list all the letters
and whether any data exists for those letters. Sample output would be:

Letter LetterExists
-------- --------------
A 1
B 1
C 0
D 1
...etc.

On SQL Server, I've used UNION SELECT statements to generate the rows. You
could also use a permanent table of letters with(and I'd love to hear
performance comparison). For Access, I don't think the straight SELECT
would work, since I'm not selecting from a specific table, but the permanent
table of letters should work great for Access.

The SQL query I'm using looks something like this:

SELECT 'A' AS Letter,
CASE WHEN EXISTS (SELECT PrimaryKeyColumn FROM DataTable WHERE
DataColumn LIKE 'A%') THEN 1 ELSE 0 END AS LetterExists
UNION SELECT 'B' AS Letter,
CASE WHEN EXISTS (SELECT PrimaryKeyColumn FROM DataTable WHERE
DataColumn LIKE 'B%') THEN 1 ELSE 0 END AS LetterExists

This looks cumbersome when you get all your letters (and numbers), but it
works.

For Access, I imagine I would create a table containing all the letters and
numbers which I want to show in my menu/index. Then I would create a query
like this: (totally untested--it's just thinking out loud to get you
started):

SELECT Letter, CBool(SELECT COUNT(PrimaryKeyColumn) FROM DataTable WHERE
DataColumn LIKE Letter & "*") AS LetterExists FROM tblIndexedLetters

Then, in my ASP Script, I would open a recordset to the view or query and
loop through the recordset. When LetterExists is 0, I would just write the
letter with no link; otherwise I would write the letter with the appropriate
<a> tags.

Hope that gets you started!
--Boris

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3f96d3cd_3@127.0.0.1...

> Hi Guys
>
> Wonder if anybody has found a way round this one.
>
> First show a menu linking system as follows:
>
> 0 - 9 A B C D E F G H I J .... etc to Z
>
> Now I've already done it on MS Access driven sites, whereby you click on

one

> of the above links and it displays all 'data lines' that start with that
> letter. This is fine, but the problem is that all these links are active

so

> there is a possibility that a visitor would click through all 27 links
> (alphabet + 1 number link) and find that only a few of these actually
> contained 'data lines'.
>
> Is thee a practical way that I can still display the above menu options,

but

> if they don't have any data then just display them as a non-link item, eg

I

> still show all of the above, but only C and E are actually underlined

links

> because they contain data.
>
> In Access as well?????
>
> Thanks
>
> Laphan (reformed x-poster)
>
>

[Aaron Bertrand - MVP]

All right, I'm working on it... however why bother showing the letters that
aren't represented in the data?




"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3f96d3cd_3@127.0.0.1...

> Hi Guys
>
> Wonder if anybody has found a way round this one.
>
> First show a menu linking system as follows:
>
> 0 - 9 A B C D E F G H I J .... etc to Z
>
> Now I've already done it on MS Access driven sites, whereby you click on

one

> of the above links and it displays all 'data lines' that start with that
> letter. This is fine, but the problem is that all these links are active

so

> there is a possibility that a visitor would click through all 27 links
> (alphabet + 1 number link) and find that only a few of these actually
> contained 'data lines'.
>
> Is thee a practical way that I can still display the above menu options,

but

> if they don't have any data then just display them as a non-link item, eg

I

> still show all of the above, but only C and E are actually underlined

links

> because they contain data.
>
> In Access as well?????
>
> Thanks
>
> Laphan (reformed x-poster)
>
>

[Aaron Bertrand - MVP]

First, create your names table:


CREATE TABLE Names
(
name VARCHAR(32)
)


Insert some dummy data:


INSERT Names VALUES('33r')
INSERT Names VALUES('9994')
INSERT Names VALUES('Aaron')
INSERT Names VALUES('Bob')
INSERT Names VALUES('Dilbert')
INSERT Names VALUES('Frank')
INSERT Names VALUES('Foobar')
INSERT Names VALUES('Mojo')
INSERT Names VALUES('Zachary')


Now create an alphabet table to left join against:


CREATE TABLE Alphabet
(
letter CHAR(1)
)


And insert the 26 letters:


INSERT Alphabet VALUES('A')
INSERT Alphabet VALUES('B')
INSERT Alphabet VALUES('C')
....
INSERT Alphabet VALUES('X')
INSERT Alphabet VALUES('Y')
INSERT Alphabet VALUES('Z')


Now, to get the numerics, you need this query:


sql1 = "SELECT COUNT(*) FROM table
WHERE ISNUMERIC(LEFT(column, 1))=true"
set rs = conn.execute(sql1)
if rs(0) > 0 then
response.write "<a href=page.asp?letter=0-9>0-9</a>"
else
response.write "0-9"
end if


And to get the letters, you can use a LEFT JOIN:


sql2 = "select n = MIN(N.NAME), a.letter
FROM alphabet a
LEFT JOIN
names n
ON LEFT(n.name,1) = a.letter
GROUP BY a.letter"

set rs = conn.execute(sql2)
do while not rs2.eof
if isnull(rs("n")) then
response.write rs("letter")
else
response.write "<a href=page.asp?letter="
response.write rs("letter") & ">" & rs("letter") & "</a>"
end if
rs.movenext
loop



"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3f96d3cd_3@127.0.0.1...

> Hi Guys
>
> Wonder if anybody has found a way round this one.
>
> First show a menu linking system as follows:
>
> 0 - 9 A B C D E F G H I J .... etc to Z
>
> Now I've already done it on MS Access driven sites, whereby you click on

one

> of the above links and it displays all 'data lines' that start with that
> letter. This is fine, but the problem is that all these links are active

so

> there is a possibility that a visitor would click through all 27 links
> (alphabet + 1 number link) and find that only a few of these actually
> contained 'data lines'.
>
> Is thee a practical way that I can still display the above menu options,

but

> if they don't have any data then just display them as a non-link item, eg

I

> still show all of the above, but only C and E are actually underlined

links

> because they contain data.
>
> In Access as well?????
>
> Thanks
>
> Laphan (reformed x-poster)
>
>

[Laphan]

And that is why ladies and gentleman Aaron is king.......

Many thanks Aaron.

Rgds

Laphan


Aaron Bertrand - MVP <aaron@TRASHaspfaq.com> wrote in message
news:OW8UWzNmDHA.2732@TK2MSFTNGP11.phx.gbl...
First, create your names table:


CREATE TABLE Names
(
name VARCHAR(32)
)


Insert some dummy data:


INSERT Names VALUES('33r')
INSERT Names VALUES('9994')
INSERT Names VALUES('Aaron')
INSERT Names VALUES('Bob')
INSERT Names VALUES('Dilbert')
INSERT Names VALUES('Frank')
INSERT Names VALUES('Foobar')
INSERT Names VALUES('Mojo')
INSERT Names VALUES('Zachary')


Now create an alphabet table to left join against:


CREATE TABLE Alphabet
(
letter CHAR(1)
)


And insert the 26 letters:


INSERT Alphabet VALUES('A')
INSERT Alphabet VALUES('B')
INSERT Alphabet VALUES('C')
....
INSERT Alphabet VALUES('X')
INSERT Alphabet VALUES('Y')
INSERT Alphabet VALUES('Z')


Now, to get the numerics, you need this query:


sql1 = "SELECT COUNT(*) FROM table
WHERE ISNUMERIC(LEFT(column, 1))=true"
set rs = conn.execute(sql1)
if rs(0) > 0 then
response.write "<a href=page.asp?letter=0-9>0-9</a>"
else
response.write "0-9"
end if


And to get the letters, you can use a LEFT JOIN:


sql2 = "select n = MIN(N.NAME), a.letter
FROM alphabet a
LEFT JOIN
names n
ON LEFT(n.name,1) = a.letter
GROUP BY a.letter"

set rs = conn.execute(sql2)
do while not rs2.eof
if isnull(rs("n")) then
response.write rs("letter")
else
response.write "<a href=page.asp?letter="
response.write rs("letter") & ">" & rs("letter") & "</a>"
end if
rs.movenext
loop



"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3f96d3cd_3@127.0.0.1...

> Hi Guys
>
> Wonder if anybody has found a way round this one.
>
> First show a menu linking system as follows:
>
> 0 - 9 A B C D E F G H I J .... etc to Z
>
> Now I've already done it on MS Access driven sites, whereby you click on

one

> of the above links and it displays all 'data lines' that start with that
> letter. This is fine, but the problem is that all these links are active

so

> there is a possibility that a visitor would click through all 27 links
> (alphabet + 1 number link) and find that only a few of these actually
> contained 'data lines'.
>
> Is thee a practical way that I can still display the above menu options,

but

> if they don't have any data then just display them as a non-link item, eg

I

> still show all of the above, but only C and E are actually underlined

links

> because they contain data.
>
> In Access as well?????
>
> Thanks
>
> Laphan (reformed x-poster)
>
>

[Laphan]

Because it's clever ;0)




Aaron Bertrand - MVP <aaron@TRASHaspfaq.com> wrote in message
news:uZzpXaNmDHA.2436@TK2MSFTNGP09.phx.gbl...
All right, I'm working on it... however why bother showing the letters that
aren't represented in the data?




"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3f96d3cd_3@127.0.0.1...

> Hi Guys
>
> Wonder if anybody has found a way round this one.
>
> First show a menu linking system as follows:
>
> 0 - 9 A B C D E F G H I J .... etc to Z
>
> Now I've already done it on MS Access driven sites, whereby you click on

one

> of the above links and it displays all 'data lines' that start with that
> letter. This is fine, but the problem is that all these links are active

so

> there is a possibility that a visitor would click through all 27 links
> (alphabet + 1 number link) and find that only a few of these actually
> contained 'data lines'.
>
> Is thee a practical way that I can still display the above menu options,

but

> if they don't have any data then just display them as a non-link item, eg

I

> still show all of the above, but only C and E are actually underlined

links

> because they contain data.
>
> In Access as well?????
>
> Thanks
>
> Laphan (reformed x-poster)
>
>

[Tom B]

Not meaning to diminish Aaron's royalty, but isn't Aaron's solution
essentially the same as Boris'?


"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3f96f914_3@127.0.0.1...

> And that is why ladies and gentleman Aaron is king.......
>
> Many thanks Aaron.
>
> Rgds
>
> Laphan
>
>
> Aaron Bertrand - MVP <aaron@TRASHaspfaq.com> wrote in message
> news:OW8UWzNmDHA.2732@TK2MSFTNGP11.phx.gbl...
> First, create your names table:
>
>
> CREATE TABLE Names
> (
> name VARCHAR(32)
> )
>
>
> Insert some dummy data:
>
>
> INSERT Names VALUES('33r')
> INSERT Names VALUES('9994')
> INSERT Names VALUES('Aaron')
> INSERT Names VALUES('Bob')
> INSERT Names VALUES('Dilbert')
> INSERT Names VALUES('Frank')
> INSERT Names VALUES('Foobar')
> INSERT Names VALUES('Mojo')
> INSERT Names VALUES('Zachary')
>
>
> Now create an alphabet table to left join against:
>
>
> CREATE TABLE Alphabet
> (
> letter CHAR(1)
> )
>
>
> And insert the 26 letters:
>
>
> INSERT Alphabet VALUES('A')
> INSERT Alphabet VALUES('B')
> INSERT Alphabet VALUES('C')
> ...
> INSERT Alphabet VALUES('X')
> INSERT Alphabet VALUES('Y')
> INSERT Alphabet VALUES('Z')
>
>
> Now, to get the numerics, you need this query:
>
>
> sql1 = "SELECT COUNT(*) FROM table
> WHERE ISNUMERIC(LEFT(column, 1))=true"
> set rs = conn.execute(sql1)
> if rs(0) > 0 then
> response.write "<a href=page.asp?letter=0-9>0-9</a>"
> else
> response.write "0-9"
> end if
>
>
> And to get the letters, you can use a LEFT JOIN:
>
>
> sql2 = "select n = MIN(N.NAME), a.letter
> FROM alphabet a
> LEFT JOIN
> names n
> ON LEFT(n.name,1) = a.letter
> GROUP BY a.letter"
>
> set rs = conn.execute(sql2)
> do while not rs2.eof
> if isnull(rs("n")) then
> response.write rs("letter")
> else
> response.write "<a href=page.asp?letter="
> response.write rs("letter") & ">" & rs("letter") & "</a>"
> end if
> rs.movenext
> loop
>
>
>
> "Laphan" <news@DoNotEmailMe.co.uk> wrote in message
> news:3f96d3cd_3@127.0.0.1...

> > Hi Guys
> >
> > Wonder if anybody has found a way round this one.
> >
> > First show a menu linking system as follows:
> >
> > 0 - 9 A B C D E F G H I J .... etc to Z
> >
> > Now I've already done it on MS Access driven sites, whereby you click on

> one

> > of the above links and it displays all 'data lines' that start with that
> > letter. This is fine, but the problem is that all these links are

active

> so

> > there is a possibility that a visitor would click through all 27 links
> > (alphabet + 1 number link) and find that only a few of these actually
> > contained 'data lines'.
> >
> > Is thee a practical way that I can still display the above menu options,

> but

> > if they don't have any data then just display them as a non-link item,

eg

> I

> > still show all of the above, but only C and E are actually underlined

> links

> > because they contain data.
> >
> > In Access as well?????
> >
> > Thanks
> >
> > Laphan (reformed x-poster)
> >
> >

>
>
>
>

[Aaron Bertrand - MVP]

> Not meaning to diminish Aaron's royalty, but isn't Aaron's solution

> essentially the same as Boris'?

Not meaning to defend myself where it isn't necessary, but while Boris' post
says it appeared before mine, it actually appeared much later (and I'm
pretty sure it was after Laphan had replied). This could either be due to
Boris having a system clock setting earlier than reality (or an time zone
further east or west, I forget which direction would cause this), or some
delay in the post replicating (e.g. if he posted to a different news server
from the rest of us).

I'm sure it was not Laphan's intention to ignore / disregard Boris'
suggestion which, admittedly, is quite similar to mine. I think it just
wasn't available at the time of Laphan's replies.

A

[Tom B]

Ah...I see. I hadn't looked in this newsgroup until this morning (E.S.T.)
and saw all of the postings. As you said, I went by the posted date/time.

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:eXZr$YXmDHA.2456@TK2MSFTNGP09.phx.gbl...

> > Not meaning to diminish Aaron's royalty, but isn't Aaron's solution
> > essentially the same as Boris'?

>
> Not meaning to defend myself where it isn't necessary, but while Boris'

post

> says it appeared before mine, it actually appeared much later (and I'm
> pretty sure it was after Laphan had replied). This could either be due to
> Boris having a system clock setting earlier than reality (or an time zone
> further east or west, I forget which direction would cause this), or some
> delay in the post replicating (e.g. if he posted to a different news

server

> from the rest of us).
>
> I'm sure it was not Laphan's intention to ignore / disregard Boris'
> suggestion which, admittedly, is quite similar to mine. I think it just
> wasn't available at the time of Laphan's replies.
>
> A
>
>

[Chris Hohmann]

"Tom B" <shuckle@hotmail.com> wrote in message
news:%2331sv3XmDHA.1960@TK2MSFTNGP12.phx.gbl...

> Ah...I see. I hadn't looked in this newsgroup until this morning

(E.S.T.)

> and saw all of the postings. As you said, I went by the posted

date/time.

>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:eXZr$YXmDHA.2456@TK2MSFTNGP09.phx.gbl...

> > > Not meaning to diminish Aaron's royalty, but isn't Aaron's

solution

> > > essentially the same as Boris'?

> >
> > Not meaning to defend myself where it isn't necessary, but while

Boris'

> post

> > says it appeared before mine, it actually appeared much later (and

I'm

> > pretty sure it was after Laphan had replied). This could either be

due to

> > Boris having a system clock setting earlier than reality (or an time

zone

> > further east or west, I forget which direction would cause this), or

some

> > delay in the post replicating (e.g. if he posted to a different news

> server

> > from the rest of us).
> >
> > I'm sure it was not Laphan's intention to ignore / disregard Boris'
> > suggestion which, admittedly, is quite similar to mine. I think it

just

> > wasn't available at the time of Laphan's replies.
> >
> > A

While I am admittedly late to the party, here's a variation on the
theme:

[spLinks]
SELECT DISTINCT
IIF(name Like "[0-9]%","0-9",Left(name,1))
FROM
Names

<%
Const sLinks = "0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z"
Dim cn,rs,sPattern,re
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "FILE NAME=C:\SomeDirOutsideAppRoot\MyConnection.UDL"
cn.spLinks rs
sPattern = rs.GetString(2,,"","|")
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Set re = New RegExp
re.Global = True
re.Pattern = Left(sPattern,Len(sPattern)-1)
Response.Write re.Replace(sLinks,"<a href='page.asp?letter=$&'>$&</a>")
Set re = Nothing
%>

-Chris Hohmann

[Boris Nikolaevich]

Wow! Sorry to bring up an old post, but I haven't been around in a while.
Sinus infection. TMI.

Anyway, I'm 100% flattered that my solution was similar to Aaron's. Why
anyone would complain that his post was similar to mine--and not the other
way around--is beyond me!

I, too, noticed that my response showed up in the group quite some time
after I actually posted it. My system clock is correct, so I'm blaming it
on internet congestion, server relay, or replication delay.

I think it's great that enough experienced people monitor and use this
newsgroup that one problem gets several possible solutions--and when some of
the solutions are similar, it just provides validation that we're on the
right track! (I believe the Russian verse translates as "...in the mouths of
two or three witnesses..." [Ïðè óñòàõ äâóõ èëè òð¸õ ñâèäåòåëåé áóäåò òâ¸ðäî
âñÿêîå ñëîâî.] Trust me, it applies to all truth, including ones and
zeros.)

Thanks to all who contribute here, especially those who can be considered
"royalty" (as Tom indirectly put it). This group has been an INVALUABLE
resource in my professional development.

--Boris


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:eXZr$YXmDHA.2456@TK2MSFTNGP09.phx.gbl...

> > Not meaning to diminish Aaron's royalty, but isn't Aaron's solution
> > essentially the same as Boris'?

>
> Not meaning to defend myself where it isn't necessary, but while Boris'

post

> says it appeared before mine, it actually appeared much later (and I'm
> pretty sure it was after Laphan had replied). This could either be due to
> Boris having a system clock setting earlier than reality (or an time zone
> further east or west, I forget which direction would cause this), or some
> delay in the post replicating (e.g. if he posted to a different news

server

> from the rest of us).
>
> I'm sure it was not Laphan's intention to ignore / disregard Boris'
> suggestion which, admittedly, is quite similar to mine. I think it just
> wasn't available at the time of Laphan's replies.
>
> A
>
>