Relations between tables (links) are one of the more confusing parts of a database. At the same time, they are essential. Without the ability to relate tables, there is no point to having a database. Since the topic is so essential and so confusing it is going to be very difficult to adequately assist/advise you from the information in your question... so this answer is based on many assumptions.
I absolutely hate it when someone answers a person’s question with “You should not do that!”. So, I am going to answer your question “as is”
If there is a column that “matches” in your contact and show table. ie [name]. You can make an entry in the “MATCH CONDITION” section of the relation. Contact[name]=Show[name]. Then the results will be more like you expect.
Because you mention you are a “newbie” I am going point out that the way you have done this can be very problematic. Especially in the future.
I cannot teach you everything there is to know about table relations in this short response, but I can try for a summary.
There are essentially 2 types of relations between tables. I call them “soft’ and “hard”. What you have done is created a “soft” relation.
With a soft relation there are 2 tables that are completely separate. In your case contact and show. When you make a record in contact you fill in all the columns, [name][address][zip] when you make a record in Show you also fill in each column [location][topic][guest]. At some point you wish to know which customers were included as guest in each show. So, you can make a “soft” link then “Filter match” on Contact[name]=Show[Guest]
Now you have a “soft” list. Soft because the bigger your database gets the more likely your data will be inaccurate. You are relying on the [guest] and [name] to be spelled correctly every single time. Nobody, changing names, and nobody having the same name as someone else. None of these things will always happen, so as each day passes your database gets more and more inaccurate.
Soft relations are useful in some select circumstances, but you need to know what you’re doing and why.
As a newbie.. you likely do not want a soft relation.
A hard relation is when you have two tables and you place the INDEX(ID) of one table record into the other. An easy example is making invoices for customers.
Customers table [customer-id][name][address]
Invoice table [invoice-id][product][total amount] and to link them [customer-id]
In this simple example we have what is called a one-to-many relation. Each invoice must have an existing customer ID included. Using this customer ID, we now know the customer name addresses for the invoice.
Hard links are the preferred way to link tables because they eliminate issues with spelling, common names, changes. eliminate duplicate entry, etc.
NOW I am going to go way out on a limb, based on what you asked and the way you asked. It is likely that you need a HARD Relation of the Many-to-Many type. Which is even more complicated, and I cannot teach that in a post response.
One thing you must know. “converting” a soft relation to a hard relation is very time consuming and expensive. We have done this for clients more times than I know. By time the inherent issues of a soft relation become a problem there are thousands and thousands of records in the database. Much of the work is grunt manually editing individual records. It is always better to take the time to get it right during initial development.
GOOD NEWS. We happen to be working on video tutorials for relations.
Bad News. We will not have finished tutorial for a couple of weeks at the earliest.
I hope this helps.
Cooper Collier
WatersWebWorks.
https://www.teamdesk.net/experts?id=21
