Problem with Defining Relationship and then an appropriate Query

[Mike Webb]

Hello all,

I am using Access 2002 and do not know VB or SQL.

I started with 2 tables: Contacts (i.e., name, address, etc.) and
Organizations (those the contacts are sorted into for (we hope) retrieval by
common groups). I have ~2K contacts and ~90 organizations.

I then created a table I called Join matching the ID # of the Contact with
the ID # or #'s of
the Organizations they are assigned to. It is the only place in the
database where this association is made.

I want to figure a way to create a 1:M relationship among them so I can then
create a form to display
each contact and search Contacts based on the Organization.

I've spent the better part of 2 days exploring the Northwinds sample DB,
went through 4 sample DB's
I found at various Access websites, and read up everything I could find on
the internet. I've tried creating
forms and queries with and without the built-in wizards, and also tried
"findrecord2k.mdb" from
www.mvps.org/access to see if I could use it to build a query - but I got
lost. But I can't seem to
figure this out. I'm thinking I may just combine the table into one and use
Yes/No boxes for the
organization belonged to. My hesitation is that that would make an add'l 89
columns. Seems unwieldy
to me.

There's gotta be a better way.

Can someone give me a "push" in the right direction?

TIA,
Mike

 

[Allan Bach]

If I understand you correctly, you have a database where one organization can have many contacts. If so, you're correct in the relationship of the two tables. What I usually do is have a primary key (Autonumber) named OrganizationID, then a field named Organization (Text Data Type). In the Contacts table, I create the foreign key (OrganizationID, Number, Long Integer Data Type)
On a form, I can use a Main/Sub form design and use the Form wizard to create it. I'll pull the Organization fields, then the Contact fields for the form. Access will create a main form with the Organization, and a sub form with a continuous form to enter the contacts
If you want to create a form to enter a contact, then choose the Organization, just use the fields from the Contact table and create a form. After your form is created, find the OrganizationID field and delete it. Then, using the Toolbox, click the combo box icon, click the OrganizationID field (in the field list) and drop it into the form, exactly where the original OrganizationID field was before you deleted it. A wizard will start and allow you to use the Organization table as a lookup for this new OrganizationID field. It will appear as a combo box, allowing you to choose the orgnaization for each contact.
A simple query can then be created to show the Organization/contact report

Hope this helps
Alla

 

[Rolls]

You have a tblOrganizations and a tblPeople with a many-to-many relationship
between then. To resolve the ambiguity you need tblOrganizations-People
which contains the unique combinations. The resolver table is maintained
via a subform with two comboboxes (use the wizard) that select
[OrganizationID] and [PersonID] using a RowSource set to the respective
tables. The resolver table (your contact list) contains three fields: 1)
Autonumber - PK - [SomeNameID], 2) LongInteger - FK - OrganizationID, 3)
LongInteger - FK - PersonID. On the subform name the left combobox (item 2
control) cboOrganization, set Properties: columns to 2, columnwidth to
"0";"1" (the first width = 0, the second whatever is needed so the dropdown
box is wide enough. You have a Yes/No choice whether to display headings.
Control column = 1, not 0. You might want to change the font, size or weight
of the text displayed for cboOrganization. Repeat for Item 3 in cboPerson.
The subform should be a "continuous" form to show all people (child)
contacts related to the organization (parent). Now, as you change the
OrganizationID on the main form, all the contacts on the subform will be
displayed and should change each time the organization changes.

One screen consisting of frmMain and sfrmContacts should be enough for this
DB. You can add, change, or delete Organizations and Contacts from the same
screen if you add buttons to display the respective tables on pop-up forms
that return to frmMain upon exit. This will handle the possibility that one
inique person might be a valid contact for two or more organizations.

Almost every database has a "people table". Once you design one you can use
the same structure in another DB. You might want to consider this common
structure for the fields in the people table:

1) PersonID
2) Title
3) FName
4) MName
5) LName
6) Suffix

A query that is used as a rowsource for cboPerson can then sort names
alphabetically by LName. You would create an expression to build FullName
to be displayed in the combobox by concatenating the fields FullName:
[tblPerson]![Title] & " " & [tblPerson]![FName] ... etc. Nested within this
expression you fan further refine the result to check for blank (Null)
fields with an IIF statement. This will display a full name correctly with
one space between each field 2 through 5 that is present.

 

[Mike Webb]

You have a tblOrganizations and a tblPeople with a many-to-many relationship
between then. To resolve the ambiguity you need tblOrganizations-People
which contains the unique combinations. The resolver table is maintained
via a subform with two comboboxes (use the wizard) that select
[OrganizationID] and [PersonID] using a RowSource set to the respective
tables. The resolver table (your contact list) contains three fields: 1)
Autonumber - PK - [SomeNameID], 2) LongInteger - FK - OrganizationID, 3)
LongInteger - FK - PersonID. On the subform name the left combobox (item 2
control) cboOrganization, set Properties: columns to 2, columnwidth to
"0";"1" (the first width = 0, the second whatever is needed so the dropdown
box is wide enough. You have a Yes/No choice whether to display headings.
Control column = 1, not 0. You might want to change the font, size or weight
of the text displayed for cboOrganization. Repeat for Item 3 in cboPerson.
The subform should be a "continuous" form to show all people (child)
contacts related to the organization (parent). Now, as you change the
OrganizationID on the main form, all the contacts on the subform will be
displayed and should change each time the organization changes.

One screen consisting of frmMain and sfrmContacts should be enough for this
DB. You can add, change, or delete Organizations and Contacts from the same
screen if you add buttons to display the respective tables on pop-up forms
that return to frmMain upon exit. This will handle the possibility that one
inique person might be a valid contact for two or more organizations.

Almost every database has a "people table". Once you design one you can use
the same structure in another DB. You might want to consider this common
structure for the fields in the people table:

1) PersonID
2) Title
3) FName
4) MName
5) LName
6) Suffix

A query that is used as a rowsource for cboPerson can then sort names
alphabetically by LName. You would create an expression to build FullName
to be displayed in the combobox by concatenating the fields FullName:
[tblPerson]![Title] & " " & [tblPerson]![FName] ... etc. Nested within this
expression you fan further refine the result to check for blank (Null)
fields with an IIF statement. This will display a full name correctly with
one space between each field 2 through 5 that is present.