Query needs tables switched

[Scott B]

Greetings,

I have two tables, one is list of names and the other is list of
organizations. More than one person can belong to one organization. And
each person can only belong to one organization. I want a query that joins
names to organizations. I want to ultimately create a form to enter names
into organizations for invoice purposes. When I make a query it always
creates it backwards in my mind. I believe this is true because after
making the query I go to Forms and the Form Wizard subform and linked table
functions always use the names as the main form and I want the organization
as the main form. What am I doing wrong? Here is the SQL of the query if
it helps. "tblLUBillToContact" is the names table and "tblLUBillTo" is the
organization table.

Many thanks,
Scott B

SELECT tblLUBillToContact.Contact, tblLUBillTo.Organization,
tblLUBillTo.Department, tblLUBillTo.BillAddress1, tblLUBillTo.BillAddress2,
tblLUBillTo.BillCity, tblLUBillTo.BillState, tblLUBillTo.BillProvince,
tblLUBillTo.BillPostalCode, tblLUBillTo.BillCountry
FROM tblLUBillTo INNER JOIN tblLUBillToContact ON
tblLUBillTo.BillToContactID = tblLUBillToContact.BilltoContactID
ORDER BY tblLUBillTo.Organization;

 

[Sylvain Lafontaine]

First, you should forget about the old convention of using prefixes such a
tbl. This is clearly outdated now. You should also use things like
Organizations for the name of the table, OrganizationId for its ID and
OrganizationName for its name.

Second, you should use alias in your queries.

Third, your problem come from the fact that you are trying to set the
foreign key field on the wrong table. The tblLUBillToContact should have a
field for the ID of the organization and this ID should be used as the
foreign key in your join query. The relation O.BillToContactID = C
..BilltoContactID should be replaced with something like O.BillToID = C
..BilltoID, the BilltoID beeing the ID of the organization. Each Contact
should have this field as its foreign key to the Organizations table:

SELECT C .Contact, O.Organization,
O.Department, O.BillAddress1, O.BillAddress2,
O.BillCity, O.BillState, O.BillProvince,
O.BillPostalCode, O.BillCountry

FROM tblLUBillTo as O INNER JOIN tblLUBillToContact as C
ON O.BillToID = C .BillToID

ORDER BY O.Organization;

S. L.

 

[Scott B]

SL

Thanks for the quick reply. I'm not sure I understand the answer. Here is
the structure of the two tables.
tblLUBillTo:
BilltoID Primary Key
BillToContactID Number
BillOrganization Text
BillDepartment Text
BillAddress1 Text
BillAddress2 Text
BIllState Text
BillProvince Text
BillPostalCode Text
BillCountry Text

tblLuBillToContact:
BilltoContactID Primary Key
Contact Text

How do I get Access to set a foreign key?

Best regards,
Scott B

 

[Sylvain Lafontaine]

You must add the BilltoID field to the tblLuBillToContact table and use it
as the foreign key if you want the tblLUBillTo table to be the *main* table.

If you want the tblLUBillTo table to be the main table, the BillToContactID
field is useless. However, it would be appropriate if you wanted the
tblLuBillToContact table to be the main table. This is way Access is
showing you the tblLuBillToContact as the main table.

S. L.

 

[Scott B]

I'm sorry I am so dense, but I cannot make this work. Maybe I need to start
over again from scratch. Am I off-base in what I am trying to accomplish?
I seems to me that I do not want to store a record for an organization for
every contact in that organization. I thought that was the beauty of a
relational database. So what do I need do to make one record for an
organization and many records for the contacts at that organization and then
retrieve this info in a query? I have no formal training in Access and no
programming experience at all. All I know about using Access is what I have
learned from reading, hard work banging away at it and from these news
groups. Sometimes it seems so obtuse and sometimes it is clear as a bell.
Today it is clear as mud. Maybe a different approach is what is needed. A
new Start?

Many thanks,
Scott B

 

[Sylvain Lafontaine]

You are not a storing a record for an organization for every contact, you
are storing the ID of this associated record in the contact record:

blLUBillTo:
BilltoID Primary Key
BillOrganization Text
BillDepartment Text
BillAddress1 Text
BillAddress2 Text
BIllState Text
BillProvince Text
BillPostalCode Text
BillCountry Text

tblLuBillToContact:
BilltoContactID Primary Key
Contact Text
BilltoID Number


The BillToContactID has been deleted from the first table and the field
BilltoID has been added to the second table. This second field (BilltoID on
the second table) is called a Foreign Key and the etablished relation is
called a 1-N (single-multiple) relation. This design is mandated by your
description: "More than one person can belong to one organization. And each
person can only belong to one organization" .

If you want to have only one single person per organization, then you can
keep your previous design and have a 1-1 relation.

If you want to have multiple person per organization and multiple
organizations per person, then you must etablish an N-N relation by adding a
third table with foreign keys pointing to both tables:

tbl_Contact_And_Organization:

ID_ContactOrg Primary Key
BilltoID Number
BilltoContactID Number

S. L.

 

[Scott B]

The water has cleared and the mud is gone. It works fine. Many thanks.

Scott B