|
 |
access_asp thread: counting duplicates
Message #1 by jake williamson 28 <jake.williamson@2...> on Thu, 09 May 2002 12:39:35 +0100
|
|
hello!
i'm trying to build a results page that takes this style of info in a
database:
MOVIENAME
Top Gun
Top Gun
Top Gun
The Naked Gun
The Naked Gun
The Naked Gun
The Naked Gun
Some Like It Hot
Some Like It Hot
and presents it on the page as numbered results, eg:
Top Gun = 3 requests
The Naked Gun = 4 requests
Some Like It Hot = 2 requests
i know that you cna use the SELECT DISTINCT MOVIENAME sql to combine the
results but can you then count them?
any ideas oh guru's??!!
cheers,
jake
Message #2 by "Ken Schaefer" <ken@a...> on Fri, 10 May 2002 11:46:17 +1000
|
|
SELECT
a.MovieName,
COUNT(a.MovieName)
FROM
Movies AS a
GROUP BY
a.MovieName
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "jake williamson 28" <jake.williamson@2...>
Subject: [access_asp] counting duplicates
: hello!
:
: i'm trying to build a results page that takes this style of info in a
: database:
:
: MOVIENAME
: Top Gun
: Top Gun
: Top Gun
: The Naked Gun
: The Naked Gun
: The Naked Gun
: The Naked Gun
: Some Like It Hot
: Some Like It Hot
:
: and presents it on the page as numbered results, eg:
:
: Top Gun = 3 requests
: The Naked Gun = 4 requests
: Some Like It Hot = 2 requests
:
: i know that you cna use the SELECT DISTINCT MOVIENAME sql to combine the
: results but can you then count them?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by jake williamson 28 <jake.williamson@2...> on Fri, 10 May 2002 13:24:29 +0100
|
|
hello!
now i got the counting duplicates sorted (thank you!) i need to get it to
select between dates......
this is my SQL so far:
SELECT MOVIEGENRE, count(*)
FROM INFO Group By MOVIEGENRE
my date column is imaginatively called DATE and is in the 'Short Date'
format. i need to select the data by month
so i would say i need a more than/less than:
SELECT MOVIEGENRE, count(*)
FROM INFO Group By MOVIEGENRE
WHERE DATE => 01/04/2002 AND DATE <= 01/05/2002
to display may's data. thing is this dont work...
i also need to get the selection from a pop up menu...
man, this is hard work!!
any ideas oh guru's??!!
cheers,
jake
Message #4 by "Ken Schaefer" <ken@a...> on Mon, 13 May 2002 16:27:55 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "jake williamson 28" <jake.williamson@2...>
Subject: [access_asp] counting duplicates
<snip>
: my date column is imaginatively called DATE and is in the 'Short Date'
: format. i need to select the data by month
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
That's a bad idea - never use reserved words for field names, variable names
etc. It only leads to grief in the long term. Why don't you call it
something imaginative like ReleaseDate or something?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: so i would say i need a more than/less than:
:
: SELECT MOVIEGENRE, count(*)
: FROM INFO Group By MOVIEGENRE
: WHERE DATE => 01/04/2002 AND DATE <= 01/05/2002
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You need to use the correct delimiters for dates. Which is a #, otherwise
you are presenting Access with a mathematical expression (1 divided by 4
divided by 2002, which is a small number of milliseconds after 1/1/1900
12:00:00 AM).
SELECT
Moviegenre,
COUNT(Moviegenre)
FROM
Info
GROUP BY
MovieGenre
WHERE
ReleaseDate >= #2002/04/01#
AND
ReleaseDate < #2002/05/01#
www.adopenstatic.com/faq/dateswithaccess.asp should also help you (you need
to use ISO date format to get around some quirky Access date handling
issues)
Cheers
Ken
Message #5 by "Maha Arupputhan Pappan" <mahap@p...> on Mon, 13 May 2002 07:41:16
|
|
> hello!
i'm trying to build a results page that takes this style of info in a
database:
MOVIENAME
Top Gun
Top Gun
Top Gun
The Naked Gun
The Naked Gun
The Naked Gun
The Naked Gun
Some Like It Hot
Some Like It Hot
and presents it on the page as numbered results, eg:
Top Gun = 3 requests
The Naked Gun = 4 requests
Some Like It Hot = 2 requests
i know that you cna use the SELECT DISTINCT MOVIENAME sql to combine the
results but can you then count them?
any ideas oh guru's??!!
cheers,
jake
Hi,
I am not a guru and correct if I am wrong. If you are using VBScript, I
think it is possible to count each group by using the DCOUNT() method.
Thanks,
Maha
Message #6 by jake williamson 28 <jake.williamson@2...> on Tue, 14 May 2002 11:23:33 +0100
|
|
hey ken,
thanks for that, dates are a nightmare....
it seems that even though my db shows the dates as english (01/05/2002) the
sql request needs to have them set up as usa style (2002/05/01) - really
weird...
anyways, this is the code i've put in:
SELECT MOVIEGENRE, Count(*)
FROM INFO
WHERE ((INFO.DATE >= #2002/04/01#) AND (INFO.DATE <= #2002/05/01#))
GROUP BY MOVIEGENRE
all seems to work! i'm gonna try to submit the date via popup menu now. i'm
guessing i just replace the dates with 'varToo' and 'varFrom'...
thanks again for your help,
jake
on 13/5/02 7:27, Ken Schaefer at ken@a... wrote:
> You need to use the correct delimiters for dates. Which is a #, otherwise
> you are presenting Access with a mathematical expression (1 divided by 4
> divided by 2002, which is a small number of milliseconds after 1/1/1900
> 12:00:00 AM).
>
> SELECT Moviegenre, COUNT(Moviegenre)
> FROM Info
> GROUP BY MovieGenre WHERE ReleaseDate >= #2002/04/01# AND ReleaseDate <
> #2002/05/01#
>
> http://www.adopenstatic.com/faq/dateswithaccess.asp should also help you (you
> need
> to use ISO date format to get around some quirky Access date handling
> issues)
>
> Cheers
> Ken
|
|
|
View Cart
