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

hi Eric,

First I will discuss some of your structure issues. For the benefit of
those who did not see your database, here are your currently defined
tables and fields:

tbl_Getround_Detail
==========================
0 GetRound_Detail_ID, 4 (Long), 4
1 GetRound_ID, 4 (Long), 4
2 Run_Direction, 10 (Text), 255
3 Run_waypoint, 10 (Text), 255
4 Postcode, 10 (Text), 8
5 Lat, 10 (Text), 30
6 Notmapped, 1 (Boolean), 1
7 Run_No, 4 (Long), 4

tbl_Getrounds
==========================
0 GetRound_ID, 4 (Long), 4
1 Run_No, 4 (Long), 4
2 FromGetRound, 10 (Text), 255
3 ToGetRound, 10 (Text), 255
4 From_PostCode, 10 (Text), 8
5 To_PostCode, 10 (Text), 8
6 Reason, 10 (Text), 255
7 FromStreetNameID, 4 (Long), 4
8 ToStreetNameID, 4 (Long), 4
9 Direction_From, 10 (Text), 255
10 Direction_To, 10 (Text), 255
11 GetRoundPoint, 10 (Text), 255
12 GetRoundPoint_ID, 10 (Text), 255
13 GetRound_Note, 10 (Text), 255
14 GetRound_SetDown, 10 (Text), 50

tbl_Points
==========================
0 Run_No, 4 (Long), 4
1 Point_ID, 4 (Long), 4
2 OrderSeq, 4 (Long), 4
3 Run_point_Venue, 10 (Text), 255
4 Run_point_Address, 10 (Text), 255
5 Run_Point_Postcode, 10 (Text), 9
6 Getround_Flag, 1 (Boolean), 1
7 TrafficSign, 10 (Text), 6
8 Restriction, 10 (Text), 10

tbl_Road_Restrictions
==========================
0 Road_Restriction_ID, 4 (Long), 4
1 Road_Name_From, 10 (Text), 255
2 Postcode_From, 10 (Text), 20
3 Road_Name_To, 10 (Text), 255
4 Postcode_To, 10 (Text), 20
5 Restriction, 10 (Text), 255
6 Run_No, 4 (Long), 4
7 Direction_From, 10 (Text), 255
8 Direction_To, 10 (Text), 255
9 Junction_ID, 4 (Long), 4

tbl_Road_Restrictions_Detail
==========================
0 Road_Restriction_Detail_ID, 4 (Long), 4
1 Road_Restriction_ID, 4 (Long), 4
2 Run_Direction, 10 (Text), 255
3 Run_waypoint, 10 (Text), 255
4 Postcode, 10 (Text), 8
5 Lat, 10 (Text), 30
6 Notmapped, 1 (Boolean), 1
7 Run_No, 4 (Long), 4

tbl_Runs
==========================
0 Run_No, 4 (Long), 4
1 RUN_No_ID, 4 (Long), 4
2 Crosstown_Area, 10 (Text), 30
3 Run_From, 10 (Text), 255
4 Run_From_Postcode, 10 (Text), 12
5 Run_To, 10 (Text), 50
6 Run_To_Postcode, 10 (Text), 12
7 Start_Note, 12 (Memo), 0
8 End_Note, 12 (Memo), 0
9 Date_1st_Visit, 8 (Date), 8
10 Date_2nd_Visit, 8 (Date), 8
17 textbox, 4 (Long), 4

tbl_Waypoints
==========================
0 Run_waypoint_ID, 4 (Long), 4
1 Run_No, 4 (Long), 4
2 Crosstown_Area, 10 (Text), 30
3 Run_Direction, 10 (Text), 20
4 Run_waypoint, 10 (Text), 255
5 Postcode, 10 (Text), 8
6 Lat, 10 (Text), 50
7 Notmapped, 1 (Boolean), 1
8 OrderSeq, 4 (Long), 4
9 Restriction_Flag, 1 (Boolean), 1

this list was obtained from code, ShowFields, written by Duane Hookom
and modified slightly by me
~~~

Relationship diagram:
make sure to set up all the relationships, add Getrounds,
Getround_detail, and Waypoints to the relationship diagram and make the
relationships

In order to enforce RI (Referential Integrity) between Runs and
Road_Restrictions, you will need to either:
1. add the following Run_No to Runs
OR
2. change the Run_No in tbl_Road_Restrictions

