Movie Inventory Database: Generate Report for Directors, SQL Query?

[tatihulot]

Hello everyone, I am using Access 2013. A few years ago, I created a many-to-many movie database. I've never been great at database design, and I got a LOT of help from several people here. (Thank you.) In fact, I did not know what a junction table was until I got responses here.

Anyway, I need a little bit more help with the database. I'm attaching a copy of the database to this message. It has three continuous subforms, one for actors, one for directors, and one for genre.



Here's what I want to do: I want to generate a report that lists all the movies by a specific director, or a report that lists all the movies with a specific actor. I'm having trouble figuring out how to do this.

Is the best way to do this by creating a query, and then generating a report based on that query? If so, I haven't figured out how to do that. Is there a SQL sequence that would make this possible?

Movie Database 2017.zip

Thank you very much for any assistance. J. Danniel

[RuralGuy]

Create a query that joins the Movie table with the Actor table and see if you can filter the Movies for a certain Actor.

[ssanfu]

You will also need the junction table.......



I've been playing around with your dB...... made a few changes.

Fixed this and that, added "_PK" and "_FK" suffixes to fields, added record movement buttons..... mostly changed the 3 sub forms.
You were having to enter (or at least it looks like it) a PK number as well as type in data (for example the Genre) - I changed them to combo boxes.
I also change the sub form record sources.


I would recommend deleting the calculated fields in the tables "tblDirectors" and "tblActors".

[tatihulot]

Steve,
Thank you so much for your help. I really like the drop down menus/combo boxes. I have NO idea how to create them, though. I'd prefer not to have a scrolling list of movies, though. I'd prefer to have only the current record that I'm working on visible.

Let's put the redesign of the interface on the back burner for the time being, if that's all right with you, and focus on the reports/queries issue. I know how to do basic queries, but what I want to do now, which involves three different tables (including the junction table), is a bit beyond me.

So, the question I have now is: Now that I've started writing a query with the three tables, where do I go from there? I just need a push in that direction. Thanks again! Jd

[orange]

Write the purpose/idea for the query in plain English. This will help clarify your thoughts and focus.
Since you know the subject matter, you can substitute your table and field names into the description.

If you are working with the query designer and you have identified your relationships, you can pull the appropriate tables into the query window. You can move the required fields from the table(s) into the grid and quickly test and view the results.

There are youtube videos on MS Access queries with multiple tables.
W3schools has tutorials on SQL with examples.

Good luck.

[tatihulot]

I think I achieved my goal. It was simpler than I thought, once I actually sat down in front of my computer and devoted the time to it.

Here's what I did: I successfully created a query that displays the directors, and the directors' corresponding movies. I added sorts to the query so the directors' names and corresponding titles are alphabetized.

I then filtered the query to display only one director, or a list of directors.

Then, I generated a report. It appears to be exactly what I'm looking for.

So, thanks to everyone who replied.

[orange]

The thing about the videos is that there are often more than 1 on the same or similar topic. You can get various viewpoints or "teaching techniques", and you can pause and review.
I recognize that 1-size doesn't fit all. Some presenters are better than others (that's very subjective I know), but it's the combo of viewer and presenter, subject matter, materials.... that makes one better than another. Sometimes it isn't until the 3rd or 4th viewing that the presenter starts to make sense. Anyway, that's another story.

So, describe the first step or two that you want some guidance. Plain English, no jargon just like you would use with a 10 yr old.

[tatihulot]

Now to figure out how to create those combo boxes!

[ssanfu]

Good luck with your project. Have fun....

[tatihulot]

Once again, thank you for your assistance and patience.

I have not yet begun to try to recreate your combo boxes. I might need a nudge in the right direction for them, as well.

But the good news is the queries are working exactly how I want them to, and the reports are to my liking.