Hi all. I'm very new to SQL and have almost no background in DBs or dev of any kind.

That said Im trying to setup a website for my rec league softball team and have setup 3 dbs (I know I could have used 1 DB w/3 tabls but Im not going back now LOL). They are Players. Games and Stats_Entry and each has 1 table called Results. The one I want to query is stats_entry and it has the basic baseball columns. Player (from player DB) then Date (from Game DB) then Hits, ABs, Avg, Sgl, Dbl, Tpl, HR, Walks, Sac, FC. I have some basic queries setup to get team stats and can filter by Player or Datebut I need a few queries that are far beyond my meager skills.

1. I need to be able to get player averages by game and totals by palyer. I can get Team total average by using the sum(hits)/sum(abs) but I dont know the proper way to do it all in a single query.

3. I add the numereric(3,3) to the queries to show average as .380 instead of 0.38033434334. I tried about a million different combos without success.

3. I'd like to use the toprow query to get the leader of each stat category by Player so I can see a list of Players with highest avg, hits etc.

I have a few more but if someone can help me with these Ill go farther.

I use an Access DB on a IIS 5.x and Frontpage 02.

My site is located at http://bikecenter.servebeer.com and go to login (No user or pw while testing).

Thank you very much.

Mitch

Well upon further (guessing) I was able to get team totals with this SQL statement

SELECT Player, sum(ABs)
FROM Results
GROUP BY Player


I can then remove the Player field in all columns and get the desired data output.

What I havent figured out is where I would insert the NUMERIC(4,3) or DEC(4,3) DECIMAL(4,3) statements (not positive which command actually works in an Access DB though) in the SQL above so that the Decimal place in AVG doesnt show up randomly (ex: 0.389990989) instead of the desired .390.

Any help on sorting database results by column would be greatly appreciated as well. Ive tried a few suggestions but they werent detailed enough for me to get it working.

Thanks again.
Mitch

Is what Im asking difficult or incorrectly asked or not described correctly? Seems like a simple question to me but I guess Im wrong.

Maybe I picked the wrong forum?>

Oh well, buh bye and thanks for all the suggestions and responses...

Mitch

Hi Mitch,
No there is nothing difficult about what you want to do from what I have read, Im not an SQL guru but have many years experience with it and the first point of advise I would give you is to put all the tables in one database, that is the very purpose of a database. to try to do what you are saying in three databases is ridiculas, especially for a person new to SQL because you have to join databases as well as tables.
There are many alternatives to putting in one DB but all would be the wrong thing to do in such a situation.
 
TRUST ME, go back now.

If this site is going to last you any period of time and you continue to use it you will face lots of problem every time you try to enhance you app.

If you are prepared to do this you will find that you will get a lot of help from this forum. But please be aware that this is an SQL forum and details such as joining databases in Access may be too product specific to get any real answer, hence put you tables in one database and we can help.

If any gurus dissagree with me, please comment and help Mitch out.

Rod





======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

PS, your site looks good.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Thank you very much for the reply! Ill consider creating YADB with 3 tables but at this point Im not really using the Player or Game DB just the Stats DB.

At this point Im pretty much done except Im still unable to get the proper (baseball) decimal position (ex: .389). I can find the syntax (NUMERIC(4,3) dec(4,3 and DECIMAL(4,3)) but cant find where in the sql query it belongs. Here is an example.

SELECT sum(Hits)/(sum(ABs)-sum(Walks))
FROM Results

The above example gives me batting average but it seems no matter where I put the any of the above Numeric,DEC,DECIMAL statements at any position in the query it hoses the query.

Can anyone please tell me how to set my decimal position to a consistent 3,3 or 4,3 precision and scale? Im thinking 4,3 only because occasionally someone will be batting a perfect 1.000.

Anyone?