1
6
8
11
12
17
20
23
26
45
52
61
65
67
74
77
79
84
89
94
119
120
130
137
164
172
184
212
269

fill out your field descriptions! These are used for the StatusBar text
when you build forms

I emailed you analyzer reports showing:

Table Summary, sorted by table name
- Table Name
- Table Description
- whether it is linked
- Number of Fields
- Number of Records
- Date Modified
- Number of Indexes

Field List, sorted by Field Name and then by Table Name
- Field Name
- Table Name
- Data Type
- Field Size
- # Values Filled
- # Unique Values
- Field Description

Deep Analysis: Data Dictionary with Value Analysis,
sorted by Table Name and then Field position
~~~~~~~~~~~~~~~~~~~~~~~~
- Table Name
- Table Description
- Number of Fields
- Number of Records
- Date Created
- Date Modified
~~~~~~~~~~~~~~~~~~~~~~~~
- Field Position
- Field Name
- Field Description
- Autonumber?
- Data Type
- Field Size
- Maximum Length used (text fields)
- Indexes -- Primary Key, Required, Unique, Foreign Key
- # Values Filled
- # Unique Values
- % Filled
- % Unique
- Default Value
- Minimum Value
- Maximum Value

If you cannot render files in SNP (snapshot) format, here is a link to
the Microsoft site to download the SNAPSHOT viewer for Access:
http://www.microsoft.com/downloads/d...displaylang=en

I also emailed you a ppt slide showing how to interpret the Deep
Analysis report since I have not wasted space on the report to show what
each column is.

Several of your text fields are defined to be 255 characters, which
happens when you start a table by importing data. The analyzer reports
show how many characters you have actually used. for instance, your
Points table has 216 records; Run_point_Venue, which is always filled,
is defined to be 255 yet the most characters ever used is 35.

in Waypoints, technically, your Lat field is LatLong; I would suggest
separating these into 2 fields. Long is a reserved word so you could
use Lat and Lng for your fieldnames; also, the field size is not
consistent in your different tables (refer to the Fieldlist Analyzer report)

also, some of your field values for Lat need to be adjusted such as:

geotagged geo:lat=51.459435 ge

I am assuming that you imported data and the rest of the entry was chopped

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

you didn't compile your database, did you? g ... I had to comment code
to get it to compile

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


looks like you started frm_points by copying another form and you
neglected to remove the code behind the form that was no longer applicable

frm_Runs also had issues

~~

I think you are making a mistake by storing names instead of IDs for
Road_Name_To
Road_Name_From

you should have a table for Roadnames with autonumber RoadID and store
Long Integer RoadID (default value -- null) in related tables. I
notice multiple names in the Waypoints table for Run_waypoint; these
should also be RoadID

you could use a combobox and the NotInList event to update the Roads
table with new values
~~~

why are you storing HTML tags in your Start_Note and End_Note fields ...
you can concatenate them when needed...

If you make multiple visits for each run, the date of the visit should
be stored in a related table rather than creating Date_1st_Visit,
Date_2nd_Visit, ...

I see you have used captions in your table designs; personally, I think
this is a bad idea; users should not open a table directly and when
developers open the table, they should see the real field names. You
can always change captions when you design forms. Generally, each table
will have just one form/subform per table and that is a better place to
make your changes.

In Waypoints, your Waypoint name is uppercase, to convert these to mixed
case, you can run the following update query:

UPDATE tbl_Waypoints SET tbl_Waypoints.Run_waypoint =
StrConv([Run_waypoint],3);

you can also use this event procedure on the control AfterUpdate event:

if isnull(me.activecontrol) then exit sub
me.activecontrol = StrConv(me.activecontrol,3)

