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 |
#31
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Hi Crystal,
I understand the point(s) David was making, which is why i pointed out that the unlinking of some tables was simply an error caused by my haste to post a cut down version to yourself. But now we seem to have digressed into an area involving geocode references, but as i suggested, they are not entirely relevant to this particular application because it is essential to the application that users enter the data, with very little in the way of 'intelligent interpretation' such as cross referencing geocodes, and the like. It is fundamental to this databases' usage that users enter data based on fact finding as they go along each route. This is not an application where for example, the database provides the information from a read only point of view, it is really the opposite where users have to gather and intepret the data , and only then can they enter it. So, as I mentioned, there really is no need to obscure the main issue, with a geocode cross-reference engine which is simply not required. What is required is the ability to synch associated GetRounds with repeating waypoints across more than one Run. "strive4peace" wrote: 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 |
#32
|
|||
|
|||
How can I Automate a complex update process involving 3-4 tabl
Hi Eric,
I have spent some time with your database -- now I see that I did not gather from your explanations exactly what you are trying to do ... I am not ready to answer your question yet, need to still spend more time... Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * efandango wrote: Hi Crystal, I understand the point(s) David was making, which is why i pointed out that the unlinking of some tables was simply an error caused by my haste to post a cut down version to yourself. But now we seem to have digressed into an area involving geocode references, but as i suggested, they are not entirely relevant to this particular application because it is essential to the application that users enter the data, with very little in the way of 'intelligent interpretation' such as cross referencing geocodes, and the like. It is fundamental to this databases' usage that users enter data based on fact finding as they go along each route. This is not an application where for example, the database provides the information from a read only point of view, it is really the opposite where users have to gather and intepret the data , and only then can they enter it. So, as I mentioned, there really is no need to obscure the main issue, with a geocode cross-reference engine which is simply not required. What is required is the ability to synch associated GetRounds with repeating waypoints across more than one Run. "strive4peace" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|