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

[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

 

[Jason Lepack]

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

 

[largie]

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

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

 

[Jason Lepack]

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.

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

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 -

- Show quoted text -