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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|