Need help creating a query to find film reviews by name, without changing structure.

[zac18992]

I have a database in Access with four tables: Member, Film, Payment and Review. The idea is a film club where members are stored in the member table, payments in the payment etc. The original data had each film review assigned to a certain person.

The tables and relationships are as follows, with primary keys in bold and relationships described between respective tables of the relationship:

tblMember: Username, First name, Surname, Password, DOB
Username is foreign key in tblPayment from primary key in tblMember (one member to many payments)
tblPayment: Payment ID, Amount, Film ID, Username
Film ID is foreign key in tblPayment from primary key in tblFilm (one film to many payments)
tblFilm: Film ID, Title, Genre, Month shown
Film ID is foreign key in tblReview from primary key in tblFilm (one film to many reviews)
tblReview: Review ID, Film ID, Comments, Marks out of 10


My aim is to make a query that gets reviews (comments and marks out of 10) including film ID, title, month shown, first name and last name. The review table has 19 fields from the data I have imported, in which all reviews were by a unique member. However, when I make the query described above, I get tons of duplicates and around 50 fields in the query result

Is there any way that I can get the unique reviews via query, without changing my current database structure. If I were to change the structure, what would be the best way?

Thanks!

 

[Beetle]

tblReview: Review ID, Film ID, Comments, Marks out of 10

Seems like you would want Username as a FK field in this table. Don't you need to know which user reviewed the Film?

The review table has 19 fields from the data I have imported, in which all reviews were by a unique member. However, when I make the query described above, I get tons of duplicates and around 50 fields in the query result.

I assume you mean that the original record set has 19 rows (not fields) and that the query is returning 50 rows. What is the SQL of the query you attempted?

 

[zac18992]

Seems like you would want Username as a FK field in this table. Don't you need to know which user reviewed the Film?



I assume you mean that the original record set has 19 rows (not fields) and that the query is returning 50 rows. What is the SQL of the query you attempted?

Yes I meant rows! I apologise, I'm not exactly knowledgeable with databases

I have thought about having username in the review table also, and linking this to the member table in relationships. However, this creates a 'loop' and from what I gather that is not appropriate?

 

[zac18992]

The SQL of the query is as following:

SELECT tblFilm.[Film ID], tblFilm.Title, tblFilm.[Month shown], tblMember.Firstname, tblMember.Surname, tblReview.Comments, tblReview.[Marks out of 10]
FROM tblMember INNER JOIN ((tblFilm INNER JOIN tblPayment ON tblFilm.[Film ID] = tblPayment.[Film ID]) INNER JOIN tblReview ON tblFilm.[Film ID] = tblReview.[Film ID]) ON tblMember.Username = tblPayment.Username;

 

[Beetle]

I have thought about having username in the review table also, and linking this to the member table in relationships. However, this creates a 'loop' and from what I gather that is not appropriate?

I'm not sure why you think this creates some sort of unacceptable "loop". You're just creating an additional relationship between members an reviews.

SELECT tblFilm.[Film ID], tblFilm.Title, tblFilm.[Month shown], tblMember.Firstname, tblMember.Surname, tblReview.Comments, tblReview.[Marks out of 10]
FROM tblMember INNER JOIN ((tblFilm INNER JOIN tblPayment ON tblFilm.[Film ID] = tblPayment.[Film ID]) INNER JOIN tblReview ON tblFilm.[Film ID] = tblReview.[Film ID]) ON tblMember.Username = tblPayment.Username;

The reason you're getting too many rows is likely because you are including tblPayments in the query (presumably you did this as a roundabout way of getting to the member name). tblPayments doesn't belong in the query if you are just wanting to get the reviews (payments and reviews are unrelated). If you create a relationship between members and reviews, then all you should need is a query like;

SELECT tblFilm.[Film ID], tblFilm.Title, tblFilm.[Month Shown], tblMember.FirstName, tblMember.SurName, tblReview.Comments, tblReview.[Marks Out Of 10]
FROM tblMember INNER JOIN (tblFilm INNER JOIN tblReview ON tblFilm.[Film ID] = tblReview.[Film ID]) ON tblMember.Username = tblReview.Username

 

[zac18992]

It was just that the relationship with member and review tables create a circlular relationship diagram and I didn't think I was supposed to do that.

