Joining two tables

[Brian]

I need help joining info from two tables.
Table1 and Table 2 have the same
fields(Node,Card,Slot,Facility,Sub-Port,Channel,Group,Cic) Group is
text, rest are number.
Table 1 contains records for all possible combinations of
Node,Card,Slot,Facility,Sub-Port,Channel which indicates a physical
port on a piece of equipment. Table 2 is imported from a piece of
equipment containing all above info that has an assigned Group and Cic.
I want to the populate Table 1 with the Group and Cic from Table 2,
their will always be a match between the physical port fields. The only
other quirk is the sub-port field is not always populated, but it would
be consistent between the two tables. How would I make this happen.

[David Seeto via AccessMonster.com]

Hi Brian,

I think your starting point should be an update query that populates Table2
from the corresponding records in Table2:
UPDATE Table1 INNER JOIN Table2
ON (Table1.Channel = Table2.Channel)
AND (Table1.[Sub-Port] = Table2.[Sub-Port])
AND (Table1.Facility = Table2.Facility)
AND (Table1.Slot = Table2.Slot)
AND (Table1.Card = Table2.Card) AND (Table1.Node = Table2.Node)
SET Table1.[Group] = [Table2].[Group], Table1.Cic = [Table2].[Cic];

This matches on Sub-Port as well - when you say that

the sub-port field is not always populated, but it would
be consistent between the two tables

does that mean that if the Sub-Port is blank in Table2, then the matching
record in Table1 would also be blank? If so, then your update query will do
the job. If not, we would probably need a separate update query to handle
the blank Sub-Ports.

Hope that helps!

--
Message posted via http://www.accessmonster.com