Multiple Joins

[Geoff]

I wish to make-table-query between two current tables. However, i want
to take select data as such:

Table1
city
state
population

table2
city
state
population

I want to create a Table3 (or update Table1) such that the population
is taken from Table2 based on BOTH the city and state matching between
Table1 and Table2.

Please help me understand how to get Access to force a match on two
joins versus just one.

 

[tina]

if, like me, you're not great a writing SQL, then use the query design
window. drag-n-drop the city field from Table1 to Table2, and do the same
with the state field. you automatically get two inner joins.

hth

 

[Geoff]

Thanks Tina. I did that and it created more record than are in either
table. What Access does at that point is creates a record for every
match in City and in State. This creates multiple fields with the same
data.

 

[tina]

please post your SQL.

 

[Geoff]

Thanks Tina. I have no idea how to post SQL. I simply created two
joins between city/city and state/state. The make-table-query then
takes all matches between city/city and creates records, then does the
same with state/state. that means there are duplicate records.

what i want it to do is make sure both the state and city match then
make one record. I've tried inner and outer joins. No dice.

 

[tina]

i understand what you're trying to do. and i understand what result you say
you're getting - but i don't understand why you're getting it. so i want to
see the SQL to try to determine what else is going on that's affecting the
outcome.

to post the SQL, open your query in design view. on the menu bar, click View
| SQL. in the SQL pane, highlight the *entire* SQL statement, and click the
copy button on the toolbar, or press Ctrl+C on your keyboard, then paste
into your message.

 

[Geoff]

Tina,

Thanks.

Here is what i pulled from SQL:

SELECT [Customer Inventory (cleaned) v2].City, [Customer Inventory
(cleaned) v2].State, [Globe answers rolled up].Unit_Std_Maint_Cost INTO
v3
FROM [Customer Inventory (cleaned) v2] LEFT JOIN [Globe answers rolled
up] ON ([Customer Inventory (cleaned) v2].City = [Globe answers rolled
up].City) AND ([Customer Inventory (cleaned) v2].Face_Description =
[Globe answers rolled up].Desc_) AND ([Customer Inventory (cleaned)
v2].Face_Description = [Globe answers rolled up].Model_Style) AND
([Customer Inventory (cleaned) v2].Manufacturer = [Globe answers rolled
up].Vendor);

 

[tina]

okay, first of all, you're using a LEFT JOIN. that means all the records on
the "left side" table [Customer Inventory (cleaned) v2] will be returned
(subject to criteria) regardless of whether there are matched records in the
"right side" table [Globe answers rolled up]. by definition, an INNER JOIN
returns only the records that match in both tables, leaving out unmatched
records from either table.

your joined fields are
[Customer Inventory (cleaned) v2] to [Globe answers rolled up]
City City
Face_Description Desc_
Face_Description Model_Style
Manufacturer Vendor

the state fields are not joined at all (unless one of the above pairs *is*
the "state" field, with a different name). and Face_Description is joined to
two fields in the other table. it's very unusual to see a single field in
one table linked to two fields in the *same* other table; that could
possibly have caused the Cartesian recordset (all records from both tables)
that you said you got when you used the inner join.

suggest you try building the query with INNER JOIN between *only* the
city/city and state/state fields. see if you get the return dataset you want
strictly for those matches. if so, then add the other field joins that you
need *one at a time*, checking the return dataset after each new join is
added. post back with your results.

hth