Rating system: Query to get a rate for each entry found.

  • Thread starter Thread starter largie
  • Start date Start date
L

largie

Hi

I currently have 2 tables with this information:
MoviesTable:
ID - Autonumber
Title - Text[50]

Rate:
RID - Autonumber
ID - Int
Value - Int

The movie-table are filled with these entries
ID = 1, Title = "Pulp Fiction"
ID = 2, Title = "Batman begins"
ID = 3, Title = "Insomnia"

The rate-table are filled with these entries (note value are a number
between 0 and 10)
RID = 1, ID = 1, Value = 8
RID = 2, ID = 1, Value = 7
RID = 3, ID = 1, Value = 5
RID = 4, ID = 2, Value = 5

I've tried different solutions to get the sum of a movie using one
query (with a subquery) but I can't get it to work. As a result I want
this
ID = 1, Value = 20, Count = 3
ID = 2, Value = 5, Count = 1
ID = 3, Value = 0, Count = 0

Hopefully someone could help me, I'm really stuck on this little
query. I'm currently using Microsoft Access 2003.

Thank you for any help, cheers
Large
 
Try this one on for size. It uses simple aggregate functions to get
the count() and sum() of values. It then uses a right join on the
MoviesTable so that you get results for ID=3.

Paste this in using SQL View in Query Designer.

SELECT
M.ID,
Count(R.Value) AS NumRatings,
Sum(R.Value) AS TotalRatings
FROM
Rate AS R
RIGHT JOIN
MoviesTable AS M
ON R.ID = M.ID
GROUP BY M.ID;

Cheers,
Jason Lepack
 
Thanks alot! It worked like a sharm! :)

Try this one on for size. It uses simple aggregate functions toget
the count() and sum() of values. It then uses a right join on the
MoviesTable so that yougetresults for ID=3.

Paste this in using SQL View inQueryDesigner.

SELECT
M.ID,
Count(R.Value) AS NumRatings,
Sum(R.Value) AS TotalRatings
FROM
RateAS R
RIGHT JOIN
MoviesTable AS M
ON R.ID = M.ID
GROUP BY M.ID;

Cheers,
Jason Lepack

Hi
Click to expand...
I currently have 2 tables with this information:
MoviesTable:
ID - Autonumber
Title - Text[50]
Click to expand...
Rate:
RID - Autonumber
ID - Int
Value - Int
Click to expand...
The movie-table are filled with these entries
ID = 1, Title = "Pulp Fiction"
ID = 2, Title = "Batman begins"
ID = 3, Title = "Insomnia"
Click to expand...
Therate-table are filled with these entries (note value are a number
between 0 and 10)
RID = 1, ID = 1, Value = 8
RID = 2, ID = 1, Value = 7
RID = 3, ID = 1, Value = 5
RID = 4, ID = 2, Value = 5
Click to expand...
I've tried different solutions togetthe sum of a movie using one
query(with a subquery) but I can'tgetit to work. As a result I want
this
ID = 1, Value = 20, Count = 3
ID = 2, Value = 5, Count = 1
ID = 3, Value = 0, Count = 0
Click to expand...
Hopefully someone could help me, I'm really stuck on this little
query. I'm currently using Microsoft Access 2003.
Click to expand...
Thank you for any help, cheers
Large
Click to expand...
Click to expand...
 
Very welcome!

Thanks alot! It worked like a sharm! :)

Try this one on for size. It uses simple aggregate functions toget
the count() and sum() of values. It then uses a right join on the
MoviesTable so that yougetresults for ID=3.
Click to expand...
Paste this in using SQL View inQueryDesigner.
Click to expand...
SELECT
M.ID,
Count(R.Value) AS NumRatings,
Sum(R.Value) AS TotalRatings
FROM
RateAS R
RIGHT JOIN
MoviesTable AS M
ON R.ID = M.ID
GROUP BY M.ID;
Click to expand...
Cheers,
Jason Lepack
Click to expand...
On Feb 21, 3:56 pm, (e-mail address removed) wrote:
Hi
I currently have 2 tables with this information:
MoviesTable:
ID - Autonumber
Title - Text[50]
Rate:
RID - Autonumber
ID - Int
Value - Int
The movie-table are filled with these entries
ID = 1, Title = "Pulp Fiction"
ID = 2, Title = "Batman begins"
ID = 3, Title = "Insomnia"
Therate-table are filled with these entries (note value are a number
between 0 and 10)
RID = 1, ID = 1, Value = 8
RID = 2, ID = 1, Value = 7
RID = 3, ID = 1, Value = 5
RID = 4, ID = 2, Value = 5
I've tried different solutions togetthe sum of a movie using one
query(with a subquery) but I can'tgetit to work. As a result I want
this
ID = 1, Value = 20, Count = 3
ID = 2, Value = 5, Count = 1
ID = 3, Value = 0, Count = 0
Hopefully someone could help me, I'm really stuck on this little
query. I'm currently using Microsoft Access 2003.
Thank you for any help, cheers
Large- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top