Hi Eric,

I got your database ... sorry I have not had time to do more than take a
quick glance at it. I will try to get back to you by tomorrow

currently in Pennsylvania ...

Warm Regards,
Crystal

*
(: have an awesome day
*


efandango wrote:
Hello Crystal,

I have sent you a cut down version of the file. Please let me know when you
have it.

btw, where are you based (generally)?

I'm in the UK.



"strive4peace" wrote:

Hi Eric,

that file size is fine --

1. compact/repair database
2. zip it up

yes, I am watching this thread ~~

Warm Regards,
Crystal

*
(: have an awesome day
*


efandango wrote:
Crystal,

the db is Access 2007. it zips down to 3.5Mb. will that be ok?

I will have to get permission to send the file, failing that I will try to
give a simpler, easier to understand explanation of what I am trying to do.
do you have this thread on auto noify, in case it takes me a while to compile
a simpler explanation?

regards

Eric


"strive4peace" wrote:

Hi Eric,

I am sorry, I am just not following your request ... please email me
your database

strive4peace2006 at yahoo.com
subject = from Eric

specify the name of the form/subform to look at

I will answer ask for clarification and answer your questions in this post

Warm Regards,
Crystal

*
(: have an awesome day
*


efandango wrote:
Hi Crystal,

A Waypoint is a Road Name.

Road_Name_From
Road_Name_To
are two address(road name) fields selected on a subform.

Perhaps it would help if I explain how the relationship works.

The Waypoints are lists of road names on a continous form, like this

Run List 39:

Green Dragon Yard E1
Greenfield Road E1
Grindal House E1
Gun Street E1
Gunthorpe Street E1
Hanbury Street E1

Every so often one of these waypoints will be related to a subrecord which
contains intersections that relate to that particular single Waypoint:

For example, Gun Street, E1 will have an interection of two other streets;
[Road_Name_From] and [Road_Name_To]

All the waypoints for all the master records of 'Run Lists' already exist.
The user has to go through each list of Runs (made up of waypoints) and
decide which 'Waypoint' has an Intersection. Then go to the subform and fill
in (via combo boxes) the appropriate Interesction street names.

Can i point out that the forms and tables I have exisiting, work well at the
moment, and I don't really want to change that sitation or create extra/new
fields, instead I would prefer to work with what I have exisitng.

The reason for wanting to do what i stated in my original post, was to avoid
the user having to trawl through the master records of waypoints and create
sub records for any previous (dupe) records elsewhere; Instead, I want to add
new records from 'tbl_Road_Restrictions' and its sub-table
'tbl_Road_Restrictions_Detail' to duplicate Waypoints (without an
intersection subrecord) in other corresponding records of 'tbl_Waypoints'
using [Run_No] as the reference/link.

I hope this makes some sense to you, if you need more explanation, just let
me know.

regards

Eric





"strive4peace" wrote:

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)