counting similar entries in a DB

[Alistair]

I have a Dbase that contains film reviews. and I am trying to generate a
dynamic menu based on the contents of the dbase.

lets say the fields are, Film and Review.

so I have some reviews

Film 1
Great film

Film 2
Not Bad

Film 3
hated it

Film 1
didn't think much

Film 1
Prefer the original

now, there are five records, but only three films.

I can easily generate a menu from this by looping round the recordset and
generating a link for each entry, When the link is clicked the search
criteria will be for that film and all records relating to that film will be
displyed. i.e. showreview.asp?film=film1 etc etc. This means I only supply
links to film reviews that actually exist.

but I want to make sure that there is only one menu link for Film1 and not
three.

How would I go about doing this?

I could create a new db for each film but I don't think that idea is very
"streamlined"

many thanks

[Bob Barrows]

Never ask for database-related help without telling us what type and version
of database you are using. More below:
Alistair wrote:

> I have a Dbase that contains film reviews. and I am trying to
> generate a dynamic menu based on the contents of the dbase.
>
> lets say the fields are, Film and Review.
>

<snip>

> now, there are five records, but only three films.
>
> I can easily generate a menu from this by looping round the recordset
> and generating a link for each entry, When the link is clicked the
> search criteria will be for that film and all records relating to
> that film will be displyed. i.e. showreview.asp?film=film1 etc etc.
> This means I only supply links to film reviews that actually exist.
>
> but I want to make sure that there is only one menu link for Film1
> and not three.
>
> How would I go about doing this?

Use the DISTINCT keyword in the query that supplies the records for the
menu. This should be valid in most databases.

Select DISTINCT Film FROM Films ORDER BY Film

>
> I could create a new db for each film but I don't think that idea is
> very "streamlined"

Exactly. It would not result in "normalized" database. Your current design
is fine, unless you are repeating information about each film in each record
about the film, in which case you might want to break out that information
into a second "header" table.

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Steve G]

I know I am pretty new at this stuff, but I am thinking that in his example,
using distinct may still produce 3 instances of Film, since the record in
each instance is a little different. Would MS Datashaping be a way to help
him out here?

Steve G

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u0yScgwlDHA.3316@tk2msftngp13.phx.gbl...

> Never ask for database-related help without telling us what type and

version

> of database you are using. More below:
> Alistair wrote:

> > I have a Dbase that contains film reviews. and I am trying to
> > generate a dynamic menu based on the contents of the dbase.
> >
> > lets say the fields are, Film and Review.
> >

> <snip>

> > now, there are five records, but only three films.
> >
> > I can easily generate a menu from this by looping round the recordset
> > and generating a link for each entry, When the link is clicked the
> > search criteria will be for that film and all records relating to
> > that film will be displyed. i.e. showreview.asp?film=film1 etc etc.
> > This means I only supply links to film reviews that actually exist.
> >
> > but I want to make sure that there is only one menu link for Film1
> > and not three.
> >
> > How would I go about doing this?

>
> Use the DISTINCT keyword in the query that supplies the records for the
> menu. This should be valid in most databases.
>
> Select DISTINCT Film FROM Films ORDER BY Film
>

> >
> > I could create a new db for each film but I don't think that idea is
> > very "streamlined"

>
> Exactly. It would not result in "normalized" database. Your current

design

> is fine, unless you are repeating information about each film in each

record

> about the film, in which case you might want to break out that information
> into a second "header" table.
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bob Barrows]

Steve G wrote:

> I know I am pretty new at this stuff, but I am thinking that in his
> example, using distinct may still produce 3 instances of Film, since
> the record in each instance is a little different.

No. Try it with a table in your database.

It would only produce multiple records for each Film if you included both
fields (Film and Review) in the select statement. Since my suggested Select
statement only includes the Film column, the query will only return 1 record
for each unique Film. I am assuming that the content of the Film column is
unique for each film. If not, then he has other problems.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Steve G]

Quite right...sorry, I misread his post. Thought he was trying to pull both
the film name and the review at the same time.

Steve G

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OXLjInxlDHA.2616@TK2MSFTNGP11.phx.gbl...

> Steve G wrote:

> > I know I am pretty new at this stuff, but I am thinking that in his
> > example, using distinct may still produce 3 instances of Film, since
> > the record in each instance is a little different.

>
> No. Try it with a table in your database.
>
> It would only produce multiple records for each Film if you included both
> fields (Film and Review) in the select statement. Since my suggested

Select

> statement only includes the Film column, the query will only return 1

record

> for each unique Film. I am assuming that the content of the Film column is
> unique for each film. If not, then he has other problems.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>