~~~~~~~~~~~~~~~~~~~~~~~~~~~`

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
table between Roads and Road Restrictions. There would be no need to
duplicate records.

also, just another guess, but shouldn't the Road_Restrictions table have
some kind of date fields ... like DateFrom and DateTo -- if DateTo is
filled then it is no longer applicable?



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 cut down version of the file. Please let me know when you
have it.

btw, where are you based (generally)?

I'm in the UK.



"strive4peace" wrote:

Hi Eric,

that file size is fine --

1. compact/repair database
2. zip it up

yes, I am watching this thread ~~

Warm Regards,
Crystal

*
(: have an awesome day
*


efandango wrote:
Crystal,

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

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

regards

Eric


"strive4peace" wrote:

Hi Eric,

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

strive4peace2006 at yahoo.com
subject = from Eric

specify the name of the form/subform to look at

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

Warm Regards,
Crystal

*
(: have an awesome day
*


efandango wrote:
Hi Crystal,

A Waypoint is a Road Name.

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

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

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

Run List 39:

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

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

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

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

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

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

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

regards

Eric





"strive4peace" wrote:

Hi efandango (what is your name?)

can you please define some of your terminology? for instance, what is a
Waypoint? Run_waypoint?

Road_Name_From
Road_Name_To

seem like you should have a table:

Roads
- RoadID, autonumber
- Road_Name, text

and you should be storing IDs in related tables:

RoadID_From, long integer, defaultvalue -- null
RoadID_To, long integer, defaultvalue -- null

Warm Regards,
Crystal

*
(: have an awesome day
*


efandango wrote:
To some of you experts, it may be small change… but, To me this problem is
complex and is beginning to hurt… I have tried various methods of doing
complex updates using mainly queries and a bit of form code, but just can’t
make the breakthrough required to bring it all together, so If someone can
help by getting their teeth into this problem with me, I would be very
grateful.

The situation:
I have a large set of master records ‘tbl_Waypoints’ and some of the field
records [Run_waypoint] have a related sub record in ‘tbl_Road_Restrictions’,
which has its own set of Sub Records in ‘tbl_Road_Restrictions_Detail’.

The problem:
Each time a set of sub records are created, a flag [Restriction_Flag] is
automatically adjusted to say ‘Yes’ from a default of ‘No’ and changes color
to blue on the form**. This indicates that a particular waypoint now has a
subroute associated to it. However, whenever a user creates a new subset of
records for a given Waypoint, that particular Waypoint could also exist on
another set of master records elesewhere in the database and woud therefore
require an identical set of sub records assigned to it/them.

**this process is done via a function that say’s if any one of
‘tbl_Road_Restrictions.Road_Name_From’ or ‘tbl_Road_Restrictions
.Road_Name_To’ exists in ‘tbl_Waypoint.Run_waypoint’ then mark the flag.

The process:
Assuming a master ‘tbl_Waypoints’ record has [Restriction_Flag] marked as
Yes (-1) and a duplicate field exists in one master record set (unflagged),
then find the other duplicates in the other master record sets, and do the
following: Mark the master records [Restriction_Flag] as Yes (-1) and then
create a copy of the (original) related sub record (and its related sub
records), and then assign them to the other master records.

How would I go about achieving this process as an automatic process/query. I
have never done anyhting like this, and my sql skills are not upto the job,
can someone please help me?


These are the 4 tables involved, and their fields, relationships). There are
other fields involved but I don’t think they are required for this right now,
so for the sake of clarity, I will leave them out for now.

tbl_Runs Overall Master Records (1 record instance of each)
[Run_No] (pk, number)

tbl_Waypoints Master Records
[Run_Waypoint_ID] (pk, number)
[Run_No] (number) (links to above)
[Restriction_Flag] (yes/no)
[Run_waypoint] (txt)
[Postcode]

tbl_Road_Restrictions Sub Master II Records (1 record instance of each)
[Road_Restriction_ID] (pk, number)
[Run_No] (Links to overall master)
[Road_Name_From] (txt)
[Postcode_From] (txt)
[Road_Name_To] (txt)
[Postcode_To] (txt)

tbl_Road_Restrictions_Detail Numerous records
[Road_Restrictions_Detail_ID] (pk, number)
[Road_Restriction_ID] (links to above)
[Run_waypoint] (txt)
[Postcode] (txt)

Crystal,


Wow, what a response!... I have only speed-read your reply; but I will read
it in depth and come back to you in time.

kind regards

Eric




"strive4peace" wrote:

hi Eric,

First I will discuss some of your structure issues. For the benefit of
those who did not see your database, here are your currently defined
tables and fields:

tbl_Getround_Detail
==========================
0 GetRound_Detail_ID, 4 (Long), 4
1 GetRound_ID, 4 (Long), 4
2 Run_Direction, 10 (Text), 255
3 Run_waypoint, 10 (Text), 255
4 Postcode, 10 (Text), 8
5 Lat, 10 (Text), 30
6 Notmapped, 1 (Boolean), 1
7 Run_No, 4 (Long), 4

tbl_Getrounds
==========================
0 GetRound_ID, 4 (Long), 4
1 Run_No, 4 (Long), 4
2 FromGetRound, 10 (Text), 255
3 ToGetRound, 10 (Text), 255
4 From_PostCode, 10 (Text), 8
5 To_PostCode, 10 (Text), 8
6 Reason, 10 (Text), 255
7 FromStreetNameID, 4 (Long), 4
8 ToStreetNameID, 4 (Long), 4
9 Direction_From, 10 (Text), 255
10 Direction_To, 10 (Text), 255
11 GetRoundPoint, 10 (Text), 255
12 GetRoundPoint_ID, 10 (Text), 255
13 GetRound_Note, 10 (Text), 255
14 GetRound_SetDown, 10 (Text), 50

tbl_Points
==========================
0 Run_No, 4 (Long), 4
1 Point_ID, 4 (Long), 4
2 OrderSeq, 4 (Long), 4
3 Run_point_Venue, 10 (Text), 255
4 Run_point_Address, 10 (Text), 255
5 Run_Point_Postcode, 10 (Text), 9
6 Getround_Flag, 1 (Boolean), 1
7 TrafficSign, 10 (Text), 6
8 Restriction, 10 (Text), 10

tbl_Road_Restrictions
==========================
0 Road_Restriction_ID, 4 (Long), 4
1 Road_Name_From, 10 (Text), 255
2 Postcode_From, 10 (Text), 20
3 Road_Name_To, 10 (Text), 255
4 Postcode_To, 10 (Text), 20
5 Restriction, 10 (Text), 255
6 Run_No, 4 (Long), 4
7 Direction_From, 10 (Text), 255
8 Direction_To, 10 (Text), 255
9 Junction_ID, 4 (Long), 4

tbl_Road_Restrictions_Detail
==========================
0 Road_Restriction_Detail_ID, 4 (Long), 4
1 Road_Restriction_ID, 4 (Long), 4
2 Run_Direction, 10 (Text), 255
3 Run_waypoint, 10 (Text), 255
4 Postcode, 10 (Text), 8
5 Lat, 10 (Text), 30
6 Notmapped, 1 (Boolean), 1
7 Run_No, 4 (Long), 4

tbl_Runs
==========================
0 Run_No, 4 (Long), 4
1 RUN_No_ID, 4 (Long), 4
2 Crosstown_Area, 10 (Text), 30
3 Run_From, 10 (Text), 255
4 Run_From_Postcode, 10 (Text), 12
5 Run_To, 10 (Text), 50
6 Run_To_Postcode, 10 (Text), 12
7 Start_Note, 12 (Memo), 0
8 End_Note, 12 (Memo), 0
9 Date_1st_Visit, 8 (Date), 8
10 Date_2nd_Visit, 8 (Date), 8
17 textbox, 4 (Long), 4

tbl_Waypoints
==========================
0 Run_waypoint_ID, 4 (Long), 4
1 Run_No, 4 (Long), 4
2 Crosstown_Area, 10 (Text), 30
3 Run_Direction, 10 (Text), 20
4 Run_waypoint, 10 (Text), 255
5 Postcode, 10 (Text), 8
6 Lat, 10 (Text), 50
7 Notmapped, 1 (Boolean), 1
8 OrderSeq, 4 (Long), 4
9 Restriction_Flag, 1 (Boolean), 1

this list was obtained from code, ShowFields, written by Duane Hookom
and modified slightly by me
~~~

Relationship diagram:
make sure to set up all the relationships, add Getrounds,
Getround_detail, and Waypoints to the relationship diagram and make the
relationships

In order to enforce RI (Referential Integrity) between Runs and
Road_Restrictions, you will need to either:
1. add the following Run_No to Runs
OR
2. change the Run_No in tbl_Road_Restrictions

1
6
8
11
12
17
20
23
26
45
52
61
65
67
74
77
79
84
89
94
119
120
130
137
164
172
184
212
269

fill out your field descriptions! These are used for the StatusBar text
when you build forms

I emailed you analyzer reports showing:

Table Summary, sorted by table name
- Table Name
- Table Description
- whether it is linked
- Number of Fields
- Number of Records
- Date Modified
- Number of Indexes

Field List, sorted by Field Name and then by Table Name
- Field Name
- Table Name
- Data Type
- Field Size
- # Values Filled
- # Unique Values
- Field Description

Deep Analysis: Data Dictionary with Value Analysis,
sorted by Table Name and then Field position
~~~~~~~~~~~~~~~~~~~~~~~~
- Table Name
- Table Description
- Number of Fields
- Number of Records
- Date Created
- Date Modified
~~~~~~~~~~~~~~~~~~~~~~~~
- Field Position
- Field Name
- Field Description
- Autonumber?
- Data Type
- Field Size
- Maximum Length used (text fields)
- Indexes -- Primary Key, Required, Unique, Foreign Key
- # Values Filled
- # Unique Values
- % Filled
- % Unique
- Default Value
- Minimum Value
- Maximum Value

If you cannot render files in SNP (snapshot) format, here is a link to
the Microsoft site to download the SNAPSHOT viewer for Access:
http://www.microsoft.com/downloads/d...displaylang=en

I also emailed you a ppt slide showing how to interpret the Deep
Analysis report since I have not wasted space on the report to show what
each column is.

Several of your text fields are defined to be 255 characters, which
happens when you start a table by importing data. The analyzer reports
show how many characters you have actually used. for instance, your
Points table has 216 records; Run_point_Venue, which is always filled,
is defined to be 255 yet the most characters ever used is 35.

in Waypoints, technically, your Lat field is LatLong; I would suggest
separating these into 2 fields. Long is a reserved word so you could
use Lat and Lng for your fieldnames; also, the field size is not
consistent in your different tables (refer to the Fieldlist Analyzer report)

also, some of your field values for Lat need to be adjusted such as:

geotagged geo:lat=51.459435 ge

I am assuming that you imported data and the rest of the entry was chopped

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

you didn't compile your database, did you? g ... I had to comment code
to get it to compile

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


looks like you started frm_points by copying another form and you
neglected to remove the code behind the form that was no longer applicable

frm_Runs also had issues

~~

I think you are making a mistake by storing names instead of IDs for
Road_Name_To
Road_Name_From

you should have a table for Roadnames with autonumber RoadID and store
Long Integer RoadID (default value -- null) in related tables. I
notice multiple names in the Waypoints table for Run_waypoint; these
should also be RoadID

you could use a combobox and the NotInList event to update the Roads
table with new values
~~~

why are you storing HTML tags in your Start_Note and End_Note fields ...
you can concatenate them when needed...

If you make multiple visits for each run, the date of the visit should
be stored in a related table rather than creating Date_1st_Visit,
Date_2nd_Visit, ...

I see you have used captions in your table designs; personally, I think
this is a bad idea; users should not open a table directly and when
developers open the table, they should see the real field names. You
can always change captions when you design forms. Generally, each table
will have just one form/subform per table and that is a better place to
make your changes.

In Waypoints, your Waypoint name is uppercase, to convert these to mixed
case, you can run the following update query:

UPDATE tbl_Waypoints SET tbl_Waypoints.Run_waypoint =
StrConv([Run_waypoint],3);

you can also use this event procedure on the control AfterUpdate event:

if isnull(me.activecontrol) then exit sub
me.activecontrol = StrConv(me.activecontrol,3)

~~~~~~~~~~~~~~~~~~~~~~~~~~~`

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

