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

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

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

 

~Scott

 

Did that work?

 

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

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

 

might work to?

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

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

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