Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query base on Many-to-Many relationship

Posted on 2013-05-10
4
Medium Priority
?
412 Views
Last Modified: 2013-06-21
Customer Table: tblCustID(AutoN); tblCustFName(Text); tblCustLName(Text); tblCustLocal(Number)

frmCustomerInfo:  tblCustID; tblCustFName; tblCustLName; these are text boxes
tblCusLocal(ComboBox) Properties: SELECT tblLocal.tblLocalID, tblLocal.tblLocalName, tblOrganization.tblOrganizationName FROM tblLocal INNER JOIN tblOrganization ON tblLocal.tblLocalID=tblOrganization.[tblOrganizationLocal];

frmOrders:  tblOrdersDate(Date) TextBox; tblOrdersSeason(text) ComboBox; tblOrdersID(Auto)TextBox; tblOrdersCustID(text)ComboBox... There is a subForm that contain products purchased.

Here's my problem - when I run the query do display the orders sorted by customer and organization I get duplicate records for each customer cause each location has multiple organizations and to get from the tblcustomer to the tblorganization, I have to go through the tbllocal
(one-to-many betwn tblcustomer and tblorganization where tbllocal is the one in both relationships).  
The results give me locals plus all the organization that that local has associated with it for each customer, but if you examine the above info in up top, U can C that, when I enter the customer info in the frmCustomer, I pick a specific local/organization combination.  I know I'm leaving out some info, but I figure this is a good way to start.
0
Comment
Question by:JZeig
[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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39157252
the many-to-many relationship should be avoided.
to do this you need a third table ( i think from what you describe, it is the local table )
anyway, see this demo

http://office.microsoft.com/en-us/access-help/demo-set-the-relationship-between-two-tables-HA010254901.aspx?pid=CH100739911033
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39159311
List few records of the table, and show the required output.

Here some info, hope to be helpful.

M-M relation means you have left table(L_ID, ...) and right table(R_ID,...)
the M-M will be a link table between the two (L_ID, R_ID, ....)

Having only the M-M table, you may create 2 queries to represent the left and right tables.

For Left table: LQ (Select Distinct L_ID, ... FROM [M-M] )
For tight table: RQ (Select Distinct R_ID, ... FROM [M-M])

Now you have three tables, LQ, RQ, and [M-M].
0
 

Author Comment

by:JZeig
ID: 39201184
Here are the relationships - this might help

tblOrders: tblOrdersCustID------------------ tblCustomer: tblCustID

                                                    within same table "tblCustomer"

                                                              tblCustomer: tblCustLocal -----------------------------------------
------------------------------tblLocal: tblLocalID

                                                     within same table "tblLocal"
                              tblLocal: tblLocalID---------------------tblOrganization: tblOrganizationLocal

U have query up top.  The desired outcome is only the orders for a specific organization.  If a specific "Local" has multiple "Organization"s, I get duplicates.  I think I need to link the organization first then that way the organization will default to the correct local.  "Local"s have multiple organizations, but the organization is restricted to a specific local.  which I have  setup but I think this backwards.
0
 
LVL 31

Accepted Solution

by:
hnasr earned 1500 total points
ID: 39202863
Try to reproduce the issue using a sample database, upload. Use few records.
List the required output and tell which tables the output fields are selected.
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 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