Query Help!!

[penguinspeaks]

Ok. I have been messing around with this for a few days now, and am about to shoot someone. I have a table that has columns named as follows: event, tour, round, wins, loses, gig_name Ok, what i need to display is this, wins, loses, then show a total of loses + (wins*5)
THis is what i have tried SELECT tour, round, wins, loses, SUM((wins * 5) + (loses *1)) as Totpoints FROM rounds WHERE event = 'LSIT' AND gig_name = 'Bam' ORDER by tour DESC, round DESC

It shows ok, but then i get an eror on the actual web page:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers

i have tried everything i know of to get it to work.

Ok. Here is an example of what it should look like.

http://www.gig-golf.com/Stats/sqlhelp.asp

PLEASE let me know if this helps let
you know what i need here. Thanks for any help out there.. Bam

 

[penguinspeaks]

Sorry. I got so dang frustrated that I left out some info. I have MS Access DB and am using FP2002. Windows 2002 server.. anything else you need.. just ask Bam

 

[carp]

Given what you have told us, I don't think you have a SQL problem; it sounds more like an Access-centric issue. You might have better results posting this in an Access forum.

 

[r937]

i think the problem is that you are trying to use SUM() which is an aggregate function to do simple expression arithmetic

try this:

[tt]select tour
, round
, wins
, loses
, wins * 5 + loses as Totpoints
from rounds
where event = 'LSIT'
and gig_name = 'Bam'
order
by tour desc
, round desc[/tt]

rudy

http://r937.com/

 

[penguinspeaks]

Thanks for that. It works perfectly! Now. I have a query set up within the DB itself. I have added SQL to that in FP. IT accepts the SQL, but when viewed, it gives an error. This is what I have:

SELECT sumOfwins
,sumOfloses
, sumOfties
, someOfwins * 5 + sumOfloses + sumOfties * 3 as Totpoints
FROM "total query"
WHERE gig_name = 'Bam'

It is accepted as a good statement, but when view on the web page, it gives this error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers

I know I am fairly new to this, but I don't understand the paramater error, or how to correct this. Can you help with this??
And again, thanks so much for the help you have already given to me.Bam

 

[r937]

please show the "total query"

i'm guessing it does not have the gig_name column

or perhaps the quotes are causing a problem, try saving it as TotalQuery instead of "total query"


rudy

 

[penguinspeaks]

Not sure what you mean by show the query. I created it like you would a table in MS Access DB. I made a gig_name column, wins, loses, ties. It has those 4 columns. It totals everything from the wins, loses, and ties columns, and named them sumOfwins, someOfloses, and someOfties. and gig_name is just gig_name. I went ahead and changed the name of the access created query to query1, just so it isn't 2 words, and it doesn't get confused.
So let me know what other info you need.
Bam

 

[r937]

if you can run the query fine in the access sql view, but it doesn't run on your web page, then it's a scripting problem, not a query problem

 

[penguinspeaks]

Hmmmm.. I don't know how it could be the script. I am using Frontpage, and am using the DBRW. I just flop the SQL in there and let it go. SO I don't know how the script would be incorrect. My question is.. it runs fine with the adding of the 2 columns... but when the * goes in there.. it messes up.
SELECT sumOfwins
, sumOfloses
, sumOfties
, someOfwins * 5 + sumOfloses + sumOfties * 3 as Totpoints
FROM "total query"
WHERE gig_name = 'Bam'
LOL I just don't know!!
Thanks for the help thus far bud.
Bam

 

[r937]

i dunno, never seen DBRW, but like i said, if the query runs in the access query sql view, but not in frontpage, then there's your problem

anyhow, here's a workaround

instead of

someOfwins * 5
+ sumOfloses
+ sumOfties * 3
as Totpoints

you can use

someOfwins+someOfwins+someOfwins+someOfwins+someOfwins
+ sumOfloses
+ sumOfties+sumOfties+sumOfties
as Totpoints


rudy

 

[penguinspeaks]

LOL. I actually couldn't get THAT to work. BUt this did for some reason"
SELECT sumOfwins
, sumOfloses
, sumOfties
, sumOfties + sumOfties + sumOfties + sumOfloses + sumOfwins + sumOfwins + sumOfwins * 3
as TotPoints FROM query1
WHERE gig_name = 'Bam'
Weird.. but it works... thanks a bunch bud!!!
Bam

 

[penguinspeaks]

Dang... one more question. If a table has many rows on it, how can I tell it to only display the most recent entry? Like if I am inputing scores... but the ones that I enter are the most current, how do i tell it to only display those? Meaning that the last entry of posts woldn't be correct anymore.
Bam

 

[r937]

what table, what columns, most recent by what column?

select a, b, c, d
from yourtable ZZ
where d =
( select max(d)
from yourtable
where a = ZZ.a
and b = ZZ.b )