Add foreign key to existing records

[zeroaccess]

Say you needed to add a foreign key (see attached) to a table that has existing records and relationships. What is the easiest way?

 

[Gasman]

I do not believe you would.?
You would look in tblSampleDetails to get the InspectionID ?

Using spaces in fieldnames means more work typing as well.?

 

[zeroaccess]

Normally you wouldn't, but this arrangement is greatly complicating my queries. I thought being able to count how many of an item in table three occurs per table one would be nice.

 

[jdraw]

Perhaps you could describe the scenario in simple English terms and highlight what and how

this arrangement is greatly complicating my queries

.

 

[isladogs]

I agree that you shouldn't do so, not least because it creates multiple relationships between those tables.
Its also not clear how that would simplify your queries

 

[Uncle Gizmo]

Normally you wouldn't, but this arrangement is greatly complicating my queries

I'm with Jack on this one, we need more information. However if the problem you are trying to solve is that you want to display some information from the inspection table and a query is not cutting it, then what I've done in the past is create a Form in datasheet view and use a combo box as a substitute for a step in a query. Now you have an underlying query for the form, (which you display in datasheet view) and the form contains a combobox which in itself contains another query.

 

[The_Doc_Man]

There is also the idea that if you add the extra field to the third table but DO NOT add a permanent relationship, you can build a query using the query design grid. When you drop the first and third tables into the graphics area above the query grid, you can draw a TEMPORARY relationship that avoids the confusion of having that 2nd table in the way. That way you could avoid having a permanent anomalous relationship that would be caused by having both a direct and an indirect path from table 1 to table 3.

 

[zeroaccess]

There is also the idea that if you add the extra field to the third table but DO NOT add a permanent relationship, you can build a query using the query design grid. When you drop the first and third tables into the graphics area above the query grid, you can draw a TEMPORARY relationship that avoids the confusion of having that 2nd table in the way. That way you could avoid having a permanent anomalous relationship that would be caused by having both a direct and an indirect path from table 1 to table 3.

Right - this sounds like it's worth trying.

I'm thinking an Update query could do this, and am experimenting now. If you have any suggestions on that, do tell.

If it helps me make queries to get my statistics it would be worth it. If it doesn't work, I'll delete the field.

 

[strive4peace]

hi Zero

here is the SQL to update. You'd want to run this query before you do statistics anytime since values could change. Maybe also name the field a little different -- I used InspectionIDcopy

UPDATE tblErrorDetails
INNER JOIN tblSampleDetails
ON tblErrorDetails.[Sample ID] = tblSampleDetails.[Sample ID]
SET tblErrorDetails.[InspectionIDcopy] = tblSampleDetails.[Inspection ID]

I'd remove spaces from your fieldnames though, as Gasman also mentioned.

 

[zeroaccess]

Great, thank you. I will let everyone know if this helps achieve my goal.