Need Help With SQL

[In My Memory]

I have an MSAccess table that looks like this:

Table: [i]MyThings[/i]

[b]ID[/b]	[b]Item[/b]	[b]TotalVotes[/b]	[b]Voters[/b]
1	Shoes	40		16
2	Coffee	4		2
3	Cat	20		5
4	Skirt	10		10
[i]etc.[/i]

 

I have intended the last two fields as a rating system that works like this:

* Users select an item, and rate it between 1 and 5

 

* The users rating is added to the TotalVotes field, and the Voters field is incremented by 1.

 

* Officially, the rating of an item is equal TotalVotes / Voters. According to my current database, Shoes have a rating of 2.5 (40 accumulated votes / 16 voters), Coffee has a rating of 2, Cats have a of 4, and Skirts are wildly unpopular with a rating of 1.

 

I am trying to create a database search to show me only items with a rating of 2.5 or higher, however the following SQL does not work:

SELECT * FROM MyThings WHERE TotalVotes / Voters > 2.5

 

I have limited experience with SQL, so please do not mind my incredibly newbie question that probably has a very obvious answer .

 

All assistance is appreciated in advance

[BenSon]

According to my current database, Shoes have a rating of 2.5 (40 accumulated votes / 16 voters), Coffee has a rating of 2, Cats have a of 4, and Skirts are wildly unpopular with a rating of 1.

I dont see why your surprised skirts are so unpopular i hardly ever wear mine anymore . However i much prefer a hot cup of black coffee in the morning then a hot cup of black moggy

As for your question I took computing studies for two years and failed very single test. *curse you free arcade!!!! * but i would suggest puting 'TotalVotes / Voters ' in brackets

 

~Scott

[rajama]

... but i would suggest puting 'TotalVotes / Voters ' in brackets

 

~Scott

 

Did that work?

 

You may need square brackets around each column name...

[VikingF]

SELECT * FROM MyThings WHERE ('TotalVotes' / 'Voters') > 2.5

 

might work to?

[Dave]

I just created the table in MySQL, and tested the query (looked fine to me), and it seems to work fine. I guess it's a quirk in Access, because it doesn't really implement SQL very well. Try removing the space between / and the column names? I don't really know

[In My Memory]

Benson, Rajama, VikingF, Dave,

 

I ran the same query this morning without any changes, and it seemed to work fine. It must have been a problem with my Access.

 

But thanks anyway

 

 

All the best,

In My Memory

[Klaynos]

Access is the evil spawn of saten, I've never liked the way it handels sql, or the sql it outputs itself