Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Newby can't link tables
Hi I want to link two tables i.e.

I have a contact table and a show table. I want to be able to show who i have sent an invite to by show and in reverse under the contacts show which show they have been invited to.

I can link the two tables but when i do all of the contacts show under all of the shows and vis versa

Thanks

Tina
ID
1138
Category
Customization
Author

tina newsome
Date Created
10/24/2017 1:47:37 AM
Date Updated
10/31/2017 1:14:22 PM
Comments
cooper collier  10/24/2017 1:38:53 PM
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


tina newsome 10/24/2017 2:53:26 PM
Thankyou very much for that. I started this database as i am sure most newbie uneducated in databases do with importing a spreadsheet. I have had to learn and my first thing was to seperate it our into tables. I have relied on the guys at teamdesk to help me but am wanting to know more as everytime i go to do something it is helpful to look through where they have helped.

My husband is IT but I am determined to work this out on my own or with the help of others ha ha.

I am very proud of what i have achieved thus far and it is growing all the time.

I would be very interested in your video tutorials. I am sure once the penny drops it will be ingrained in my brain forever.

My predecessor at my work did it all in excel so this database is wonderful compared to that, and thankyou for not saying you should know that. We all have to start somewhere and to be honest i find it very relaxing doing this. It is like a puzzle i need to solve

Thanks

Tina
cooper collier  10/24/2017 4:19:39 PM
We already have many beginner videos. They are all on youtube. You can find them listed here
https://www.teamdesk.net/videos

Honestly the ones we have so far are very beginner...ie.. "BORING!"
We are just now getting to the more exciting topics, (if anything related to database design could be called exciting)...

Teamdesk does a great job of helping convert from spread sheets to database, but it is always challenging and requires quite a bit of front end work, but it is worth it in the end.

If you are not in a huge hurry, maybe one day next week, I can find time to take a look at what you have and let you know if your heading off a cliff or not.







tina newsome 10/24/2017 4:38:57 PM
Hiya

Yes that would be great. For reasons that will become clear i would need you to sign an NDA though if that is ok

Thanks

Tina
cooper collier  10/25/2017 2:34:07 PM
I am sure I will not have a problem with NDA.
just email me directly next week.

cooper@frobbit.com
or
cooper@waterswebworks.com
cooper collier  10/31/2017 1:14:22 PM
i am back in my office and pretty much caught up from my trip.
you can email me the papers anytime

cooper@frobbit.com
Feedback
Back to Search Results