|
 |
asp_databases thread: select query help needed
Message #1 by "Eric Van Camp" <eric@a...> on Sun, 8 Sep 2002 10:59:29 +0200
|
|
i have an access database which contains oa stats and statspaths as tables
stats has all references to the other tables in it
now if i run the following query:*****
SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL FROM statsPaths
INNER JOIN STATS on statsPaths.pathid=stats.pathid where
(9/1/2002<=stats.date ) GROUP BY PathName,stats.date,total ORDER BY
COUNT(DATE) DESC
***end query
all goes well and i get the correct result out of the two tables stats and
statspaths...
example of results
Pathname Date TOTAL
http://192.168.254.10:405/history.asp 9/4/2002 6
http://192.168.254.10:405/index.asp 9/4/2002 6
http://192.168.254.10:405/history.asp 9/3/2002 4
BUT
if i execute the following query
*****query****
SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL FROM statsPaths
INNER JOIN STATS on statsPaths.pathid=stats.pathid where
(9/1/2002<=stats.date and stats.date<.9/8/2002 ) GROUP BY
PathName,stats.date,total ORDER BY COUNT(DATE) DESC
*******end query********
nothing is shown, the result set is empty ALTHOUGH there are some records
which have to be shown!!!
no sql error is shown, so the select query is correct for it's syntax...
anyone knows why.....
Met vriendelijke groet,
Eric Van Camp
apeiron
Trolieberg 74
3010 Kessel-lo
t:016/35.69.99
f:016/35.57.90
mobile:0479/598.599
Message #2 by "Kim Iwan Hansen" <kimiwan@k...> on Sun, 8 Sep 2002 16:29:39 +0200
|
|
You need to use ## around date values in access, otherwise it'll just
evaluate the 9/1/2002 and return the product.
VBScript works the same way, so you can try the following in asp to see the
difference:
d1 = #9/1/2002#
d2 = 9/1/2002
Response.Write(d1 & "... " & formatdatetime(d1,1) & "<hr>")
Response.Write(d2 & "... " & formatdatetime(d2,1))
-Kim
> -----Original Message-----
> From: Eric Van Camp [mailto:eric@a...]
> Sent: 8. september 2002 10:59
> To: ASP Databases
> Subject: [asp_databases] select query help needed
>
>
> i have an access database which contains oa stats and statspaths as tables
> stats has all references to the other tables in it
> now if i run the following query:*****
> SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL FROM statsPaths
> INNER JOIN STATS on statsPaths.pathid=stats.pathid where
> (9/1/2002<=stats.date ) GROUP BY PathName,stats.date,total ORDER BY
> COUNT(DATE) DESC
> ***end query
> all goes well and i get the correct result out of the two tables stats and
> statspaths...
>
> example of results
> Pathname Date
> TOTAL
> http://192.168.254.10:405/history.asp 9/4/2002 6
> http://192.168.254.10:405/index.asp 9/4/2002 6
> http://192.168.254.10:405/history.asp 9/3/2002 4
> BUT
> if i execute the following query
> *****query****
> SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL FROM statsPaths
> INNER JOIN STATS on statsPaths.pathid=stats.pathid where
> (9/1/2002<=stats.date and stats.date<.9/8/2002 ) GROUP BY
> PathName,stats.date,total ORDER BY COUNT(DATE) DESC
> *******end query********
> nothing is shown, the result set is empty ALTHOUGH there are some records
> which have to be shown!!!
> no sql error is shown, so the select query is correct for it's syntax...
>
> anyone knows why.....
>
>
> Met vriendelijke groet,
> Eric Van Camp
> apeiron
> Trolieberg 74
> 3010 Kessel-lo
> t:016/35.69.99
> f:016/35.57.90
> mobile:0479/598.599
>
>
Message #3 by "Drew, Ron" <RDrew@B...> on Tue, 10 Sep 2002 17:16:51 -0400
|
|
Why not use BETWEEN
where stats.date BETWEEN 9/1/2002 and 9/8/2002
-----Original Message-----
From: Eric Van Camp [mailto:eric@a...]
Sent: Sunday, September 08, 2002 4:59 AM
To: ASP Databases
Subject: [asp_databases] select query help needed
i have an access database which contains oa stats and statspaths as
tables stats has all references to the other tables in it now if i run
the following query:*****
SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
FROM statsPaths
INNER JOIN STATS on statsPaths.pathid=3Dstats.pathid
where (9/1/2002<=3Dstats.date )
GROUP BY PathName,stats.date,total
ORDER BY COUNT(DATE) DESC
***end query
all goes well and i get the correct result out of the two tables stats
and statspaths...
example of results
Pathname Date
TOTAL
http://192.168.254.10:405/history.asp 9/4/2002 6
http://192.168.254.10:405/index.asp 9/4/2002 6
http://192.168.254.10:405/history.asp 9/3/2002 4
BUT
if i execute the following query
*****query****
SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
FROM statsPaths
INNER JOIN STATS on statsPaths.pathid=3Dstats.pathid
where (9/1/2002<=3Dstats.date and stats.date<.9/8/2002 )
GROUP BY PathName,stats.date,total
ORDER BY COUNT(DATE) DESC
*******end query******** nothing is shown, the result set is empty
ALTHOUGH there are some records which have to be shown!!! no sql error
is shown, so the select query is correct for it's syntax...
anyone knows why.....
Met vriendelijke groet,
Eric Van Camp
apeiron
Trolieberg 74
3010 Kessel-lo
t:016/35.69.99
f:016/35.57.90
mobile:0479/598.599
Message #4 by John Pirkey <mailjohnny101@y...> on Tue, 10 Sep 2002 14:24:47 -0700 (PDT)
|
|
i hope this is just a typo when you added it to the email, but
SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
FROM statsPaths
INNER JOIN STATS on statsPaths.pathid=stats.pathid
*************************************************
where (9/1/2002<=stats.date and stats.date<.9/8/2002 )
*************************************************
GROUP BY PathName,stats.date,total
ORDER BY COUNT(DATE) DESC
the line seperated out has an extra period, just before the actual date. that might
cause some problems...
just an observation.
john
--- "Drew, Ron" <RDrew@B...> wrote:
> Why not use BETWEEN
> where stats.date BETWEEN 9/1/2002 and 9/8/2002
>
> -----Original Message-----
> From: Eric Van Camp [mailto:eric@a...]
> Sent: Sunday, September 08, 2002 4:59 AM
> To: ASP Databases
> Subject: [asp_databases] select query help needed
>
> i have an access database which contains oa stats and statspaths as
> tables stats has all references to the other tables in it now if i run
> the following query:*****
> SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
> FROM statsPaths
> INNER JOIN STATS on statsPaths.pathid=stats.pathid
> where (9/1/2002<=stats.date )
> GROUP BY PathName,stats.date,total
> ORDER BY COUNT(DATE) DESC
> ***end query
> all goes well and i get the correct result out of the two tables stats
> and statspaths...
>
> example of results
> Pathname Date
> TOTAL
> http://192.168.254.10:405/history.asp 9/4/2002 6
> http://192.168.254.10:405/index.asp 9/4/2002 6
> http://192.168.254.10:405/history.asp 9/3/2002 4
> BUT
> if i execute the following query
> *****query****
> SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
> FROM statsPaths
> INNER JOIN STATS on statsPaths.pathid=stats.pathid
> where (9/1/2002<=stats.date and stats.date<.9/8/2002 )
> GROUP BY PathName,stats.date,total
> ORDER BY COUNT(DATE) DESC
> *******end query******** nothing is shown, the result set is empty
> ALTHOUGH there are some records which have to be shown!!! no sql error
> is shown, so the select query is correct for it's syntax...
>
> anyone knows why.....
>
>
> Met vriendelijke groet,
> Eric Van Camp
> apeiron
> Trolieberg 74
> 3010 Kessel-lo
> t:016/35.69.99
> f:016/35.57.90
> mobile:0479/598.599
>
>
>
=====
----------------------------
John Pirkey
MCSD
http://www.stlvbug.org
__________________________________________________
Yahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost
http://dir.remember.yahoo.com/tribute
Message #5 by "Drew, Ron" <RDrew@B...> on Tue, 10 Sep 2002 17:36:41 -0400
|
|
No typo...I just edited the script to separate the statements then took
a look. Did not notice, but you are correct, there is an extra "." in
his statement.
-----Original Message-----
From: John Pirkey [mailto:mailjohnny101@y...]
Sent: Tuesday, September 10, 2002 5:25 PM
To: ASP Databases
Subject: [asp_databases] RE: select query help needed
i hope this is just a typo when you added it to the email, but
SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
FROM statsPaths
INNER JOIN STATS on statsPaths.pathid=3Dstats.pathid
*************************************************
where (9/1/2002<=3Dstats.date and stats.date<.9/8/2002 )
*************************************************
GROUP BY PathName,stats.date,total
ORDER BY COUNT(DATE) DESC
the line seperated out has an extra period, just before the actual date.
that might
cause some problems...
just an observation.
john
--- "Drew, Ron" <RDrew@B...> wrote:
> Why not use BETWEEN
> where stats.date BETWEEN 9/1/2002 and 9/8/2002
>
> -----Original Message-----
> From: Eric Van Camp [mailto:eric@a...]
> Sent: Sunday, September 08, 2002 4:59 AM
> To: ASP Databases
> Subject: [asp_databases] select query help needed
>
> i have an access database which contains oa stats and statspaths as
> tables stats has all references to the other tables in it now if i run
> the following query:*****
> SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
> FROM statsPaths
> INNER JOIN STATS on statsPaths.pathid=3Dstats.pathid
> where (9/1/2002<=3Dstats.date )
> GROUP BY PathName,stats.date,total
> ORDER BY COUNT(DATE) DESC
> ***end query
> all goes well and i get the correct result out of the two tables stats
> and statspaths...
>
> example of results
> Pathname Date
> TOTAL
> http://192.168.254.10:405/history.asp 9/4/2002 6
> http://192.168.254.10:405/index.asp 9/4/2002 6
> http://192.168.254.10:405/history.asp 9/3/2002 4
> BUT
> if i execute the following query
> *****query****
> SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
> FROM statsPaths
> INNER JOIN STATS on statsPaths.pathid=3Dstats.pathid
> where (9/1/2002<=3Dstats.date and stats.date<.9/8/2002 )
> GROUP BY PathName,stats.date,total
> ORDER BY COUNT(DATE) DESC
> *******end query******** nothing is shown, the result set is empty
> ALTHOUGH there are some records which have to be shown!!! no sql error
> is shown, so the select query is correct for it's syntax...
>
> anyone knows why.....
>
>
> Met vriendelijke groet,
> Eric Van Camp
> apeiron
> Trolieberg 74
> 3010 Kessel-lo
> t:016/35.69.99
> f:016/35.57.90
> mobile:0479/598.599
>
>
>
=3D=3D=3D=3D=3D
----------------------------
John Pirkey
MCSD
http://www.stlvbug.org
__________________________________________________
Yahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost
http://dir.remember.yahoo.com/tribute
Message #6 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 11 Sep 2002 01:40:21 +0200
|
|
Maybe I didn't explain myself clear enough in a previous reply to this
question:
You need to use ## around date values in access, otherwise it'll just
evaluate the 9/1/2002 and return the product.
9 divided by 1 divided by 2002 equals 0,004955, which in turn equals the
date Saturday, December 30, 1899.
-Kim
> -----Original Message-----
> From: Drew, Ron [mailto:RDrew@B...]
> Sent: 10. september 2002 23:37
> To: ASP Databases
> Subject: [asp_databases] RE: select query help needed
>
>
> No typo...I just edited the script to separate the statements then took
> a look. Did not notice, but you are correct, there is an extra "." in
> his statement.
>
> -----Original Message-----
> From: John Pirkey [mailto:mailjohnny101@y...]
> Sent: Tuesday, September 10, 2002 5:25 PM
> To: ASP Databases
> Subject: [asp_databases] RE: select query help needed
>
>
> i hope this is just a typo when you added it to the email, but
>
> SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
> FROM statsPaths
> INNER JOIN STATS on statsPaths.pathid=stats.pathid
>
> *************************************************
> where (9/1/2002<=stats.date and stats.date<.9/8/2002 )
> *************************************************
>
> GROUP BY PathName,stats.date,total
> ORDER BY COUNT(DATE) DESC
>
> the line seperated out has an extra period, just before the actual date.
> that might
> cause some problems...
>
> just an observation.
>
> john
>
> --- "Drew, Ron" <RDrew@B...> wrote:
> > Why not use BETWEEN
> > where stats.date BETWEEN 9/1/2002 and 9/8/2002
> >
> > -----Original Message-----
> > From: Eric Van Camp [mailto:eric@a...]
> > Sent: Sunday, September 08, 2002 4:59 AM
> > To: ASP Databases
> > Subject: [asp_databases] select query help needed
> >
> > i have an access database which contains oa stats and statspaths as
> > tables stats has all references to the other tables in it now if i run
> > the following query:*****
> > SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
> > FROM statsPaths
> > INNER JOIN STATS on statsPaths.pathid=stats.pathid
> > where (9/1/2002<=stats.date )
> > GROUP BY PathName,stats.date,total
> > ORDER BY COUNT(DATE) DESC
> > ***end query
> > all goes well and i get the correct result out of the two tables stats
> > and statspaths...
> >
> > example of results
> > Pathname Date
> > TOTAL
> > http://192.168.254.10:405/history.asp 9/4/2002 6
> > http://192.168.254.10:405/index.asp 9/4/2002 6
> > http://192.168.254.10:405/history.asp 9/3/2002 4
> > BUT
> > if i execute the following query
> > *****query****
> > SELECT TOP 10 Pathname, stats.Date,COUNT(statid) as TOTAL
> > FROM statsPaths
> > INNER JOIN STATS on statsPaths.pathid=stats.pathid
> > where (9/1/2002<=stats.date and stats.date<.9/8/2002 )
> > GROUP BY PathName,stats.date,total
> > ORDER BY COUNT(DATE) DESC
> > *******end query******** nothing is shown, the result set is empty
> > ALTHOUGH there are some records which have to be shown!!! no sql error
> > is shown, so the select query is correct for it's syntax...
> >
> > anyone knows why.....
> >
> >
> > Met vriendelijke groet,
> > Eric Van Camp
> > apeiron
> > Trolieberg 74
> > 3010 Kessel-lo
> > t:016/35.69.99
> > f:016/35.57.90
> > mobile:0479/598.599
> >
> >
> >
>
>
> =====
>
> ----------------------------
> John Pirkey
> MCSD
> http://www.stlvbug.org
>
>
> __________________________________________________
> Yahoo! - We Remember
> 9-11: A tribute to the more than 3,000 lives lost
> http://dir.remember.yahoo.com/tribute
>
>
|
|
|
View Cart