you're welcome, Eric

once you have digested all of this (and please also read the Access
Basics document I have a link for in my siggy), let us know if you need
more 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:
Crystal,


Wow, what a response!... I have only speed-read your reply; but I will read
it in depth and come back to you in time.

kind regards

Eric




"strive4peace" wrote:

hi Eric,

First I will discuss some of your structure issues. For the benefit of
those who did not see your database, here are your currently defined
tables and fields:

tbl_Getround_Detail
==========================
0 GetRound_Detail_ID, 4 (Long), 4
1 GetRound_ID, 4 (Long), 4
2 Run_Direction, 10 (Text), 255
3 Run_waypoint, 10 (Text), 255
4 Postcode, 10 (Text), 8
5 Lat, 10 (Text), 30
6 Notmapped, 1 (Boolean), 1
7 Run_No, 4 (Long), 4

tbl_Getrounds
==========================
0 GetRound_ID, 4 (Long), 4
1 Run_No, 4 (Long), 4
2 FromGetRound, 10 (Text), 255
3 ToGetRound, 10 (Text), 255
4 From_PostCode, 10 (Text), 8
5 To_PostCode, 10 (Text), 8
6 Reason, 10 (Text), 255
7 FromStreetNameID, 4 (Long), 4
8 ToStreetNameID, 4 (Long), 4
9 Direction_From, 10 (Text), 255
10 Direction_To, 10 (Text), 255
11 GetRoundPoint, 10 (Text), 255
12 GetRoundPoint_ID, 10 (Text), 255
13 GetRound_Note, 10 (Text), 255
14 GetRound_SetDown, 10 (Text), 50

