Hi efandango (what is your name?)

can you please define some of your terminology? for instance, what is a
Waypoint? Run_waypoint?

Road_Name_From
Road_Name_To

seem like you should have a table:

Roads
- RoadID, autonumber
- Road_Name, text

and you should be storing IDs in related tables:

RoadID_From, long integer, defaultvalue -- null
RoadID_To, long integer, defaultvalue -- null

Warm Regards,
Crystal

*
(: have an awesome day
*


efandango wrote:
To some of you experts, it may be small change… but, To me this problem is
complex and is beginning to hurt… I have tried various methods of doing
complex updates using mainly queries and a bit of form code, but just can’t
make the breakthrough required to bring it all together, so If someone can
help by getting their teeth into this problem with me, I would be very
grateful.

The situation:
I have a large set of master records ‘tbl_Waypoints’ and some of the field
records [Run_waypoint] have a related sub record in ‘tbl_Road_Restrictions’,
which has its own set of Sub Records in ‘tbl_Road_Restrictions_Detail’.

The problem:
Each time a set of sub records are created, a flag [Restriction_Flag] is
automatically adjusted to say ‘Yes’ from a default of ‘No’ and changes color
to blue on the form**. This indicates that a particular waypoint now has a
subroute associated to it. However, whenever a user creates a new subset of
records for a given Waypoint, that particular Waypoint could also exist on
another set of master records elesewhere in the database and woud therefore
require an identical set of sub records assigned to it/them.

**this process is done via a function that say’s if any one of
‘tbl_Road_Restrictions.Road_Name_From’ or ‘tbl_Road_Restrictions
.Road_Name_To’ exists in ‘tbl_Waypoint.Run_waypoint’ then mark the flag.

The process:
Assuming a master ‘tbl_Waypoints’ record has [Restriction_Flag] marked as
Yes (-1) and a duplicate field exists in one master record set (unflagged),
then find the other duplicates in the other master record sets, and do the
following: Mark the master records [Restriction_Flag] as Yes (-1) and then
create a copy of the (original) related sub record (and its related sub
records), and then assign them to the other master records.

How would I go about achieving this process as an automatic process/query. I
have never done anyhting like this, and my sql skills are not upto the job,
can someone please help me?


These are the 4 tables involved, and their fields, relationships). There are
other fields involved but I don’t think they are required for this right now,
so for the sake of clarity, I will leave them out for now.

tbl_Runs Overall Master Records (1 record instance of each)
[Run_No] (pk, number)

tbl_Waypoints Master Records
[Run_Waypoint_ID] (pk, number)
[Run_No] (number) (links to above)
[Restriction_Flag] (yes/no)
[Run_waypoint] (txt)
[Postcode]

tbl_Road_Restrictions Sub Master II Records (1 record instance of each)
[Road_Restriction_ID] (pk, number)
[Run_No] (Links to overall master)
[Road_Name_From] (txt)
[Postcode_From] (txt)
[Road_Name_To] (txt)
[Postcode_To] (txt)

tbl_Road_Restrictions_Detail Numerous records
[Road_Restrictions_Detail_ID] (pk, number)
[Road_Restriction_ID] (links to above)
[Run_waypoint] (txt)
[Postcode] (txt)