A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How can I Automate a complex update process involving 3-4 tables?.



 
 
Thread Tools Display Modes
  #1  
Old April 25th, 2008, 10:11 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
efandango
external usenet poster
 
Posts: 489
Default How can I Automate a complex update process involving 3-4 tables?.

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)

  #2  
Old April 26th, 2008, 06:37 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
strive4peace
external usenet poster
 
Posts: 1,670
Default How can I Automate a complex update process involving 3-4 tables?.

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)

  #3  
Old April 26th, 2008, 08:09 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
efandango
external usenet poster
 
Posts: 489
Default How can I Automate a complex update process involving 3-4 tabl

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)


  #4  
Old April 27th, 2008, 12:15 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
strive4peace
external usenet poster
 
Posts: 1,670
Default How can I Automate a complex update process involving 3-4 tabl

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)

  #5  
Old April 27th, 2008, 12:38 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
efandango
external usenet poster
 
Posts: 489
Default How can I Automate a complex update process involving 3-4 tabl

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)


  #6  
Old April 27th, 2008, 01:03 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
strive4peace
external usenet poster
 
Posts: 1,670
Default How can I Automate a complex update process involving 3-4 tabl

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)

  #7  
Old April 27th, 2008, 12:03 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
efandango
external usenet poster
 
Posts: 489
Default How can I Automate a complex update process involving 3-4 tabl

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)


  #8  
Old April 27th, 2008, 01:52 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
Evi
external usenet poster
 
Posts: 898
Default How can I Automate a complex update process involving 3-4 tabl

So just out of curiosity, Eric
A. Is a Waypoint what most people would call a whole Road, even if it is
intersected by other roads?
(my own street is actually intersected twice along its length)

B. Or is a Waypoint a *section* of road, either between 2 other roads or (in
the case of a Dead End) finishing in a ditch.?

C. Or is a Run is a section of Road defined as being between RoadA and RoadB

D. Or is the Run a series of Waypoints (ie Roads) - like the route from the
Bank to the Grocery Store?

I'm interested how it can be 'working well' when users need to ' trawl
through the master records of waypoints and create sub records for any
previous (dupe) records elsewhere' whatever that means.

Evi




"strive4peace" wrote in message
...
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)



  #9  
Old April 27th, 2008, 03:01 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
efandango
external usenet poster
 
Posts: 489
Default How can I Automate a complex update process involving 3-4 tabl

A. Yes a Waypoint is a whole road.
D. a Run is a collection of Waypoints, or put another way, a whole route.

What I meant by it is working well, was for a single instance of a Waypoint
and the associated form which contains the intersection data.

Where it is not working well, is when a user looks at a Run (route), let's
say Run No.5 which is let's say 'Main Street' to 'South Street. then focuses
on the Waypoint in question 'Middle Street' and goes to the subform which
will take the data for that Waypoint intersection and fills in the relevant
fields. Now let's say he goes through the various Runs and comes across
Run.73 which is 'Bridge Street' to 'Lower Street' but this also has among
others, the same Waypoint as Run No.5 which is 'Middle Street'. Now he has to
go to the subform fields that relate to that Waypoint/Run and fill in
identical data to the entry in Run No.5 It is this repetetion of labour that
I want to avoid. One other point is that the subform contains the
intersection data, but that also has another subform that contains other
relevant data to that intersection. So, not only does the user have to
remember what data he entered for the 1st subform, but also the
'micro-detail' of the 2nd subform. Across a database of very many routes,
this can be a difficult task to accomplish as well as being prone to
inaccuracies.

Does that make sense for you?

regards

Eric


"Evi" wrote:

So just out of curiosity, Eric
A. Is a Waypoint what most people would call a whole Road, even if it is
intersected by other roads?
(my own street is actually intersected twice along its length)

B. Or is a Waypoint a *section* of road, either between 2 other roads or (in
the case of a Dead End) finishing in a ditch.?

C. Or is a Run is a section of Road defined as being between RoadA and RoadB

D. Or is the Run a series of Waypoints (ie Roads) - like the route from the
Bank to the Grocery Store?

I'm interested how it can be 'working well' when users need to ' trawl
through the master records of waypoints and create sub records for any
previous (dupe) records elsewhere' whatever that means.

Evi




"strive4peace" wrote in message
...
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)




  #10  
Old April 28th, 2008, 09:36 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries
strive4peace
external usenet poster
 
Posts: 1,670
Default How can I Automate a complex update process involving 3-4 tabl

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)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.