tbl_Points
==========================
0 Run_No, 4 (Long), 4
1 Point_ID, 4 (Long), 4
2 OrderSeq, 4 (Long), 4
3 Run_point_Venue, 10 (Text), 255
4 Run_point_Address, 10 (Text), 255
5 Run_Point_Postcode, 10 (Text), 9
6 Getround_Flag, 1 (Boolean), 1
7 TrafficSign, 10 (Text), 6
8 Restriction, 10 (Text), 10

tbl_Road_Restrictions
==========================
0 Road_Restriction_ID, 4 (Long), 4
1 Road_Name_From, 10 (Text), 255
2 Postcode_From, 10 (Text), 20
3 Road_Name_To, 10 (Text), 255
4 Postcode_To, 10 (Text), 20
5 Restriction, 10 (Text), 255
6 Run_No, 4 (Long), 4
7 Direction_From, 10 (Text), 255
8 Direction_To, 10 (Text), 255
9 Junction_ID, 4 (Long), 4

tbl_Road_Restrictions_Detail
==========================
0 Road_Restriction_Detail_ID, 4 (Long), 4
1 Road_Restriction_ID, 4 (Long), 4
2 Run_Direction, 10 (Text), 255
3 Run_waypoint, 10 (Text), 255
4 Postcode, 10 (Text), 8
5 Lat, 10 (Text), 30
6 Notmapped, 1 (Boolean), 1
7 Run_No, 4 (Long), 4

