[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to create many to many relationship and report

Posted on 2013-01-10
13
Medium Priority
?
530 Views
Last Modified: 2013-01-12
Hello,
am attaching sample database-
am trying to understand how to make a many to many relationship with two tables.

have created the 2 tables-[tblItems] and [tblParts] and a 3rd table to make the junction.
but that is as far as I can get?

can a report be created with items with the same category number be listed and then right below have all the part numbers and the same category numbers below them?

Item descr qty      category  category number
Printer    51      HP          101
Scanner    65      HP          101
Monitor    84      HP          101


Printhead  15       HP         101
fuser      26       HP         101
etc....

thank you--
ManyToMany.accdb
0
Comment
Question by:davetough
[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
  • 8
  • 2
  • 2
  • +1
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38763116
0
 
LVL 18

Expert Comment

by:xtermie
ID: 38763156
You need an intermediate table just like you have done.
The two tables are joined 1 to many with the intermediate table so they are joined as many to many between them.  Uf you create the joins/relationships correctly, (same data type and size of the joined fields) access will then present the linked fields in the Datasheet view with the + sign as you describe.

For reports to be grouped, since you are using access, I would advise that you use the grouping levels available in the report wizard that will create several layers of grouping that can fit your needs (and sorting).

If you want to view in the way you mention, you can create a form (main table), subform (from a query or the other table) to view in a more organized manner.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38763262
First, I see no reason why you need two tables.
I really can't see any real difference between a Part and an Item...?

You can simply add a "Type" field to one table (Item or Part)
ItemID
ItemDesc
Quant
Category
Type (Item or Part)
Category Number

Then what you are asking is ridiculously simple without the need for a Junction table
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 18

Expert Comment

by:xtermie
ID: 38763295
I believe that the example is simple, but for sure if there are many parts for an item,  davetough is designing this right.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 38763316
This sample does exactly as you request without a junction table...
screenDatabase24.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38763346
Now, my feeling is that you could tweak the report to actually display the item "Type" in the group Header (and not have it repeat for every record (which is redundant), and possibly add summaries, ..etc, ...but again, ...my sample displays the data in the same way you specified.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38763362
You can simply combine the tables by copying and pasting one table on top of the other in Excel (without the key fields)
Then add in your "Type" column and fill in the corresponding values
Then import this back into Access
Then simply add in an Autonumber field as the key...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38763420
xtermie,

It can be designed in anyway the OP wants.
;-)
As you know, with many things in the database design world, there is no clear "right or wrong" design that will work for every situation.

My feeling was that I saw no real reason to create the junction table.
The fields in both tables are nearly identical, so it is not like the classic Many To Many relationship:
tblEmployees
tblProjects
tblEmpProjects (Linking table)
...In this classic Many to Many, the Employee table has no fields in common with the Projects table.

In the OP's case All of the fields are the same,
The only difference is the name of the PK field

This just sends out signals that perhaps this should be Normalized to bring all the items into one table.

Also, the current design will force the OP to create another new table for each, and any, new "Type"
So if there was a Parts table, and an Items table and a new "Modules" table, how would these three tables now be joined with a Junction table?

So again, I saw no real need for the Junction table and all of the associated complexity...

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38763636
...and I don't see any situations in the data where one "item" is associated with many "parts"
...or vice versa...
(A situation that Junction tables are created to deal with)

So again, I am not seeing a need for a Many to many table, but I am seeing reasons to combine the data into one table...

JeffCoachman
0
 

Author Closing Comment

by:davetough
ID: 38763854
Amazing how you made it that easy-thank you- so is many to many more for when you have different fields? thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38765894
Having different fields can be one attribute of a many to many, but it is not a definition of a many to many.

A "Typical" many to many is when you have two tables and either on can have many of the other.
Examples:
Students/Classes
One student can be in many classes, and also, One class can have many students

Employees/Projects
One Employee can be in many Projects, and also, One Project can have many Employees

Doctor/Patients
...etc
These are examples of the most basic Many to Many.
They can become more complex if certain combinations can, or cannot, repeat.
If you research this on your own, you will encounter many names for this type of table:
Junction table
Association table
Intersection table
Many-To-Many table
Linking table
Mapping Table
Marry Table
;-)


In any event, ...
This did not seem to be the case in your design.

This is why in some cases you should not "tell" experts how to solve an issue.
   "I must create a many to many table"
...Because this forces us to think along those lines, possibly ignoring a better approach.

In most cases you only need to specify your ultimate need, and let experts suggest a design.

So instead, perhaps you should have just asked the same question, ...only leaving out the Many To Many "requirement"
For example:
Here is what I have...
Here is what I want...
What is the best design approach?

Make sense?

;-)

JeffCoachman
0
 

Author Comment

by:davetough
ID: 38766560
yes- thanks for the help
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38770371
;-)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

656 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