shae asked in Computers & InternetSoftware · 1 decade ago

Can you create a relationship in Access (2003) from one table to many different tables?

OK. I am new to Access. I took Microsoft Office Suite Beginner-Advanced in college but since then I have hardly used Access. It's the kind of "use it or lose it" situation. I've lost it. I've also lost my text book from college as well.

Basically, my database is our member's information.

My other tables are the member's inventory.

The way I have set it up is each table is a different kind of inventory (they are very large). Example: Computer inventory, cell phone inventory, car inventory, etc...

What I want to do is create a relationship using their member #... so when you open the member information database, you can click the + sign and see all of that member's inventory.

Is there a way to do this and did that make any sense at all? I've been staring at this screen for hours and it is driving me nuts.

I know that there has to be a computer "guru" out there who knows exactly what I am talking about!

Thanks so much for all of your help!!!!

Update:

I have a primary key in each table..... what is a master key?

5 Answers

Relevance
  • Anonymous
    1 decade ago
    Favorite Answer

    Microsoft Access is a relational database. So, all the tables have to be linked, and this reduces redundant (repetitive data). You can use a primary key (same as master key) to link information. You can set up your first table with member information - having a unique id (the member number), and then you can have a separate tables for computer inventory (with a unique number for primary key), cell phone inventory (with a unique number for primary key) and car inventory (with a unique number for primary key), and then you need a table to show relationship between member and the inventory. THat is, you need to show the member # (which would be linked from the member table), and the items they have (just the id numbers) - these id numbers would be your link to the three inventory tables. Hope that makes sense

    • Login to reply the answers
  • 1 decade ago

    Can you create a relationship in Access (2003) from one table to many different tables?

    Yes!! You can create as many related tables as required (one for each inventory).

    First of all, how many records (approx) are you talking about to justify the individual inventory tables you mention? By setting the right indexes (primary and secondary keys), you can speed up queries for very large volume of data and could all be stored in one table.

    Take a look at http://www.about-access-databases.com/primary-key....

    Take a look at http://www.about-access-databases.com/access-relat... to learn about Access relational database and http://www.about-access-databases.com/access-relat... to learn how to implement them.

    In essence, your member table has a primary key (ID) which is linked to the same ID for each inventory table which can be set (recommended) as the secondary key so it can store duplicate values acting as the 'many-side' in a 'one-to-many' relationship. Additionally, you have a unique primary key in each inventory table (which will act as an audit trail reference) so that for future consolidation of tables this may help to provide an overall reference which some may refer to as the 'master' key!

    Hope this clarifies things.

    Ben Beitler

    BTW: skv77 is spot on and gets my vote!

    • Login to reply the answers
  • 3 years ago

    My first question is why did not you're making one table and use worker call as a container interior the table? this may circumvent allot of time at the same time with saving time at the same time as it is composed of writing queries and many repetition. also as your database grows it is going to circumvent a large number of time attempting to debug any subject matters. Any how in case you pass to varieties and use the kind wizard. the kind wizard will walk you by the thanks to create a kind and from the kind you are able to enter files into the table with out having to open it.

    • Login to reply the answers
  • 1 decade ago

    All you need to do is create a master key (member#?) and use that key in all the other tables

    • Login to reply the answers
  • How do you think about the answers? You can sign in to vote the answer.
  • 1 decade ago

    no id think so sorry

    • Login to reply the answers
Still have questions? Get your answers by asking now.