How to make synchronous tables in Microsoft Access?
I am using Microsoft Access 2003 on a WinXP SP2 machine.
I have two tables. One has a list of movies and other has a list of actors. Any movie in a "Movies" table can have multiple actors from "Actors" table. In the same way, any actor entry in "Actors" table can have multiple movies from the "Movies" table. I cannot figure out how to join these two tables. One idea that I came up with is giving an Actors filed to "Movies" table and a Movies field to "Actors" table. Both of these fields would actually be list boxes. However, I cannot figure out how to make these fields synchronous to each other in a way that one update in one filed causes the update of the other field. Any alternate ideas or any ideas to improve my idea?
- 1 decade agoBest Answer
What you are trying to do is create a many-to-many relationship, i.e. many actors to many movies. You need to break this relationship down further, by creating a join (or junction) table between Actors and Movies. You can call this join table "Roles".
First, make sure you have primary keys in Actors and Movies - this should be an autonumber type field in each - I'll call them ActorID and MovieID, for my example.
Next, create a new table called Roles. Put the ActorID field and MovieID field in this table, and make them Number fields. You can also go further and add more fields to describe the role the actor plays in the movie, like character name.
Next, create a relationship from Actors to Roles, using the ActorID field. Then create another relationship from Movies to Roles using the MovieID field.
Now you're set. All you have to do next is create forms to do your data entry. It's a little tricky when you have this three-table situation, but not too bad if you know how to create a query. Let's assume you want to create a form that shows the Movie information at the top, with a list of Actors below. First you'll create the form and base it on Movies table. Then, create a subform, and here's the slightly tricky part, you'll base the subform on a query that includes the Roles and Actors tables. You make the query ahead of time and save it, or you can build the query "on-the-fly" as your Record Source for the form. The link field between the Main part of the form and the Subform is the MovieID field.
I hope I wrote this clear enough. Please post back if you need further explanation.
- luciLv 43 years ago
In table view, you are able to suitable-click on the column and enter a filter out contained in the textual content field that pops up as area of the menu. click contained in the field and use commonly used filter out expressions to filter out the table information. some examples: <4333 ability below 4333. >4000 and < 4500 ability more suitable than 4000 and below 4500. 4123 ability in reality 4113. If the postcode is a textual content field then you definately favor to placed costs around the cost (regardless of the truth that get entry to is reliable at guessing what you advise. as an party: < "4333" is below 4333 in alphanumeric words The variety of information again for each filter out is shown on the bottom of the exhibit (you've were given to scroll to the bottom of the table to make certain the determination if it truly is more suitable than say one hundred information).