tbl_Runs
==========================
0 Run_No, 4 (Long), 4
1 RUN_No_ID, 4 (Long), 4
2 Crosstown_Area, 10 (Text), 30
3 Run_From, 10 (Text), 255
4 Run_From_Postcode, 10 (Text), 12
5 Run_To, 10 (Text), 50
6 Run_To_Postcode, 10 (Text), 12
7 Start_Note, 12 (Memo), 0
8 End_Note, 12 (Memo), 0
9 Date_1st_Visit, 8 (Date), 8
10 Date_2nd_Visit, 8 (Date), 8
17 textbox, 4 (Long), 4

tbl_Waypoints
==========================
0 Run_waypoint_ID, 4 (Long), 4
1 Run_No, 4 (Long), 4
2 Crosstown_Area, 10 (Text), 30
3 Run_Direction, 10 (Text), 20
4 Run_waypoint, 10 (Text), 255
5 Postcode, 10 (Text), 8
6 Lat, 10 (Text), 50
7 Notmapped, 1 (Boolean), 1
8 OrderSeq, 4 (Long), 4
9 Restriction_Flag, 1 (Boolean), 1

this list was obtained from code, ShowFields, written by Duane Hookom
and modified slightly by me
~~~

Relationship diagram:
make sure to set up all the relationships, add Getrounds,
Getround_detail, and Waypoints to the relationship diagram and make the
relationships

In order to enforce RI (Referential Integrity) between Runs and
Road_Restrictions, you will need to either:
1. add the following Run_No to Runs
OR
2. change the Run_No in tbl_Road_Restrictions

1
6
8
11
12
17
20
23
26
45
52
61
65
67
74
77
79
84
89
94
119
120
130
137
164
172
184
212
269

fill out your field descriptions! These are used for the StatusBar text
when you build forms

I emailed you analyzer reports showing:

Table Summary, sorted by table name
- Table Name
- Table Description
- whether it is linked
- Number of Fields
- Number of Records
- Date Modified
- Number of Indexes

Field List, sorted by Field Name and then by Table Name
- Field Name
- Table Name
- Data Type
- Field Size
- # Values Filled
- # Unique Values
- Field Description

