Add foreign key to existing records (1 Viewer)

zeroaccess

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
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

Enthusiastic Amateur
Local time
Today, 17:44
Joined
Sep 21, 2011
Messages
14,045
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

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
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

Super Moderator
Staff member
Local time
Today, 13:44
Joined
Jan 23, 2006
Messages
15,364
Perhaps you could describe the scenario in simple English terms and highlight what and how
this arrangement is greatly complicating my queries
.
 

isladogs

MVP / VIP
Local time
Today, 17:44
Joined
Jan 14, 2017
Messages
18,186
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

Nifty Access Guy
Staff member
Local time
Today, 17:44
Joined
Jul 9, 2003
Messages
16,244
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

Immoderate Moderator
Staff member
Local time
Today, 12:44
Joined
Feb 28, 2001
Messages
26,999
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

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
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

AWF VIP
Local time
Today, 12:44
Joined
Apr 3, 2020
Messages
1,003
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
Code:
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.
 

Users who are viewing this thread

Top Bottom