So are you suggesting to have username in the table and create a relationship from the beginning and having a circle of relationships? If there is nothing wrong with the circle then I've learnt something new thanks.

 

[Beetle]

In technical terms, yes, I suppose this does create a circular reference. Whether that will cause you any problems depends somewhat on other factors like the data flow, data entry process, etc. In this case I don't really see it being a major issue, and it's probably the simplest way to correct your existing structure so that you can get the data output that you need (i.e. it's less problematic than the way you have it structured now). You need to know which user did the review and trying to get at it through tblPayments isn't going to work.

Having said that, if you do want to consider a more properly normalized structure you could simplify things a bit and avoid any circular references. From what I can tell, the members get paid for each review. If that is the case, then it prompts the following questions;

1) Is tblPayments even necessary? It would seem that each review would only have one related payment, and since the payment amount appears to be the only unique attribute you are storing in tblPayments, why not just put the amount in tblReviews and get rid of tblPayments.

2) Even if you need to keep tblPayments for some other reason, it seems that it should be related to tblReviews, not tblFilm. This is assuming of course that you are paying them for each review (not for each film).

If my assumption about what you're tracking is correct, then I would propose a table structure like the following;

tblMembers
*********
Username
FirstName
SurName
Password
DOB

tblReviews
********
ReviewID
Username (FK to tblMembers)
FilmID (FK to tblFilms)
ReviewDate
PaymentAmount
Comments
Marks

tblFilm
*****
FilmID
GenreID (FK to tblGenre)
Title
MonthShown

tblGenre
******
GenreID
GenreName

The above structure would be more normalized, would track all the same info that you appear to be tracking now, would have no circular references and with the addition of tblGenre would make it easier for you to categorize data by Genre if you wanted to (as well as minimizing potential data entry mistakes with the Genre).

Food for thought.

 

[zac18992]

Thanks for your help

I think I may not have explained well initially, it is members paying for films but my database only deals with 1 payment for each person at the moment and every person has a payment (different payment amounts) as effectively they would pay in installments in the full database. So members pay for films and then they can write a review for a film!

 

[Beetle]

So each time a member wants to review a film they pay a fee?

And by installments you mean they are making multiple payments over time because thay are reviewing multiple films over time? Or do you mean that they are paying one fee in multiple installments?

Or is it more like they pay a monthly fee and then they can review a certain number of films?

 

[zac18992]

The system is designed for a film club, members can pay to watch films. Reviews are simply written so people can express feedback and other members can view film reviews on the system before they go and pay for a film and watch it. Members only have to pay to watch films. Films are all £5 but members will be able to pay this in installments. However, we are taking disregard to this at the moment and the database just has a single value in the payment amount for each payment ID, so basically we are only working with 'initial installments' of differing values. Then I want to be able to have a query that locates reviews by member (so in theory a member could look at reviews, based on what members wrote them).

 

[Beetle]

In that case, maybe something like;

tblMembers
*********
Username
FirstName
SurName
Password
DOB

tblFilm
*****
FilmID
GenreID (FK to tblGenre)
Title
MonthShown

tblGenre
******
GenreID
GenreName

tblMemberFilms (would track which members watched which films)
***********
MemberFilmID
UserName (FK to tblMembers)
FilmID (FK to tblFilms)
ScreeningDate << or other fields unique to this relationship

tblReviews
********
ReviewID
MemberFilmID (FK to tblMemberFilms) << this relationship would give you Film/Member
ReviewDate
Comments
Marks

tblPayments
*********
PaymentID
MemberFilmID (FK to tblMemberFilms) << same as above
PaymentDate
Amount

 

[zac18992]

Okay, that looks good for improvement

For now I must keep the same structure so will try out your suggestions soon!

With the current four table solution, I think I see why you were not worried about a circle. Because of the 'direction' of the one to many relationships, it is not really a 'circle' that will affect the current function?

There is effectively two 'lines' of relationships:

Member-Payment-Review
Member-Film-Review

And the two lines only effect the tables in that line, it's hard for me to describe it hah

So I think I see why the relationships will not cause problems if I use username in the review table unless I am now just confusing myself Thanks a lot for your help, I think I am all set