Create a query to merge records with two fields as criteria

[chilidog]

I've got 2 tables, one with company locations/addresses and another with
contacts for the company. Table 1 has an ID field for the company name and
and ID field for the location (for that specific company). Table 2 has each
contact's name with the ID fields for company name and location. For
instance, Ajax Co has ID of 1; it's NY location has ID of 3. Joe Smith works
for Ajax in NY. How do i create the query to pull on ID field first and then
on the location field?

 

[John Spencer]

If I understand you correctly you would join on two fields.

SELECT *
FROM CompanyLocationsTable INNER JOIN ContactTable
ON CompanyLocationsTable .CompanyID = ContactTable.CompanyID
AND CompanyLocationsTable .LocationID = ContactTable.LocationID

In query design view
== add both tables
== Drag from companyid to companyid (set up first part of relation)
== Drag from locationID to locationID (set up second part of relation)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KenSheridan via AccessMonster.com]

This is one of those situations which might not be as straightforward as
appears at first sight. It all depends on what is meant by 'location'.
There are two possible scenarios:

1. Location means exactly that, a place where there might be one or more
companies, e.g. with your example as well as Ajax Co having a location in NY
with a value of 3, Acme Co also has a location in NY with a value of 3. In
this case you current setup would be correct as Table 1 is modelling a many-
to-many relationship between companies and locations, so the model is:

Companies---<CompanyLocations>----Locations

The primary key of CompanyLocations is a composite one of CompanyID and
LocationID, so Contacts has a composite foreign key of the same two columns
in the way John described:

CompanyLocations>===Contacts

2. In the second scenario each location is an address specific to one
company, so there is a simple one-to-many relationship:

Companies----<Locations

Consequently Locations has a primary key LocationID and a foreign key
CompanyID referencing the primary key of Companies. Contacts needs only a
LocationID as there is a simple one-to-many relationship from Locations to
Contacts, so adding contacts to the model:

Companies----<Locations----<Contacts

One thing to be considered in this scenario is a factor common to many linear
relationships like this, that there could be a 'missing link' in the chain if
there are contacts for the company per se, but not in relation to any
specific location. The way this is handled is to include a row in Locations
for each company with a value such as N/A. So if there are 10 companies in
the database, there would be 10 N/A rows in Locations, each with a different
CompanyID value. Contacts not related to any specific location would
therefore have a LocationID value pointing to the N/A row for the company in
question. No CompanyID column is needed in Contacts as each row in Contacts
maps to a row in Companies via the relationships. If there were also a
CompanyID column in Contacts we would be told redundantly for each contact of
Ajax Co in NY that Ajax Co has a location in NY. This is not just wasteful,
but more importantly leaves the table open to the risk of inconsistent data.

Whichever scenario applies a query would mirror the relationships, with that
for scenario 1 being set up as John described, that for scenario 2 being a
simple join of Companies to Locations and Locations to Contacts. It is
important, however, that the relationships be created and referential
integrity enforced as this protects the integrity of the database. In fact
once the relationships have been created, when the tables are added to a
query the joins will be made automatically.

Ken Sheridan
Stafford, England