MS Access 2007 one to many relationship problem?

Question

How to create a "one to many" relationship between one primary key in one table and 2 or more foreign keys in another table?

Its hard to explain so I created this video, please watch it
thank you in advance

its a youtube video : http://www.youtube.com/watch?v=y0WX1vdgLGo

Answer 1

I don't think you will be able to do exactly this using table relationships due to the structure of the tables.



But you can display on a Customer form in a list box each package that is associated with a particular customer.



Using one 'Make table' query and one 'Append' query, create a new table holding the package numbers and the associated customer ID. Then create a third query to select only unique values, and use that query to populate the list box on the Customer form.



This is the structure of the temporary table tblPackageTest:

Package

Customer



This is the SQL for the FIRST query (it gets all 'SentBy' values into a field called 'Customer'):

SELECT tblPackage.PackageID, tblPackage.SentBy AS Customer INTO tblPackageTest

FROM tblPackage;



This is the SQL for the SECOND query (it gets all 'SentTo' values into that same field 'Customer'):

INSERT INTO tblPackageTest ( PackageID, Customer )

SELECT tblPackage.PackageID, tblPackage.ReceivedBy AS Customer

FROM tblPackage;



Create a THIRD query to select only unique values from tblPackageTest:

SELECT DISTINCT tblPackageTest.PackageID, tblPackageTest.Customer

FROM tblPackageTest;

----------------------------



Now, create a customer form (with the CustomerID, etc...) and place a list box on it to display the associated packages.



The 'Row Source' of the list box is going to be that THIRD query, with a reference to the current customer number as the criteria for 'Customer' - it looks something like this: [forms]![frmCustomer].[txtCustomerID]



This is the SQL for that 'Row Source':

SELECT qry3.PackageID, qry3.Customer

FROM qry3

WHERE

(((qry3.Customer)=[forms]![frmCustomer].

[txtCustomerID]));



You can execute qry1 and qry2 either in the form's OnOpen event or each time you move to a new record in the form's OnCurrent event, depending on whether or not other users might be updating the package information while you're viewing the Customer form.



You must also requery the list box on the form each time you move to a new record by putting this line in the form's OnCurrent event: Me.lstPackage.Requery

-------------------------------



I know this seems crazy-complicated but it does work to display the package numbers associated with a customer, whether or not it was 'SentBy' or 'ReceivedBy.'

Answer 2

Why would you have more than one foreign key in one table refer to ONLY one primary key in another table???

Answer 3

problematic stuff. seek at bing and yahoo. that may help!