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 |
#21
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Hello Cystal,
I have sent you a file in Access 2003. regards Eric "strive4peace" wrote: Hi Eric, ps, I will look at your file faster if you convert it to Access 2003 before you send it to me ... that is the version I am using most of the time and, with what I am helping you with, it (probably) doesn't matter... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road ~~~ now, with the unique index, we can add the road names used from other tables without fear of duplicating what is already there... INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_To FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_To) Is Not Null)); INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_From FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_From) Is Not Null)); when you run each of these queries, Access will probably tell you it cannot append all records -- and that is fine, the problem is that some of them are already there -- your unique index on RoadName is protecting you smile Now, add the following fields to tbl_Road_Restrictions - RoadID_to, Number, Field Size -- Long, Default Value -- null, Description -- Road Name To - RoadID_from, number, Field Size -- Long, Default Value -- null, Description -- Road Name From ok, now we just have to update the ID fields... UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_To = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_To = [tbl_Roads].[RoadID]; UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_From = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_From = [tbl_Roads].[RoadID]; verify that IDs are filled wherever you have road names in tbl_Road_Restrictions ... then you can delete the following fields: Road_Name_From Road_Name_to ~~ use this same method to change Roadnames to IDs in your other tables each place on a form where you have a textbox for the RoadName, change it to a combobox (right-click, changeTo -- combo) and set the properties I specified above and put code on the NotInList event ~~~~~~~~~` once your data is using IDs and not names, then send me your db again and I will guide you for creating the cross-reference table for specifying the restrictions I could do this for you but that would not teach you anything. While you may be looking at this as a looming task, it should take no more than a couple hours... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: "strive4peace" wrote: hi Eric, now, to your question: "What I want to do is have the update process say, this Waypoint also exists elsewhere in the database, therefore a copy of the relevant road restriction and the related sub record set for the details will be made and assigned to those duplicates wherever they appear." It would be more accurate to make comparisons if you were storing IDs rather than names (I know you said you do not want to make these changes, but consider the life of the database and how far into that life you are right now...) I am guessing that the Road_restrictions_Detail lists the Waypoint where the restriction applies in the Road Restrictions table... consider this: If you had a Roads table with RoadID ... then the Road_restrictions_Detail table would, in essence, be a cross-reference Hi Crystal, For now, I would like to deal with the main question in hand, and come back on the various points you made about structure, coding, etc. so what you are saying is have the best thing to do is have the various waypoints as individual entities, like: Waypoint_Address Waypoint_ID Main street 363 Main street 363 South Street 261 Narrow Lane 47 South Street 261 ... Instead of: Waypoint_Address Waypoint_ID Main street 363 Main street 1492 South Street 391 Narrow Lane 47 South Street 261 Assuming that is what you mean; I guess it makes sense, and makes for a normalised structure; right? |
#22
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Hi Eric,
got it, thanks in tbl_Waypoints sort on Run_waypoint_ID delete the 0's you have then make a relationship from tlb_Waypoints_Master_List to tbl_Waypoints on Run_waypoint_ID and Enforce Referential Integrity If you want to be able to type a Waypoint that is not in the list and automatically create a record in the master list, on frm_Waypoints, put this code on the NotInList [event procedure] for Run_waypoint '~~~~~~~~~~~~~~~~~~~~ Private Sub Run_waypoint_NotInList( _ NewData As String, _ Response As Integer) Dim s As String _ , mRecordID As Long _ , mText As String 'if you want to convert to ProperCase mText = StrConv(NewData, vbProperCase) s = "INSERT INTO tbl_Waypoints_Master_List(Run_waypoint) " _ & " SELECT '" & mText & "';" 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("Run_waypoint_ID", "tbl_Waypoints_Master_List")) If mRecordID 0 Then Response = acDataErrAdded Me.ActiveControl = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~ in Private Sub Run_waypoint_AfterUpdate() add this line to the top -- If Len(Trim(Nz(Run_waypoint.Column(2), ""))) = 0 Then Exit Sub Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Cystal, I have sent you a file in Access 2003. regards Eric "strive4peace" wrote: Hi Eric, ps, I will look at your file faster if you convert it to Access 2003 before you send it to me ... that is the version I am using most of the time and, with what I am helping you with, it (probably) doesn't matter... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road ~~~ now, with the unique index, we can add the road names used from other tables without fear of duplicating what is already there... INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_To FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_To) Is Not Null)); INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_From FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_From) Is Not Null)); when you run each of these queries, Access will probably tell you it cannot append all records -- and that is fine, the problem is that some of them are already there -- your unique index on RoadName is protecting you smile Now, add the following fields to tbl_Road_Restrictions - RoadID_to, Number, Field Size -- Long, Default Value -- null, Description -- Road Name To - RoadID_from, number, Field Size -- Long, Default Value -- null, Description -- Road Name From ok, now we just have to update the ID fields... UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_To = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_To = [tbl_Roads].[RoadID]; UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_From = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_From = [tbl_Roads].[RoadID]; verify that IDs are filled wherever you have road names in tbl_Road_Restrictions ... then you can delete the following fields: Road_Name_From Road_Name_to ~~ use this same method to change Roadnames to IDs in your other tables each place on a form where you have a textbox for the RoadName, change it to a combobox (right-click, changeTo -- combo) and set the properties I specified above and put code on the NotInList event ~~~~~~~~~` once your data is using IDs and not names, then send me your db again and I will guide you for creating the cross-reference table for specifying the restrictions I could do this for you but that would not teach you anything. While you may be looking at this as a looming task, it should take no more than a couple hours... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: "strive4peace" wrote: hi Eric, now, to your question: "What I want to do is have the update process say, this Waypoint also exists elsewhere in the database, therefore a copy of the relevant road restriction and the related sub record set for the details will be made and assigned to those duplicates wherever they appear." It would be more accurate to make comparisons if you were storing IDs rather than names (I know you said you do not want to make these changes, but consider the life of the database and how far into that life you are right now...) I am guessing that the Road_restrictions_Detail lists the Waypoint where the restriction applies in the Road Restrictions table... consider this: If you had a Roads table with RoadID ... then the Road_restrictions_Detail table would, in essence, be a cross-reference Hi Crystal, For now, I would like to deal with the main question in hand, and come back on the various points you made about structure, coding, etc. so what you are saying is have the best thing to do is have the various waypoints as individual entities, like: Waypoint_Address Waypoint_ID Main street 363 Main street 363 South Street 261 Narrow Lane 47 South Street 261 ... Instead of: Waypoint_Address Waypoint_ID Main street 363 Main street 1492 South Street 391 Narrow Lane 47 South Street 261 Assuming that is what you mean; I guess it makes sense, and makes for a normalised structure; right? |
#23
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Ok, I will try that. But going back to my original question; how would I make
things so that the user can avoid 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. regards Eric "strive4peace" wrote: Hi Eric, got it, thanks in tbl_Waypoints sort on Run_waypoint_ID delete the 0's you have then make a relationship from tlb_Waypoints_Master_List to tbl_Waypoints on Run_waypoint_ID and Enforce Referential Integrity If you want to be able to type a Waypoint that is not in the list and automatically create a record in the master list, on frm_Waypoints, put this code on the NotInList [event procedure] for Run_waypoint '~~~~~~~~~~~~~~~~~~~~ Private Sub Run_waypoint_NotInList( _ NewData As String, _ Response As Integer) Dim s As String _ , mRecordID As Long _ , mText As String 'if you want to convert to ProperCase mText = StrConv(NewData, vbProperCase) s = "INSERT INTO tbl_Waypoints_Master_List(Run_waypoint) " _ & " SELECT '" & mText & "';" 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("Run_waypoint_ID", "tbl_Waypoints_Master_List")) If mRecordID 0 Then Response = acDataErrAdded Me.ActiveControl = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~ in Private Sub Run_waypoint_AfterUpdate() add this line to the top -- If Len(Trim(Nz(Run_waypoint.Column(2), ""))) = 0 Then Exit Sub Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Cystal, I have sent you a file in Access 2003. regards Eric "strive4peace" wrote: Hi Eric, ps, I will look at your file faster if you convert it to Access 2003 before you send it to me ... that is the version I am using most of the time and, with what I am helping you with, it (probably) doesn't matter... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road ~~~ now, with the unique index, we can add the road names used from other tables without fear of duplicating what is already there... INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_To FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_To) Is Not Null)); INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_From FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_From) Is Not Null)); when you run each of these queries, Access will probably tell you it cannot append all records -- and that is fine, the problem is that some of them are already there -- your unique index on RoadName is protecting you smile Now, add the following fields to tbl_Road_Restrictions - RoadID_to, Number, Field Size -- Long, Default Value -- null, Description -- Road Name To - RoadID_from, number, Field Size -- Long, Default Value -- null, Description -- Road Name From ok, now we just have to update the ID fields... UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_To = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_To = [tbl_Roads].[RoadID]; UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_From = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_From = [tbl_Roads].[RoadID]; verify that IDs are filled wherever you have road names in tbl_Road_Restrictions ... then you can delete the following fields: |
#24
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Hi Eric,
I would have covered that ... but you did not replace the text fields in 'tbl_Road_Restrictions' and 'tbl_Road_Restrictions_Detail' with numeric ID fields ... so it did not look like you were ready for that information. once you do this, as the user types a waypoint name, the combobox will move to that selection -- they do not have to "trawl through the master records" with the arrows... and, if they end up typing a completely new name, you can use a NotInList event to add those new waypoint names to your master list. Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Ok, I will try that. But going back to my original question; how would I make things so that the user can avoid 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. regards Eric "strive4peace" wrote: Hi Eric, got it, thanks in tbl_Waypoints sort on Run_waypoint_ID delete the 0's you have then make a relationship from tlb_Waypoints_Master_List to tbl_Waypoints on Run_waypoint_ID and Enforce Referential Integrity If you want to be able to type a Waypoint that is not in the list and automatically create a record in the master list, on frm_Waypoints, put this code on the NotInList [event procedure] for Run_waypoint '~~~~~~~~~~~~~~~~~~~~ Private Sub Run_waypoint_NotInList( _ NewData As String, _ Response As Integer) Dim s As String _ , mRecordID As Long _ , mText As String 'if you want to convert to ProperCase mText = StrConv(NewData, vbProperCase) s = "INSERT INTO tbl_Waypoints_Master_List(Run_waypoint) " _ & " SELECT '" & mText & "';" 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("Run_waypoint_ID", "tbl_Waypoints_Master_List")) If mRecordID 0 Then Response = acDataErrAdded Me.ActiveControl = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~ in Private Sub Run_waypoint_AfterUpdate() add this line to the top -- If Len(Trim(Nz(Run_waypoint.Column(2), ""))) = 0 Then Exit Sub Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Cystal, I have sent you a file in Access 2003. regards Eric "strive4peace" wrote: Hi Eric, ps, I will look at your file faster if you convert it to Access 2003 before you send it to me ... that is the version I am using most of the time and, with what I am helping you with, it (probably) doesn't matter... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road ~~~ now, with the unique index, we can add the road names used from other tables without fear of duplicating what is already there... INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_To FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_To) Is Not Null)); INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_From FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_From) Is Not Null)); when you run each of these queries, Access will probably tell you it cannot append all records -- and that is fine, the problem is that some of them are already there -- your unique index on RoadName is protecting you smile Now, add the following fields to tbl_Road_Restrictions - RoadID_to, Number, Field Size -- Long, Default Value -- null, Description -- Road Name To - RoadID_from, number, Field Size -- Long, Default Value -- null, Description -- Road Name From ok, now we just have to update the ID fields... UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_To = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_To = [tbl_Roads].[RoadID]; UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_From = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_From = [tbl_Roads].[RoadID]; verify that IDs are filled wherever you have road names in tbl_Road_Restrictions ... then you can delete the following fields: |
#25
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
The reason I hadn't changed them (yet) was because I am still dealing with
the problem of how to deal with various suplicate Addresses that are in the waypoint table that will constitute the eventual address master list, the table that you call roadnames. The problem is that the full database is a live document (unlike the cutdown version I send you) and I can't allow the various waypoint lists to fall out of order, at the same time, If I consolidate the dupe addresses into single-entity representations of those addresses, I will end up with gaps in my waypoiint lists. In short the whole thing has to be done manually and meothodically, I can't see how a query would achieve the same result. So for now, I am trying to do just that, make the addresses from the waypoint table spawn a tbl_Waypoints_Master_List, so pleae bear with me while I get this done and I will come back to you. I jiust wanted to know how the process of creating copies of the related 'tbl_Road_Restrictions' and its sub-table 'tbl_Road_Restrictions_Detail' would work as much out of curiosity as it is out of need. Though to be honest, I am confused by your answer "once you do this, as the user types a waypoint name, the combobox will move to that selection -- they do not have to "trawl through the master records" with the arrows... and, if they end up typing a completely new name, you can use a NotInList event to add those new waypoint names to your master list." which just sounds like a description of a combo box using autoexpand and not the answer I was expecting to the main question that began this thread. regards Eric "strive4peace" wrote: Hi Eric, I would have covered that ... but you did not replace the text fields in 'tbl_Road_Restrictions' and 'tbl_Road_Restrictions_Detail' with numeric ID fields ... so it did not look like you were ready for that information. once you do this, as the user types a waypoint name, the combobox will move to that selection -- they do not have to "trawl through the master records" with the arrows... and, if they end up typing a completely new name, you can use a NotInList event to add those new waypoint names to your master list. Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Ok, I will try that. But going back to my original question; how would I make things so that the user can avoid 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. regards Eric "strive4peace" wrote: Hi Eric, got it, thanks in tbl_Waypoints sort on Run_waypoint_ID delete the 0's you have then make a relationship from tlb_Waypoints_Master_List to tbl_Waypoints on Run_waypoint_ID and Enforce Referential Integrity If you want to be able to type a Waypoint that is not in the list and automatically create a record in the master list, on frm_Waypoints, put this code on the NotInList [event procedure] for Run_waypoint '~~~~~~~~~~~~~~~~~~~~ Private Sub Run_waypoint_NotInList( _ NewData As String, _ Response As Integer) Dim s As String _ , mRecordID As Long _ , mText As String 'if you want to convert to ProperCase mText = StrConv(NewData, vbProperCase) s = "INSERT INTO tbl_Waypoints_Master_List(Run_waypoint) " _ & " SELECT '" & mText & "';" 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("Run_waypoint_ID", "tbl_Waypoints_Master_List")) If mRecordID 0 Then Response = acDataErrAdded Me.ActiveControl = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~ in Private Sub Run_waypoint_AfterUpdate() add this line to the top -- If Len(Trim(Nz(Run_waypoint.Column(2), ""))) = 0 Then Exit Sub Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Cystal, I have sent you a file in Access 2003. regards Eric "strive4peace" wrote: Hi Eric, ps, I will look at your file faster if you convert it to Access 2003 before you send it to me ... that is the version I am using most of the time and, with what I am helping you with, it (probably) doesn't matter... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road |
#26
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Hi Eric,
"I can't allow the various waypoint lists to fall out of order" Since the lists must have an order, give the users an Ordr ('Order' is a reserved word) field that is automatically assigned by Access when records are created, and that they have the ability to change -- or use another method*. "In short the whole thing has to be done manually and meothodically" I agree that what you need to do is something methodical; ideally, you would have a 2-dimensional way of keeping things in order. * Another option is to fill Latitude/Longitude for each road and intersection. If you do everything manually, believe me, that will lead to more headaches down the road than you have now! After more thought, I see that perhaps, ideally, you could use something like this (and this is just a start, probably requires more thought): Intersections IntersectID, autonumber - Lat, double - Lng, double Intersect_Waypoints - IntersectID, autonumber - IntSectID, long, FK to Intersections - Run_waypoint_ID, long, FK to tbl_Waypoints_Master_List - DirectionID, long, FK to DirectionIDs DirectionIDs 'so it is not confused with more generic use of term - DirectionID, autonumber - Direction, text, 10 ' E-W, N-S, etc store Latitude and Longitude as numbers in separate fields so that you can calculate distances and use them for ordering records. Currently you are storing BOTH Lat and Long in a text field... if you have this information, you can put it to much better use! Ideally, roads would be straight; but they are not -- this leads to more complexity. You are probably under the gun, so to speak, by your boss to get this done. Please understand that what you are attempting to do is complicated. On the form to identify Road Restrictions, you could store the beginning intersection, the ending intersection, make sure that both are in the Intersections and Intersect_Waypoints table and, if not, put them there. then if necessary, make sure that the intermediate intersections are also stored. This will make it possible to store the data once, keep it ordered, and reuse it (the true goal of a database). What you are doing it not easy, but certainly can be done. I am offering ideas, not a complete solution; you are the best one to see what you have to work with and, given possibnle methods, determine what will work best for your situation. You must spend time studying if you really want to get this right. The way you see it, you are on your last legs of development; the way I see it, you are just beginning to grasp the complexity of the problem. If you are looking for what I refer to as duct-tape, sorry to have wasted your time. ~~~ I apologize that I have not been able to give you the exact answers you seek. When I help someone, I first try to help them get onto the right path with their data structure -- if the foundation isn't right, then anything you do will be a temporary patch to make something work -- and, once done, you may have more patches than cloth! "not the answer I was expecting to the main question that began this thread" It seems to me that what you have been unable to do is to see your database from a different perspective -- I understand this; it is hard to see the trees when you are knee-deep in alligators. You keep coming back to the same question and I fault myself for not being better with explanations; I just don't know any other way to put it. I will be unavailable for the next 1-1/2 weeks. I was hoping to wrap up my responses to you before my trip so you are not left hanging. Maybe someone else can help. Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: The reason I hadn't changed them (yet) was because I am still dealing with the problem of how to deal with various suplicate Addresses that are in the waypoint table that will constitute the eventual address master list, the table that you call roadnames. The problem is that the full database is a live document (unlike the cutdown version I send you) and I can't allow the various waypoint lists to fall out of order, at the same time, If I consolidate the dupe addresses into single-entity representations of those addresses, I will end up with gaps in my waypoiint lists. In short the whole thing has to be done manually and meothodically, I can't see how a query would achieve the same result. So for now, I am trying to do just that, make the addresses from the waypoint table spawn a tbl_Waypoints_Master_List, so pleae bear with me while I get this done and I will come back to you. I jiust wanted to know how the process of creating copies of the related 'tbl_Road_Restrictions' and its sub-table 'tbl_Road_Restrictions_Detail' would work as much out of curiosity as it is out of need. Though to be honest, I am confused by your answer "once you do this, as the user types a waypoint name, the combobox will move to that selection -- they do not have to "trawl through the master records" with the arrows... and, if they end up typing a completely new name, you can use a NotInList event to add those new waypoint names to your master list." which just sounds like a description of a combo box using autoexpand and not the answer I was expecting to the main question that began this thread. regards Eric "strive4peace" wrote: Hi Eric, I would have covered that ... but you did not replace the text fields in 'tbl_Road_Restrictions' and 'tbl_Road_Restrictions_Detail' with numeric ID fields ... so it did not look like you were ready for that information. once you do this, as the user types a waypoint name, the combobox will move to that selection -- they do not have to "trawl through the master records" with the arrows... and, if they end up typing a completely new name, you can use a NotInList event to add those new waypoint names to your master list. Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Ok, I will try that. But going back to my original question; how would I make things so that the user can avoid 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. regards Eric "strive4peace" wrote: Hi Eric, got it, thanks in tbl_Waypoints sort on Run_waypoint_ID delete the 0's you have then make a relationship from tlb_Waypoints_Master_List to tbl_Waypoints on Run_waypoint_ID and Enforce Referential Integrity If you want to be able to type a Waypoint that is not in the list and automatically create a record in the master list, on frm_Waypoints, put this code on the NotInList [event procedure] for Run_waypoint '~~~~~~~~~~~~~~~~~~~~ Private Sub Run_waypoint_NotInList( _ NewData As String, _ Response As Integer) Dim s As String _ , mRecordID As Long _ , mText As String 'if you want to convert to ProperCase mText = StrConv(NewData, vbProperCase) s = "INSERT INTO tbl_Waypoints_Master_List(Run_waypoint) " _ & " SELECT '" & mText & "';" 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("Run_waypoint_ID", "tbl_Waypoints_Master_List")) If mRecordID 0 Then Response = acDataErrAdded Me.ActiveControl = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~ in Private Sub Run_waypoint_AfterUpdate() add this line to the top -- If Len(Trim(Nz(Run_waypoint.Column(2), ""))) = 0 Then Exit Sub Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Cystal, I have sent you a file in Access 2003. regards Eric "strive4peace" wrote: Hi Eric, ps, I will look at your file faster if you convert it to Access 2003 before you send it to me ... that is the version I am using most of the time and, with what I am helping you with, it (probably) doesn't matter... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road |
#27
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Hi Crystal,
I do understand what you are suggesting, and I am able to see the database from a different perspective, I did say that I will make the further changes, having made the initial one of basing my waypoints and the Waypoints Master List (roads) on numerical indexes; it is simply a matter of making sure that as I do that, I am able to keep the integrity of the Runs/Waypoint order intact. You would have seen in my Waypoints table that I have an OrderSeq field for this purpose. What I was reffering to manually was being able to make sure that when I converted the various dupe (master) waypoint addresses into single entity datafields, that they were the correct one's relative to the original Waypoint(s) and order. I have now managed to succesfully redo the Waypoints and Waypoints master list so that they now relay on numerical fields and relationships. Though one or two erroneous dupe records need looking at, the great bulk of them (6000+) are synching properly, so it was this comment you made that I was most relying on: "once your data is using IDs and not names, then send me your db again and I will guide you for creating the cross-reference table for specifying the restrictions I could do this for you but that would not teach you anything. While you may be looking at this as a looming task, it should take no more than a couple hours..." because then I could take my time with recoding the various fields to numerical and knowing that I had some 'engine' code to test with as I went along, and would not need to keep coming back to you and taking up so much of your valubale time. kind regards Eric "strive4peace" wrote: Hi Eric, "I can't allow the various waypoint lists to fall out of order" Since the lists must have an order, give the users an Ordr ('Order' is a reserved word) field that is automatically assigned by Access when records are created, and that they have the ability to change -- or use another method*. "In short the whole thing has to be done manually and meothodically" I agree that what you need to do is something methodical; ideally, you would have a 2-dimensional way of keeping things in order. * Another option is to fill Latitude/Longitude for each road and intersection. If you do everything manually, believe me, that will lead to more headaches down the road than you have now! After more thought, I see that perhaps, ideally, you could use something like this (and this is just a start, probably requires more thought): Intersections IntersectID, autonumber - Lat, double - Lng, double Intersect_Waypoints - IntersectID, autonumber - IntSectID, long, FK to Intersections - Run_waypoint_ID, long, FK to tbl_Waypoints_Master_List - DirectionID, long, FK to DirectionIDs DirectionIDs 'so it is not confused with more generic use of term - DirectionID, autonumber - Direction, text, 10 ' E-W, N-S, etc store Latitude and Longitude as numbers in separate fields so that you can calculate distances and use them for ordering records. Currently you are storing BOTH Lat and Long in a text field... if you have this information, you can put it to much better use! Ideally, roads would be straight; but they are not -- this leads to more complexity. You are probably under the gun, so to speak, by your boss to get this done. Please understand that what you are attempting to do is complicated. On the form to identify Road Restrictions, you could store the beginning intersection, the ending intersection, make sure that both are in the Intersections and Intersect_Waypoints table and, if not, put them there. then if necessary, make sure that the intermediate intersections are also stored. This will make it possible to store the data once, keep it ordered, and reuse it (the true goal of a database). What you are doing it not easy, but certainly can be done. I am offering ideas, not a complete solution; you are the best one to see what you have to work with and, given possibnle methods, determine what will work best for your situation. You must spend time studying if you really want to get this right. The way you see it, you are on your last legs of development; the way I see it, you are just beginning to grasp the complexity of the problem. If you are looking for what I refer to as duct-tape, sorry to have wasted your time. ~~~ I apologize that I have not been able to give you the exact answers you seek. When I help someone, I first try to help them get onto the right path with their data structure -- if the foundation isn't right, then anything you do will be a temporary patch to make something work -- and, once done, you may have more patches than cloth! "not the answer I was expecting to the main question that began this thread" It seems to me that what you have been unable to do is to see your database from a different perspective -- I understand this; it is hard to see the trees when you are knee-deep in alligators. You keep coming back to the same question and I fault myself for not being better with explanations; I just don't know any other way to put it. I will be unavailable for the next 1-1/2 weeks. I was hoping to wrap up my responses to you before my trip so you are not left hanging. Maybe someone else can help. Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: The reason I hadn't changed them (yet) was because I am still dealing with the problem of how to deal with various suplicate Addresses that are in the waypoint table that will constitute the eventual address master list, the table that you call roadnames. The problem is that the full database is a live document (unlike the cutdown version I send you) and I can't allow the various waypoint lists to fall out of order, at the same time, If I consolidate the dupe addresses into single-entity representations of those addresses, I will end up with gaps in my waypoiint lists. In short the whole thing has to be done manually and meothodically, I can't see how a query would achieve the same result. So for now, I am trying to do just that, make the addresses from the waypoint table spawn a tbl_Waypoints_Master_List, so pleae bear with me while I get this done and I will come back to you. I jiust wanted to know how the process of creating copies of the related 'tbl_Road_Restrictions' and its sub-table 'tbl_Road_Restrictions_Detail' would work as much out of curiosity as it is out of need. Though to be honest, I am confused by your answer "once you do this, as the user types a waypoint name, the combobox will move to that selection -- they do not have to "trawl through the master records" with the arrows... and, if they end up typing a completely new name, you can use a NotInList event to add those new waypoint names to your master list." which just sounds like a description of a combo box using autoexpand and not the answer I was expecting to the main question that began this thread. regards Eric "strive4peace" wrote: Hi Eric, I would have covered that ... but you did not replace the text fields in 'tbl_Road_Restrictions' and 'tbl_Road_Restrictions_Detail' with numeric ID fields ... so it did not look like you were ready for that information. once you do this, as the user types a waypoint name, the combobox will move to that selection -- they do not have to "trawl through the master records" with the arrows... and, if they end up typing a completely new name, you can use a NotInList event to add those new waypoint names to your master list. Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Ok, I will try that. But going back to my original question; how would I make things so that the user can avoid 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. regards Eric "strive4peace" wrote: Hi Eric, got it, thanks in tbl_Waypoints sort on Run_waypoint_ID delete the 0's you have then make a relationship from tlb_Waypoints_Master_List to tbl_Waypoints on Run_waypoint_ID and Enforce Referential Integrity If you want to be able to type a Waypoint that is not in the list and automatically create a record in the master list, on frm_Waypoints, put this code on the NotInList [event procedure] for Run_waypoint '~~~~~~~~~~~~~~~~~~~~ Private Sub Run_waypoint_NotInList( _ NewData As String, _ Response As Integer) Dim s As String _ , mRecordID As Long _ , mText As String 'if you want to convert to ProperCase mText = StrConv(NewData, vbProperCase) s = "INSERT INTO tbl_Waypoints_Master_List(Run_waypoint) " _ & " SELECT '" & mText & "';" 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("Run_waypoint_ID", "tbl_Waypoints_Master_List")) If mRecordID 0 Then Response = acDataErrAdded Me.ActiveControl = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~ in Private Sub Run_waypoint_AfterUpdate() add this line to the top -- If Len(Trim(Nz(Run_waypoint.Column(2), ""))) = 0 Then Exit Sub Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Cystal, I have sent you a file in Access 2003. regards Eric "strive4peace" wrote: Hi Eric, ps, I will look at your file faster if you convert it to Access 2003 before you send it to me ... that is the version I am using most of the time and, with what I am helping you with, it (probably) doesn't matter... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" |
#28
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
I saw the old file, and have these comments: I opened the table in Access
2007 and used the visual designer to see the relationships. GetRounds and GetRoundsDetails aren't associated with any tables Points, Runs, Master, and Waypoints are linked Road Restrictions and _Details are linked Too many unlinked tables. I would convert into latitude/longitude based. "efandango" wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road ~~~ now, with the unique index, we can add the road names used from other tables without fear of duplicating what is already there... INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_To FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_To) Is Not Null)); INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_From FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_From) Is Not Null)); when you run each of these queries, Access will probably tell you it cannot append all records -- and that is fine, the problem is that some of them are already there -- your unique index on RoadName is protecting you smile Now, add the following fields to tbl_Road_Restrictions - RoadID_to, Number, Field Size -- Long, Default Value -- null, Description -- Road Name To - RoadID_from, number, Field Size -- Long, Default Value -- null, Description -- Road Name From ok, now we just have to update the ID fields... UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_To = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_To = [tbl_Roads].[RoadID]; UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_From = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_From = [tbl_Roads].[RoadID]; verify that IDs are filled wherever you have road names in tbl_Road_Restrictions ... then you can delete the following fields: Road_Name_From Road_Name_to ~~ use this same method to change Roadnames to IDs in your other tables each place on a form where you have a textbox for the RoadName, change it to a combobox (right-click, changeTo -- combo) and set the properties I specified above and put code on the NotInList event ~~~~~~~~~` once your data is using IDs and not names, then send me your db again and I will guide you for creating the cross-reference table for specifying the restrictions I could do this for you but that would not teach you anything. While you may be looking at this as a looming task, it should take no more than a couple hours... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: "strive4peace" wrote: hi Eric, now, to your question: "What I want to do is have the update process say, this Waypoint also exists elsewhere in the database, therefore a copy of the relevant road restriction and the related sub record set for the details will be made and assigned to those duplicates wherever they appear." It would be more accurate to make comparisons if you were storing IDs rather than names (I know you said you do not want to make these changes, but consider the life of the database and how far into that life you are right now...) I am guessing that the Road_restrictions_Detail lists the Waypoint where the restriction applies in the Road Restrictions table... consider this: If you had a Roads table with RoadID ... then the Road_restrictions_Detail table would, in essence, be a cross-reference Hi Crystal, For now, I would like to deal with the main question in hand, and come back on the various points you made about structure, coding, etc. so what you are saying is have the best thing to do is have the various waypoints as individual entities, like: Waypoint_Address Waypoint_ID Main street 363 Main street 363 South Street 261 Narrow Lane 47 South Street 261 ... Instead of: Waypoint_Address Waypoint_ID Main street 363 Main street 1492 South Street 391 Narrow Lane 47 South Street 261 Assuming that is what you mean; I guess it makes sense, and makes for a normalised structure; right? Though I am not sure how I would ge the query to resolve my original question, but I guess the best thing is to do what you say, and cross that bridge when I come to it... bearing in mind I want the query to update the other various 'Runs' where the waypoint appears in terms of creating copies of the associated 'Road_Restrictions' and the sub ''Road_Restrictions_Detail'. The reason I was reluctant to make those changes is because I have over 5,000 instances of the waypoints divided unevenly across over 330 Runs... but if it is ultimately the best way and results in an elegant solution, then I will do it. For now, if you can confirm that I am intepreting this correctly, then I will set about re-working the various waypoints into their respective ID sets. Though, I haven't figure out how yet because picking up on your coment about how far into the life of the db I am; the answer is all the way, to the end... (in short, all the data is in and save for the occasional adding of a new Run, there won't be any more additions, though their will likely be changes to existing Runs, Waypoints, etc. |
#29
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
David,
GetRounds and GetRoundsDetails were originally associated with tbl_Runs. I unhooked them when I was cutting down the number of records for a smaller filesize in order to email the mdb; I simply forgot to re-synch them back to the main table before I sent the file. I am not sure what you mean by "I would convert into latitude/longitude based". however, with regard to Lat and Lon fields, please don't let them obscure the main issue, which is to synch assciated GetRounds with individual waypoints across more than one Run. regards Eric "David Glienna (MVP)" wrote: I saw the old file, and have these comments: I opened the table in Access 2007 and used the visual designer to see the relationships. GetRounds and GetRoundsDetails aren't associated with any tables Points, Runs, Master, and Waypoints are linked Road Restrictions and _Details are linked Too many unlinked tables. I would convert into latitude/longitude based. "efandango" wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road ~~~ now, with the unique index, we can add the road names used from other tables without fear of duplicating what is already there... INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_To FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_To) Is Not Null)); INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_From FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_From) Is Not Null)); when you run each of these queries, Access will probably tell you it cannot append all records -- and that is fine, the problem is that some of them are already there -- your unique index on RoadName is protecting you smile Now, add the following fields to tbl_Road_Restrictions - RoadID_to, Number, Field Size -- Long, Default Value -- null, Description -- Road Name To - RoadID_from, number, Field Size -- Long, Default Value -- null, Description -- Road Name From ok, now we just have to update the ID fields... UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_To = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_To = [tbl_Roads].[RoadID]; UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_From = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_From = [tbl_Roads].[RoadID]; verify that IDs are filled wherever you have road names in tbl_Road_Restrictions ... then you can delete the following fields: Road_Name_From Road_Name_to ~~ use this same method to change Roadnames to IDs in your other tables each place on a form where you have a textbox for the RoadName, change it to a combobox (right-click, changeTo -- combo) and set the properties I specified above and put code on the NotInList event ~~~~~~~~~` once your data is using IDs and not names, then send me your db again and I will guide you for creating the cross-reference table for specifying the restrictions I could do this for you but that would not teach you anything. While you may be looking at this as a looming task, it should take no more than a couple hours... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: "strive4peace" wrote: hi Eric, now, to your question: "What I want to do is have the update process say, this Waypoint also exists elsewhere in the database, therefore a copy of the relevant road restriction and the related sub record set for the details will be made and assigned to those duplicates wherever they appear." It would be more accurate to make comparisons if you were storing IDs rather than names (I know you said you do not want to make these changes, but consider the life of the database and how far into that life you are right now...) I am guessing that the Road_restrictions_Detail lists the Waypoint where the restriction applies in the Road Restrictions table... consider this: If you had a Roads table with RoadID ... then the Road_restrictions_Detail table would, in essence, be a cross-reference Hi Crystal, For now, I would like to deal with the main question in hand, and come back on the various points you made about structure, coding, etc. so what you are saying is have the best thing to do is have the various waypoints as individual entities, like: Waypoint_Address Waypoint_ID Main street 363 Main street 363 South Street 261 Narrow Lane 47 South Street 261 ... Instead of: Waypoint_Address Waypoint_ID Main street 363 Main street 1492 South Street 391 Narrow Lane 47 South Street 261 Assuming that is what you mean; I guess it makes sense, and makes for a normalised structure; right? Though I am not sure how I would ge the query to resolve my original question, but I guess the best thing is to do what you say, and cross that bridge when I come to it... bearing in mind I want the query to update the other various 'Runs' where the waypoint appears in terms of creating copies |
#30
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Hi Eric,
The point that David is making is that you need a way to relate all your data. Your structures and relationships need some re-thinking. David's idea to base the data on Latitude and Longitude is good -- you need a way to know WHICH intersections to apply to another road segment and using Lat/Long would do that for you. You would need, of course, to enter Lat/Long for each intersection. The reason your question cannot be answered is because you are currently only considering the endpoints and have no good way to associate your data. You can use an artificial method, like having the user enter an order but, this will turn out to be a lot more effort when lat/long can be used... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: David, GetRounds and GetRoundsDetails were originally associated with tbl_Runs. I unhooked them when I was cutting down the number of records for a smaller filesize in order to email the mdb; I simply forgot to re-synch them back to the main table before I sent the file. I am not sure what you mean by "I would convert into latitude/longitude based". however, with regard to Lat and Lon fields, please don't let them obscure the main issue, which is to synch assciated GetRounds with individual waypoints across more than one Run. regards Eric "David Glienna (MVP)" wrote: I saw the old file, and have these comments: I opened the table in Access 2007 and used the visual designer to see the relationships. GetRounds and GetRoundsDetails aren't associated with any tables Points, Runs, Master, and Waypoints are linked Road Restrictions and _Details are linked Too many unlinked tables. I would convert into latitude/longitude based. "efandango" wrote: Hello Crystal, I have sent you a new file, with an email message. I forgot to mention in my last email, that I have not actually recieved any emails from yourself, i checked to see if any were blocked, and nothing seems to be wrong at this end. regards Eric "strive4peace" wrote: Hi Eric, "how far into the life of the db I am; the answer is all the way, to the end" I was referring to its use, not development While I can see you have put quite a bit of time into developing it, there is only so far you can go with duct tape and bailing wire smile Please understand that designing tables is an iterative process; even the best of us have to redo work; it is better to make your structure strong now than face problems later ~~ both of the examples you posted are the same ... can you post the alternative? I notice that the Waypoints table has duplicate entries, which is why I suggested a Roads table that would have distinct names. It would not take as much time as you think to convert to using IDs instead of text. If, for instance, you have a combobox where the first column (ID) is hidden and the text shows, it is easy to add that new text entry, for instance, to the Roads table and pull the new ID on your form, here is an example with the properties you need to set for a combobox that stores the ID and displays the text combobox control Name -- RoadID ControlSource -- RoadID RowSource -- SELECT RoadID, RoadName FROM Roads ORDER BY RoadName BoundColumn -- 1 ColumnCount -- 2 columnWidths -- 0;2 (etc for however many columns you have -- the ID column will be hidden since its width is zero) ListWidth -- 2 (should add up to the sum of the column widths) RoadID will be stored in the form RecordSource while showing you information from another table... for the NotInList event of the combobox, here is code behind the form: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub RoadID_NotInList( _ NewData As String, _ Response As Integer) 'assumption: 'and its first column (hidden) 'is the Autonumber record ID for the source table Dim s As String _ , mRecordID As Long _ , mText As String '~~~~~~~~~~~~~~~~~~~~~~~~ 'Choose ONE of these code blocks '-------------------------------------------------------- 'if you want to convert to ProperCase 'mText = StrConv(NewData, vbProperCase) 's = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & mText & "';" '--------------- 'or, if you wish to leave it as the user entered... s = "INSERT INTO Roads (RoadName) " _ & " SELECT '" & NewData & "';" '-------------------------------------------------------- 'comment or remove next line after this works correctly Debug.Print s CurrentDb.Execute s CurrentDb.TableDefs.Refresh DoEvents mRecordID = Nz(DMax("RoadID", "Roads")) If mRecordID 0 Then Response = acDataErrAdded 'assuming the first column of the listbox 'is the RecordID, RecordID and is a Long Integer me.RoadID = mRecordID Else Response = acDataErrContinue End If End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE - control Name for RoadID is also RoadID ~~~~~~~~~~~~~~` ok, so we got that ... right? and now you are probably wondering how you are going to convert your data ... easy! Make a copy of your database so you have no fear about messing it up (if that is what happens) First, make the Roads table from your names: SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName INTO tbl_Roads FROM tbl_Waypoints WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null)); now, we will modify the Roads table to add: - RoadID, autonumber and we will also add a unique index: field -- RoadName Indexed -- Yes (No Duplicates) Descirption -- Name of Road ~~~ now, with the unique index, we can add the road names used from other tables without fear of duplicating what is already there... INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_To FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_To) Is Not Null)); INSERT INTO tbl_Roads ( RoadName ) SELECT DISTINCT tbl_Road_Restrictions.Road_Name_From FROM tbl_Road_Restrictions WHERE (((tbl_Road_Restrictions.Road_Name_From) Is Not Null)); when you run each of these queries, Access will probably tell you it cannot append all records -- and that is fine, the problem is that some of them are already there -- your unique index on RoadName is protecting you smile Now, add the following fields to tbl_Road_Restrictions - RoadID_to, Number, Field Size -- Long, Default Value -- null, Description -- Road Name To - RoadID_from, number, Field Size -- Long, Default Value -- null, Description -- Road Name From ok, now we just have to update the ID fields... UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_To = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_To = [tbl_Roads].[RoadID]; UPDATE tbl_Road_Restrictions INNER JOIN tbl_Roads ON tbl_Road_Restrictions.Road_Name_From = tbl_Roads.RoadName SET tbl_Road_Restrictions.RoadID_From = [tbl_Roads].[RoadID]; verify that IDs are filled wherever you have road names in tbl_Road_Restrictions ... then you can delete the following fields: Road_Name_From Road_Name_to ~~ use this same method to change Roadnames to IDs in your other tables each place on a form where you have a textbox for the RoadName, change it to a combobox (right-click, changeTo -- combo) and set the properties I specified above and put code on the NotInList event ~~~~~~~~~` once your data is using IDs and not names, then send me your db again and I will guide you for creating the cross-reference table for specifying the restrictions I could do this for you but that would not teach you anything. While you may be looking at this as a looming task, it should take no more than a couple hours... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.accessmvp.com/Strive4Peace/Index.htm * (: have an awesome day * efandango wrote: "strive4peace" wrote: hi Eric, now, to your question: "What I want to do is have the update process say, this Waypoint also exists elsewhere in the database, therefore a copy of the relevant road restriction and the related sub record set for the details will be made and assigned to those duplicates wherever they appear." It would be more accurate to make comparisons if you were storing IDs rather than names (I know you said you do not want to make these changes, but consider the life of the database and how far into that life you are right now...) I am guessing that the Road_restrictions_Detail lists the Waypoint where the restriction applies in the Road Restrictions table... consider this: If you had a Roads table with RoadID ... then the Road_restrictions_Detail table would, in essence, be a cross-reference Hi Crystal, For now, I would like to deal with the main question in hand, and come back on the various points you made about structure, coding, etc. so what you are saying is have the best thing to do is have the various waypoints as individual entities, like: Waypoint_Address Waypoint_ID Main street 363 Main street 363 South Street 261 Narrow Lane 47 South Street 261 ... Instead of: Waypoint_Address Waypoint_ID Main street 363 Main street 1492 South Street 391 Narrow Lane 47 South Street 261 Assuming that is what you mean; I guess it makes sense, and makes for a normalised structure; right? Though I am not sure how I would ge the query to resolve my original question, but I guess the best thing is to do what you say, and cross that bridge when I come to it... bearing in mind I want the query to update the other various 'Runs' where the waypoint appears in terms of creating copies |
Thread Tools | |
Display Modes | |
|
|