Assign key value to orphans

[Guest]

Hi all,
Unfortunately, I don't see a way around this. Basically, I am dealing with
2 seperate databases.
tblPermit (a disconnected copy from the permit database) has the PK of
PWPermitID.
tblCuts (from the Utility Street Cuts database) has the PK of CutID and
eventually gets assigned thePWPermitID.
tblUtility (from the Utility Street Cuts database) has the PK of UtilityID
and is used as a FK for the previous 2 tables.
tblComplaints (from the Utility Street Cuts database) has a field for each
of the previous 3 PK's, UtilityID the only one being required at the time of
data entry.

tblComplaints is used in a subform to collect complaint data pertaining to
Permits or Utility Cuts and is also available as a standalone form. I did
the standalone form because the person doing the data entry may not have
immediate access to the Permit or Utility Cut data.

What I am trying to figure out is how to assign the proper FK to the
record(s) once all the data is available. Right now all I have is a form
based on tblUtility with 3 subforms linked on the UtilityID.

Thank you for your time,
NickX

 

[John W. Vinson]

What I am trying to figure out is how to assign the proper FK to the
record(s) once all the data is available. Right now all I have is a form
based on tblUtility with 3 subforms linked on the UtilityID.

Without knowing HOW to determine which would be the proper FK value based on
data in the table, I don't see how anyone could answer. What's the nature of
your data? What information exists in the table which would allow the proper
FK to be determined?

John W. Vinson [MVP]

 

[Guest]

John,
Thank you for responding.

Without knowing HOW to determine which would be the proper FK value based on
data in the table, I don't see how anyone could answer.

HOW is the million dollar question.

What's the nature of your data?

This is inspection data that is loosely associated with Permit data (permits
issued for doing work in the roadway). Most of the time the complaints will
be directly associated with an inspection report or permit, but on some
occasions there will be a need to fill in the complaint form without the
benefit of knowing which parent record it should belong to.

What information exists in the table which would allow the proper
FK to be determined?

The only thing that really exists is the location or address, but one person
may enter the location as "123 Main St" and another person may enter the same
location as "Main St between First St and Second St". Basically, I probably
need to set up some sort of form that would make it easy for the inspector to
make the determination based on his knowledge of the location and the
specifics of the complaint.

Thanks,
NickX
PS: We do use a standardized street name table and an address table when
filling in the location.