modifying table relationships and adding data

[TomYC]

I have two tables:
t_issue, containing the field issue_id (a primary key);
and
Department, containing the two fields
issue_id and ReferralDept, where issue_id is a foreign key in a many to one relation to the field in t_issue.

The problem is that prior to creating the latter table and its relation to t_issue, many records in t_issue were created; that is, there are records in t_issue with a connection to the table Department, with referential integrity, but there are many "old" records with no relation. That means, I have discovered, that a query involving both these tables will only show items where issue_id exists in both tables.

So the question is this: how can I create, say, an update query, to populate the newer table Department, with every issue_id field that is contained in the table t_issue but which is NOT already in Department already. I can get a list, via a query, of all such issue_id fields, and I'd like to import them into Department table with a dummy ReferralDept field, such as "NotReferred" or " ". Obviously I haven't been able to create the update query yet, because any query with Department in it becomes "blind" to the records in t_issue that are presently unrelated to it!!

Many thanks--

 

[Vyurr]

I'm not 100% sure on this as I'm fairly new to Access, but couldn't you just adjust the join properties in your query between the two tables to include all records form t_issue and records from Department where both are equal? (Not sure if that would be called a Right or Left Join, but anyways...

V

 

[PHV]

SQL code:

INSERT INTO Department (issue_id,ReferralDept)
SELECT I.issue_id, 'NotReferred'
FROM t_issue I LEFT JOIN Department D ON I.issue_id = D.issue_id
WHERE D.issue_id Is Null

Another way:

INSERT INTO Department (issue_id,ReferralDept)
SELECT issue_id, 'NotReferred' FROM t_issue
WHERE issue_id NOT IN (SELECT issue_id FROM Department WHERE issue_id Is Not Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886