Deep Analysis: Data Dictionary with Value Analysis,
sorted by Table Name and then Field position
~~~~~~~~~~~~~~~~~~~~~~~~
- Table Name
- Table Description
- Number of Fields
- Number of Records
- Date Created
- Date Modified
~~~~~~~~~~~~~~~~~~~~~~~~
- Field Position
- Field Name
- Field Description
- Autonumber?
- Data Type
- Field Size
- Maximum Length used (text fields)
- Indexes -- Primary Key, Required, Unique, Foreign Key
- # Values Filled
- # Unique Values
- % Filled
- % Unique
- Default Value
- Minimum Value
- Maximum Value

If you cannot render files in SNP (snapshot) format, here is a link to
the Microsoft site to download the SNAPSHOT viewer for Access:
http://www.microsoft.com/downloads/d...displaylang=en

I also emailed you a ppt slide showing how to interpret the Deep
Analysis report since I have not wasted space on the report to show what
each column is.

Several of your text fields are defined to be 255 characters, which
happens when you start a table by importing data. The analyzer reports
show how many characters you have actually used. for instance, your
Points table has 216 records; Run_point_Venue, which is always filled,
is defined to be 255 yet the most characters ever used is 35.

in Waypoints, technically, your Lat field is LatLong; I would suggest
separating these into 2 fields. Long is a reserved word so you could
use Lat and Lng for your fieldnames; also, the field size is not
consistent in your different tables (refer to the Fieldlist Analyzer report)

also, some of your field values for Lat need to be adjusted such as:

geotagged geo:lat=51.459435 ge

I am assuming that you imported data and the rest of the entry was chopped

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

you didn't compile your database, did you? g ... I had to comment code
to get it to compile

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


looks like you started frm_points by copying another form and you
neglected to remove the code behind the form that was no longer applicable

frm_Runs also had issues

~~

I think you are making a mistake by storing names instead of IDs for
Road_Name_To
Road_Name_From

you should have a table for Roadnames with autonumber RoadID and store
Long Integer RoadID (default value -- null) in related tables. I
notice multiple names in the Waypoints table for Run_waypoint; these
should also be RoadID

you could use a combobox and the NotInList event to update the Roads
table with new values
~~~

why are you storing HTML tags in your Start_Note and End_Note fields ...
you can concatenate them when needed...

If you make multiple visits for each run, the date of the visit should
be stored in a related table rather than creating Date_1st_Visit,
Date_2nd_Visit, ...

I see you have used captions in your table designs; personally, I think
this is a bad idea; users should not open a table directly and when
developers open the table, they should see the real field names. You
can always change captions when you design forms. Generally, each table
will have just one form/subform per table and that is a better place to
make your changes.

In Waypoints, your Waypoint name is uppercase, to convert these to mixed
case, you can run the following update query:

UPDATE tbl_Waypoints SET tbl_Waypoints.Run_waypoint =
StrConv([Run_waypoint],3);

you can also use this event procedure on the control AfterUpdate event:

if isnull(me.activecontrol) then exit sub
me.activecontrol = StrConv(me.activecontrol,3)

~~~~~~~~~~~~~~~~~~~~~~~~~~~`

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

"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.

regards

Eric

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.

regards

Eric

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.

regards

Eric

Hi Eric,

got the file -- will have to make some time to look, please have
patience, I'll get to it smile


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?

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.

regards

Eric

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?

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.

regards

Eric

Hello Crystal,

Please take as long as you need, I don't want to give the impression that I
am trying to rush you. I appreciate your time. I mentioned no previous
emails, because you mentioned in an earlier email that you send a ppt file,
which I didn't actually receive.

kind regards

Eric


"strive4peace" wrote:

Hi Eric,

got the file -- will have to make some time to look, please have
patience, I'll get to it smile


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?

Hi Eric,

just sent the ppt to you -- I will look at your db sooner if you convert
it (click on the big round Office button in the top left corner of the
screen); I consult and all of my current work is with 2003, so that is
what I am booted into most of the time...

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,

Please take as long as you need, I don't want to give the impression that I
am trying to rush you. I appreciate your time. I mentioned no previous
emails, because you mentioned in an earlier email that you send a ppt file,
which I didn't actually receive.

kind regards

Eric


"strive4peace" wrote:

Hi Eric,

got the file -- will have to make some time to look, please have
patience, I'll get to it smile


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?