[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

structuring tables that might link to each other?

Posted on 2004-04-25
4
Medium Priority
?
208 Views
Last Modified: 2012-06-27
I have a database that includes two tables called, say, A and B.  Each record in table A can have multiple records in table B, but some of these sets of multiple records in table B can apply to more than one record in table A.  i.e. table A has 5 records, numbered A1 to A5.  Table B has 12 records numbered B1 to B12.  B1, B2 and B3, are all linked to A1; B4, B5 and B6 are linked to A2; and B7, B8 and B9 are linked to A3.  But B10, B11 and B12 apply equally to both A4 and A5.  There is no limit to the number of times a set of table B records can link to a single table A record.

In practical terms a user is required to select a table A record [in a form] and then add any number of table B records to it [in a sub-form].  But sometimes they don't want to enter multiple records twice, they just want to select a table A record, say A5, and rather than enter B13, B14 and B15 which are exactly the same as B10, B11 and B12 they just want to say "OK, I've selected A5, now I just want to tell the system that it should use the same table B records as A4".

What is the best way to structure / manage this in MS Access?
0
Comment
Question by:kiwibigdave
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 10913861
I sugeest that if you have'nt done so to read and understand the concept from the following
links.

Database Normalization Basics
http://support.microsoft.com/?kbid=209534

Defining Relationships Between Tables in a Microsoft Access Database
http://support.microsoft.com/?kbid=304467
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10914025
For this you need a so-called "relation table" having the key of tableA and tableB.
The "set copy" can be performed by an INSERT query selecting the rows from say A5 and INSERT then with the key A4.

This will have to be triggered from the form and you need to decide what to do when A4 already has one or morerelation rows.....

Need more info ?

Nic;o)
0
 

Author Comment

by:kiwibigdave
ID: 10914054
Thanks for that capricorn, sometimes just asking the question and being pointed back at the basics makes you realise what you've done wrong.

But to update the topic for the future, immediately I saw the many-to-many topic at "Defining Relationships Between Tables in a Microsoft Access Database
http://support.microsoft.com/?kbid=304467 " I realised I don't have a many-to-many relationship at all, and that I'm actually one table short.

My table B needs to be split into B and C, with a one-to-many relationship from B to C.  Then B and A also need a one-to-many.

Thanks also Nico, if I had asked the right question I could have saved you some time.

Then again, if I'd known the right question I could have saved myself 250 points ;-)

0
 
LVL 54

Expert Comment

by:nico5038
ID: 10914076
Looks to me you realized you needed to created the relation table I proposed :-)

Nic;o)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question