One To Many Relationships - Series of Related Tables

[iain]

I am slightly confused regarding the viability of relating too may tables
together in series using One to Many relationships for each relationship.

e.g. tblCountry linked to tblState, then tblState linked to tblRegion and
tblRegion linked to tblPlace.

Although this is possible, is it wise to do this sort of thing?
What are the pros and cons?

Any clarification on this would be gratefully recieved.

Iain

 

[Philip Herlihy]

I am slightly confused regarding the viability of relating too may tables
together in series using One to Many relationships for each relationship.

e.g. tblCountry linked to tblState, then tblState linked to tblRegion and
tblRegion linked to tblPlace.

Although this is possible, is it wise to do this sort of thing?
What are the pros and cons?

Any clarification on this would be gratefully recieved.

Iain

It's not about whether it makes sense for the database - it's about
whether it makes sense for the data. I'm unaware of any limit on how
many one-to-many relationships you might "cascade" in this way.
However, every real-world situation that you attempt to model has its
own character. In the example you give, does every Place (without
exception) have a Region?

If you have a strict hierarchy (for example the various assemblies in an
aircraft) then you could have a very large sequence of one-to-many
relationships (or many-to-many in the case of multi-use parts).
Geography is often full of pitfalls, though. So many times an online
form asks for City plus County. I live in London - doesn't apply. The
official address of my old college had no street name - just the college
name, city and postcode, which made ordering stuff difficult at times.

So, I don't think there's a generalisable rule about this, except to say
that if the data does call for yet another related table, there's no
reason not to add one, and you may make life difficult for yourself if
you don't.

Phil, London

 

[iain]

Thank you Phil,

The tables I listed were purely ficticious to illustrate what I meant by
tables "in series" but to pick up on the point you made regarding the
pitfalls of geographic regions, each Place would have a Region and each
Region a State etc.

Assuming that whatever contents each table were to represent, then this type
of construction is both sound and permissable. Correct?

Following on from this, if I wished to auto populate Country, State and
Region by selecting a Place or if I wished to add a new Place and choose
which Region it belonged to, is this sort of functionality feasible with the
sort of construct I'm talking about.

Iain

 

[KenSheridan via AccessMonster.com]

Iain:

Your model is spot on. For data entry, say you have a form based on an
Events table, and each event takes place in a specific place, the events
table need only include the SpaceID foreign key column. This is a numeric
value referencing the numeric (e.g. autonumber) primary key column of Spaces.
Using a 'surrogate' numeric key caters for more than one place having the
same name.

To show the region, state and country for the selected place in the form you
have several options:

1. Base the form on a query which joins the required tables, events to
places, places to regions and so on. Bind controls to the region, state and
country columns from those tables. Use a bound combo box for the PlaceID
column and set it up to show the place name like so:

ControlSource: PlaceID

RowSource: SELECT PlaceID, Place FROM Places ORDER BY Place;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

when you select an item in the combo box the relevant region, state etc will
show in the other columns.

2. Base the form on the Events table and use the same bound combo box and
use expressions calling the DLookup function as the ControlSource properties
of unbound text box controls to show the region, state etc, referencing the
bound text box in the DLookup function's criterion.

3. Base the form on the Events table and as the RowSource for the bound
combo box join the relevant tables and return the region, state etc in other
columns in the combo box's list. Then reference these columns in unbound
text boxes, e.g. for the region:

=[PlaceID].Column(2)

The Column property is zero-based, so Column(2) is the third column.

You'll find an example, amongst other approaches, of this last multi-column
combo box method in my demo file for handling this type of hierarchically
related entities at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


One thing about this type of data which is sometimes overlooked is that there
could be a missing link in the chain. I came across this when helping out
someone at the OECD with a database of members internationally. Some small
counties do not have regional structures, so there is no equivalent of State
or Region. Consequently the chain between Place and Country is broken. The
way to cater for this is, for each such country to include a row in the Sates
table with a value in the text column such as N/A and the relevant CountryID
in the foreign key column which references the key of the Countries table.
Similarly in the Regions table include a row with a value in the text column
such as N/A and a value in the StateID column which references the row in
States for that country with the N/A value. All rows in Places for that
country will have a foreign key RegionID value which references the N/A row
in Regions for that country. The chain is then unbroken.

When it comes to adding new data you can use a combo box's NotInList event
procedure to allow you to type in the new value and insert it into the
referenced table. Here's an example of the NotInList event procedure for a
combo box bound to a CityID column:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

This opens a frmCities form in dialogue mode and passes the new city name to
it for you to then enter other data, e.g. the County, which would again be
via a combo box in the frmCities form and that combo box would use code in
its NotInList event procedure for adding a new County, and so on up the line.
The Open event procedure of the frmCities form includes the following code:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

BTW, if you've looked at the sample Northwind database you'll have seen that
it does not have tables such as those described above, but simply has columns
in the Customers table for City, Region and Country. Just because it comes
with Access don't assume this is correct. It is of course not correctly
normalized and wide open to inconsistent data being entered.

Ken Sheridan
Stafford, England

Thank you Phil,

The tables I listed were purely ficticious to illustrate what I meant by
tables "in series" but to pick up on the point you made regarding the
pitfalls of geographic regions, each Place would have a Region and each
Region a State etc.

Assuming that whatever contents each table were to represent, then this type
of construction is both sound and permissable. Correct?

Following on from this, if I wished to auto populate Country, State and
Region by selecting a Place or if I wished to add a new Place and choose
which Region it belonged to, is this sort of functionality feasible with the
sort of construct I'm talking about.

Iain
[quoted text clipped - 30 lines]

Phil, London

 

[iain]

Thanks Ken,

I haven't reviewed the information on the link yet so appologies if the
answers are in there but just to make sure I am on the right track, the way I
thought this might work was as outlined in your option 1.

To keep using your model of the tables relating to an Event for simplicity,
my thinking was that query was required especially if the Event had other
related data such as an Activity, Date, Diary or Journal Memo, etc. so that
all information could be pulled together using one form.

This would require a further one to many relationship between tblPlace and
tblEvent. Correct?

The specific questions I am trying to answer are;

1 how can data be entered from this form using combo boxes when the same
Place name could apply to more than one Region. eg. Scotland, Western Isles,
Mull, Ben More and Scotland, Southern Highlands, Glen Dochart, Ben More.

2 tblPlace would contain 2 Ben More records and therefore how does one
select the correct one if choosing from a combo box?

3 can the fields be auto populated with data by entering a known Place name?

4 what happens if all 4 tables were to require a new record so that a
completely new location could be created?

Do your suggestions cater for this sort of functionallity and will I have to
investigate the use of subforms, or is this another thing alltogether?

Iain:

Your model is spot on. For data entry, say you have a form based on an
Events table, and each event takes place in a specific place, the events
table need only include the SpaceID foreign key column. This is a numeric
value referencing the numeric (e.g. autonumber) primary key column of Spaces.
Using a 'surrogate' numeric key caters for more than one place having the
same name.

To show the region, state and country for the selected place in the form you
have several options:

1. Base the form on a query which joins the required tables, events to
places, places to regions and so on. Bind controls to the region, state and
country columns from those tables. Use a bound combo box for the PlaceID
column and set it up to show the place name like so:

ControlSource: PlaceID

RowSource: SELECT PlaceID, Place FROM Places ORDER BY Place;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

when you select an item in the combo box the relevant region, state etc will
show in the other columns.

2. Base the form on the Events table and use the same bound combo box and
use expressions calling the DLookup function as the ControlSource properties
of unbound text box controls to show the region, state etc, referencing the
bound text box in the DLookup function's criterion.

3. Base the form on the Events table and as the RowSource for the bound
combo box join the relevant tables and return the region, state etc in other
columns in the combo box's list. Then reference these columns in unbound
text boxes, e.g. for the region:

=[PlaceID].Column(2)

The Column property is zero-based, so Column(2) is the third column.

You'll find an example, amongst other approaches, of this last multi-column
combo box method in my demo file for handling this type of hierarchically
related entities at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


One thing about this type of data which is sometimes overlooked is that there
could be a missing link in the chain. I came across this when helping out
someone at the OECD with a database of members internationally. Some small
counties do not have regional structures, so there is no equivalent of State
or Region. Consequently the chain between Place and Country is broken. The
way to cater for this is, for each such country to include a row in the Sates
table with a value in the text column such as N/A and the relevant CountryID
in the foreign key column which references the key of the Countries table.
Similarly in the Regions table include a row with a value in the text column
such as N/A and a value in the StateID column which references the row in
States for that country with the N/A value. All rows in Places for that
country will have a foreign key RegionID value which references the N/A row
in Regions for that country. The chain is then unbroken.

When it comes to adding new data you can use a combo box's NotInList event
procedure to allow you to type in the new value and insert it into the
referenced table. Here's an example of the NotInList event procedure for a
combo box bound to a CityID column:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

This opens a frmCities form in dialogue mode and passes the new city name to
it for you to then enter other data, e.g. the County, which would again be
via a combo box in the frmCities form and that combo box would use code in
its NotInList event procedure for adding a new County, and so on up the line.
The Open event procedure of the frmCities form includes the following code:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

BTW, if you've looked at the sample Northwind database you'll have seen that
it does not have tables such as those described above, but simply has columns
in the Customers table for City, Region and Country. Just because it comes
with Access don't assume this is correct. It is of course not correctly
normalized and wide open to inconsistent data being entered.

Ken Sheridan
Stafford, England

Thank you Phil,

The tables I listed were purely ficticious to illustrate what I meant by
tables "in series" but to pick up on the point you made regarding the
pitfalls of geographic regions, each Place would have a Region and each
Region a State etc.

Assuming that whatever contents each table were to represent, then this type
of construction is both sound and permissable. Correct?

Following on from this, if I wished to auto populate Country, State and
Region by selecting a Place or if I wished to add a new Place and choose
which Region it belonged to, is this sort of functionality feasible with the
sort of construct I'm talking about.

Iain

I am slightly confused regarding the viability of relating too may tables
together in series using One to Many relationships for each relationship.

[quoted text clipped - 30 lines]

Phil, London

 

[KenSheridan via AccessMonster.com]

Iain:

Firstly, taking your numbered points in order:

1. There would be two rows in Places for Ben More, each with a different
PlaceID and with one referencing the Mull row of the Regions table, the other
referencing the Glen Dochart row. This is directly analogous to the
duplication of city names to which I referred and consequently Places
requires a surrogate key as would Cities.

2. The combo box would be bound to the PlaceID column and would have at
least two columns in its drop down list (see the example in my demo which
lists the County, District and Civil Parish).

3. The place name can either be selected with the mouse, or typed into the
combo box. With the latter, provided the AutoExpand property of the combo
box is True (Yes in the properties sheet) the control will progressively go
to the first match as each character is entered.

4. With the example I gave you for adding a new city, in the same way that
it opens the frmCities form and passes the new city name to it, the frmCities
form would include a combo box bound to the CountyID column in the Cities
table, which would include code in its NotInList event procedure to add a new
county. This would open a frmCounties form and pass the new county name to
it in the same way. This process can be repeated to add a region (in England
a region is bigger than a county; Staffordshire is in West Midlands region),
and then to add a new Country. You'd do exactly the same with your hierarchy.
A crucial thing to understand with this, though, is that each form must be
opened in dialogue mode, using the acDialog setting for the WindowMode
argument of the OpenForm method. Opening a dorm in dialogue mode pauses the
execution of the calling code until the form has been closed (or strictly
speaking, hidden). Consequently a row is not inserted into the Regions table
until the Counties form closes, a row is not inserted into the Counties table
until the Regions form closes, and a row is not inserted into the Cities
table until the Counties form closes. Referential integrity is therefore
maintained.

However, if you already have one Ben More in the list, typing it into the
combo box will just select that one so you can't add a second one via the
NotInList event as you can for a place name not yet represented. So you'd
need another way to handle that scenario, e.g. an 'Add New Place' button on
the form which would open the Places form at a new record and in dialogue
mode. The code in the button's event procedure would then requery the
PlaceID combo box to show the new place in its list.

So, the answer to your final question is yes, and subforms are not needed in
relation to the hierarchical data.

Returning to your first points, things like the date of the event are simply
an attribute of the Events entity type so would be a column in the Events
table, with a control on the Events form bound to it. As regards activities,
however, the relationship between Events and Activities might well be many-to-
many, so would be modelled by an EventActivities table with foreign key
columns EventID and Activity ID. For this you would use a subform in the
Events form. The subform would be based on the EventActivities table and
linked to the parent form on EventID. It would be in continuous form or
datasheet view and contain a combo box bound to the ActivityID column but set
up to show the activity text in the same way as I described for the PlaceID
combo box in my first response. This combo box would include code in its
NotInList event for adding a new activity not yet included in the list.

Ken Sheridan
Stafford, England

Thanks Ken,

I haven't reviewed the information on the link yet so appologies if the
answers are in there but just to make sure I am on the right track, the way I
thought this might work was as outlined in your option 1.

To keep using your model of the tables relating to an Event for simplicity,
my thinking was that query was required especially if the Event had other
related data such as an Activity, Date, Diary or Journal Memo, etc. so that
all information could be pulled together using one form.

This would require a further one to many relationship between tblPlace and
tblEvent. Correct?

The specific questions I am trying to answer are;

1 how can data be entered from this form using combo boxes when the same
Place name could apply to more than one Region. eg. Scotland, Western Isles,
Mull, Ben More and Scotland, Southern Highlands, Glen Dochart, Ben More.

2 tblPlace would contain 2 Ben More records and therefore how does one
select the correct one if choosing from a combo box?

3 can the fields be auto populated with data by entering a known Place name?

4 what happens if all 4 tables were to require a new record so that a
completely new location could be created?

Do your suggestions cater for this sort of functionallity and will I have to
investigate the use of subforms, or is this another thing alltogether?

Iain:

[quoted text clipped - 145 lines]

 

[iain]

Thanks ken,

this really great stuff and so much for me to get my teeth into, but just in
case I head off in the wrong direction can I clarify a couple of more points.

Correct me if I am wrong but when I looked at the example in your earlier
attachment, in basic terms the solution seems to be:

Link the for tables and build the data from Country to Place as described in
your point 4.

This effectively creates a location which can be saved to a separate table.

This table exists purely to enable selection of all 4 fields at the same
time. Correct?

So, creating a new Place is really creating a new record in the Location
table?

If this is correct then:

1 is PlaceID still the foreign key in tblEvent or would CountryID be used
imstead?
2 which of the 3 options in your original message works with this solution?
3 why didn't you use an autonumber data type for the Location?

Iain

 

[KenSheridan via AccessMonster.com]

Location in my demo could be the equivalent of Place; or Parish could be the
equivalent of Place and Location the equivalent of Event (assuming each event
happens in one place only) in the hypothetical scenario I outlined earlier.
It doesn't really matter; either way there is a linear hierarchy of entity
types. So PlaceID would be the foreign key in Events. Via the
relationships we then know where the event took pace at whatever level of the
hierarchy right up to the country. If CountryID was the foreign key in
Events we'd only know which country it took place in, but it could be
anywhere in that country.

Using a different analogy, if you know that my wife Fiona's father was Angus
MacDonald then you know her paternal grandfather was John MacDonald, but if
you only know that her paternal grandfather was John MacDonald her father
could be John or Ranald MacDonald, his two sons. Three guesses where her
family are from!

Ken Sheridan
Stafford, England

Thanks ken,

this really great stuff and so much for me to get my teeth into, but just in
case I head off in the wrong direction can I clarify a couple of more points.

Correct me if I am wrong but when I looked at the example in your earlier
attachment, in basic terms the solution seems to be:

Link the for tables and build the data from Country to Place as described in
your point 4.

This effectively creates a location which can be saved to a separate table.

This table exists purely to enable selection of all 4 fields at the same
time. Correct?

So, creating a new Place is really creating a new record in the Location
table?

If this is correct then:

1 is PlaceID still the foreign key in tblEvent or would CountryID be used
imstead?
2 which of the 3 options in your original message works with this solution?
3 why didn't you use an autonumber data type for the Location?

Iain

Iain:

[quoted text clipped - 59 lines]

Ken Sheridan
Stafford, England

 

[iain]

Ok, I think I have all I need for now.

We are in danger of getting tied up in a hypothetical set of tables, name
equivalents etc. I think I need to try out your suggestions on some real
data.

Iain

Location in my demo could be the equivalent of Place; or Parish could be the
equivalent of Place and Location the equivalent of Event (assuming each event
happens in one place only) in the hypothetical scenario I outlined earlier.
It doesn't really matter; either way there is a linear hierarchy of entity
types. So PlaceID would be the foreign key in Events. Via the
relationships we then know where the event took pace at whatever level of the
hierarchy right up to the country. If CountryID was the foreign key in
Events we'd only know which country it took place in, but it could be
anywhere in that country.

Using a different analogy, if you know that my wife Fiona's father was Angus
MacDonald then you know her paternal grandfather was John MacDonald, but if
you only know that her paternal grandfather was John MacDonald her father
could be John or Ranald MacDonald, his two sons. Three guesses where her
family are from!

Ken Sheridan
Stafford, England

Thanks ken,

this really great stuff and so much for me to get my teeth into, but just in
case I head off in the wrong direction can I clarify a couple of more points.

Correct me if I am wrong but when I looked at the example in your earlier
attachment, in basic terms the solution seems to be:

Link the for tables and build the data from Country to Place as described in
your point 4.

This effectively creates a location which can be saved to a separate table.

This table exists purely to enable selection of all 4 fields at the same
time. Correct?

So, creating a new Place is really creating a new record in the Location
table?

If this is correct then:

1 is PlaceID still the foreign key in tblEvent or would CountryID be used
imstead?
2 which of the 3 options in your original message works with this solution?
3 why didn't you use an autonumber data type for the Location?

Iain

Iain:

[quoted text clipped - 59 lines]

Ken Sheridan
Stafford, England

 

[iain]

Ken,

Job done. Well, almost.

I have succesfully recreated your demo versions of forms, excluding the
hybrid control version, using my 4 related tables.

I have also amended one of the fileds in the short version and that also
seems ok, so I'm confident I can apply this throughout.

We have had an unfortuneate clash of terminology in the use of 'Event' and I
was unsure of your use of 'SpaceID' right back at the start. So what I now
have is:

1 - tblCountry, tblRegion, tblArea and tblPlace related to a further
tblLocation via PlaceID
2 - I have 900 Locations that need to be related to some 10,000 records
3 - the database has several other tables which along with the Locations,
collectively form a single record of what I call an Event (real life event).
4 - I am using a master table to which all these other tables are related to
form each record of Event.

In the case of the Locations, I am unsure of how best to create the
relationship with this master table.

Should I use PlaceID or LocationID as the foreign key in the master table?
Both are One to Many and it is a simple job to populate the master table with
values of either key.

Last question. Honest.

Regards

Location in my demo could be the equivalent of Place; or Parish could be the
equivalent of Place and Location the equivalent of Event (assuming each event
happens in one place only) in the hypothetical scenario I outlined earlier.
It doesn't really matter; either way there is a linear hierarchy of entity
types. So PlaceID would be the foreign key in Events. Via the
relationships we then know where the event took pace at whatever level of the
hierarchy right up to the country. If CountryID was the foreign key in
Events we'd only know which country it took place in, but it could be
anywhere in that country.

Using a different analogy, if you know that my wife Fiona's father was Angus
MacDonald then you know her paternal grandfather was John MacDonald, but if
you only know that her paternal grandfather was John MacDonald her father
could be John or Ranald MacDonald, his two sons. Three guesses where her
family are from!

Ken Sheridan
Stafford, England

Thanks ken,

this really great stuff and so much for me to get my teeth into, but just in
case I head off in the wrong direction can I clarify a couple of more points.

Correct me if I am wrong but when I looked at the example in your earlier
attachment, in basic terms the solution seems to be:

Link the for tables and build the data from Country to Place as described in
your point 4.

This effectively creates a location which can be saved to a separate table.

This table exists purely to enable selection of all 4 fields at the same
time. Correct?

So, creating a new Place is really creating a new record in the Location
table?

If this is correct then:

1 is PlaceID still the foreign key in tblEvent or would CountryID be used
imstead?
2 which of the 3 options in your original message works with this solution?
3 why didn't you use an autonumber data type for the Location?

Iain

Iain:

[quoted text clipped - 59 lines]

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

Iain:

I chose Events as a hypothetical example, not realising that's what you are
actually recording. Hope it didn't confuse matters too much.

The foreign key for your master table should be LocationID if it’s the
'location' to which you want to relate the 'event' rather than the larger
'place'. Say you have a value 'Edinburgh' in tblPlace , and a value
'Edinburgh Castle' in tblLocation with a LocationID of 42, then the foreign
key for an event type 'Military Tattoo' in your master table would be the
LocationID for 'Edinburgh Castle', 42. The combination of 'Military Tattoo'
and 42 in the master table shows that this is the military tattoo which takes
pace at Edinburgh Castle and distinguishes this from any other military
tattoos which you might record taking place in another location. Lets assume
hypothetically that another military tattoo is held at Holyrood Palace, which
has a LocationID of 99 in the tblLocation table, this would have values of
'Military Tattoo' and 99 in the master table. If, instead of LocationID you
had PlaceID as the foreign key in the master table then you'd only know each
military tattoo is held in Edinburgh, but not at which locations in the city.


Ken Sheridan
Stafford, England

Ken,

Job done. Well, almost.

I have succesfully recreated your demo versions of forms, excluding the
hybrid control version, using my 4 related tables.

I have also amended one of the fileds in the short version and that also
seems ok, so I'm confident I can apply this throughout.

We have had an unfortuneate clash of terminology in the use of 'Event' and I
was unsure of your use of 'SpaceID' right back at the start. So what I now
have is:

1 - tblCountry, tblRegion, tblArea and tblPlace related to a further
tblLocation via PlaceID
2 - I have 900 Locations that need to be related to some 10,000 records
3 - the database has several other tables which along with the Locations,
collectively form a single record of what I call an Event (real life event).
4 - I am using a master table to which all these other tables are related to
form each record of Event.

In the case of the Locations, I am unsure of how best to create the
relationship with this master table.

Should I use PlaceID or LocationID as the foreign key in the master table?
Both are One to Many and it is a simple job to populate the master table with
values of either key.

Last question. Honest.

Regards

Location in my demo could be the equivalent of Place; or Parish could be the
equivalent of Place and Location the equivalent of Event (assuming each event

[quoted text clipped - 48 lines]

 

[iain]

Ken,

I see what you mean. Also, the manner in which you have described these
latest relationships explains some earlier points you made that had me
slightly confused.

I think you and I are visualising these relationships the opposite way round
from one another.

Location as I see it is a collective description, defined by the 4 tables.
My reason for doing this was that whilst it is easy to remember Places in the
UK, and use the Place field as a search string or filter, it might not be so
easy say for China. In that case, searching might have to be a progressive
narrowing down starting with the country and working towards Place, to act as
a memory jogger as much as a data management feature.

So I'm going to go with LocationID as Location is an entity in it's own right.

By the way, one other thing about the adding of new records, the cboCities
example and your explanation also seems to have fallen into the 'wrong way
round' category but I don't think it is an issue.

I have tried out the code on the country combo box from the first form and
the new country is passed to the dialog box using OpenArgs. When the dialog
box opens, it displays only one 'New' record with the new country in the
country field. The process of actually creating the new record i.e.
incrementing the primary key, doesn't happen until the new country is
re-typed in the country field and the form closed manually by closing the
window.

Is this what the code is supposed to do? Seems a bit odd having to type it
twice. I've found a few examples of this code now and they all appear almost
identical. Only real difference is that the DoCmd.Close form is not included.

The manual closure of the window seems to make this redundant in any case.

Any ideas?

Iain

Iain:

I chose Events as a hypothetical example, not realising that's what you are
actually recording. Hope it didn't confuse matters too much.

The foreign key for your master table should be LocationID if it’s the
'location' to which you want to relate the 'event' rather than the larger
'place'. Say you have a value 'Edinburgh' in tblPlace , and a value
'Edinburgh Castle' in tblLocation with a LocationID of 42, then the foreign
key for an event type 'Military Tattoo' in your master table would be the
LocationID for 'Edinburgh Castle', 42. The combination of 'Military Tattoo'
and 42 in the master table shows that this is the military tattoo which takes
pace at Edinburgh Castle and distinguishes this from any other military
tattoos which you might record taking place in another location. Lets assume
hypothetically that another military tattoo is held at Holyrood Palace, which
has a LocationID of 99 in the tblLocation table, this would have values of
'Military Tattoo' and 99 in the master table. If, instead of LocationID you
had PlaceID as the foreign key in the master table then you'd only know each
military tattoo is held in Edinburgh, but not at which locations in the city.


Ken Sheridan
Stafford, England

Ken,

Job done. Well, almost.

I have succesfully recreated your demo versions of forms, excluding the
hybrid control version, using my 4 related tables.

I have also amended one of the fileds in the short version and that also
seems ok, so I'm confident I can apply this throughout.

We have had an unfortuneate clash of terminology in the use of 'Event' and I
was unsure of your use of 'SpaceID' right back at the start. So what I now
have is:

1 - tblCountry, tblRegion, tblArea and tblPlace related to a further
tblLocation via PlaceID
2 - I have 900 Locations that need to be related to some 10,000 records
3 - the database has several other tables which along with the Locations,
collectively form a single record of what I call an Event (real life event).
4 - I am using a master table to which all these other tables are related to
form each record of Event.

In the case of the Locations, I am unsure of how best to create the
relationship with this master table.

Should I use PlaceID or LocationID as the foreign key in the master table?
Both are One to Many and it is a simple job to populate the master table with
values of either key.

Last question. Honest.

Regards

Location in my demo could be the equivalent of Place; or Parish could be the
equivalent of Place and Location the equivalent of Event (assuming each event

[quoted text clipped - 48 lines]

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

Iain:

If the tblCountry table includes only the autonumber primary key and the
country name columns then you don't need to open a form at all; the code can
simply insert a new row into the table. Here's an example for cities which
would do that:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

The reason for the DoCmd.Close line BTW is that when a form is opened in
dialogue mode the execution of the calling code is paused until the called
form is closed or hidden. If the called form was hidden without expressly
saving the new record the new item could not be inserted into combo box's
list of course when the code execution resumes. So by explicitly closing the
called form this ensures that the record is saved even in the unlikely event
of the calling form having been hidden before the remaining code executes.
I'm taking account of Murphy's Law here, always a wise thing to do when
developing an application.

As regards "visualising these relationships the opposite way round" one-to-
many, and even one-to-one relationships can only be one way around; that's
fundamental to the relational model. I think what you are saying is that
data entry needs to be 'top down'. That is of course exactly what the hybrid
controls in my demo are designed to facilitate, in my case selecting a county,
then a district from a subset of districts (those in the selected county),
then civil parish from a subset of parishes (those in the selected district).
Many of the (simpler) solutions which you see for this are for a non-
normalized table like that in Northwind. Mine is designed to allow this
approach while maintaining correctly normalized tables.

For searching the database in the same 'top down' manner unbound correlated
combo boxes on a dialogue form can be used in the same way of course, but in
this case they are much simpler as its merely a case of referencing the first
in the second's RowSource and then requerying the second's in the first's
AfterUpdate event procedure; then doing the same with the second and third,
and so on down the line. No hybrid controls are necessary.

BTW the demo does not include any means of inserting new rows via the
NotInList event procedure; it was designed purely to illustrate how to build
the correlated combo boxes not as a fully developed solution.

Ken Sheridan
Stafford, England

Ken,

I see what you mean. Also, the manner in which you have described these
latest relationships explains some earlier points you made that had me
slightly confused.

I think you and I are visualising these relationships the opposite way round
from one another.

Location as I see it is a collective description, defined by the 4 tables.
My reason for doing this was that whilst it is easy to remember Places in the
UK, and use the Place field as a search string or filter, it might not be so
easy say for China. In that case, searching might have to be a progressive
narrowing down starting with the country and working towards Place, to act as
a memory jogger as much as a data management feature.

So I'm going to go with LocationID as Location is an entity in it's own right.

By the way, one other thing about the adding of new records, the cboCities
example and your explanation also seems to have fallen into the 'wrong way
round' category but I don't think it is an issue.

I have tried out the code on the country combo box from the first form and
the new country is passed to the dialog box using OpenArgs. When the dialog
box opens, it displays only one 'New' record with the new country in the
country field. The process of actually creating the new record i.e.
incrementing the primary key, doesn't happen until the new country is
re-typed in the country field and the form closed manually by closing the
window.

Is this what the code is supposed to do? Seems a bit odd having to type it
twice. I've found a few examples of this code now and they all appear almost
identical. Only real difference is that the DoCmd.Close form is not included.

The manual closure of the window seems to make this redundant in any case.

Any ideas?

Iain

Iain:

[quoted text clipped - 57 lines]

 

[iain]

Ken,

Thanks again.

I don't know what was wromg with my Dialog box, but I've recreated it and
itworks fine now. The only change I made was to use the OnLoad event instead
of OnOpen as follows:

If Not IsNull(Me.OpenArgs) Then
Me.Country = Me.OpenArgs
End If

I think I'm ok with the relationships now so we can call it a day on this
one. Got a bit of tyding up and testing to do now.

Thanks.

Iain:

If the tblCountry table includes only the autonumber primary key and the
country name columns then you don't need to open a form at all; the code can
simply insert a new row into the table. Here's an example for cities which
would do that:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

The reason for the DoCmd.Close line BTW is that when a form is opened in
dialogue mode the execution of the calling code is paused until the called
form is closed or hidden. If the called form was hidden without expressly
saving the new record the new item could not be inserted into combo box's
list of course when the code execution resumes. So by explicitly closing the
called form this ensures that the record is saved even in the unlikely event
of the calling form having been hidden before the remaining code executes.
I'm taking account of Murphy's Law here, always a wise thing to do when
developing an application.

As regards "visualising these relationships the opposite way round" one-to-
many, and even one-to-one relationships can only be one way around; that's
fundamental to the relational model. I think what you are saying is that
data entry needs to be 'top down'. That is of course exactly what the hybrid
controls in my demo are designed to facilitate, in my case selecting a county,
then a district from a subset of districts (those in the selected county),
then civil parish from a subset of parishes (those in the selected district).
Many of the (simpler) solutions which you see for this are for a non-
normalized table like that in Northwind. Mine is designed to allow this
approach while maintaining correctly normalized tables.

For searching the database in the same 'top down' manner unbound correlated
combo boxes on a dialogue form can be used in the same way of course, but in
this case they are much simpler as its merely a case of referencing the first
in the second's RowSource and then requerying the second's in the first's
AfterUpdate event procedure; then doing the same with the second and third,
and so on down the line. No hybrid controls are necessary.

BTW the demo does not include any means of inserting new rows via the
NotInList event procedure; it was designed purely to illustrate how to build
the correlated combo boxes not as a fully developed solution.

Ken Sheridan
Stafford, England

Ken,

I see what you mean. Also, the manner in which you have described these
latest relationships explains some earlier points you made that had me
slightly confused.

I think you and I are visualising these relationships the opposite way round
from one another.

Location as I see it is a collective description, defined by the 4 tables.
My reason for doing this was that whilst it is easy to remember Places in the
UK, and use the Place field as a search string or filter, it might not be so
easy say for China. In that case, searching might have to be a progressive
narrowing down starting with the country and working towards Place, to act as
a memory jogger as much as a data management feature.

So I'm going to go with LocationID as Location is an entity in it's own right.

By the way, one other thing about the adding of new records, the cboCities
example and your explanation also seems to have fallen into the 'wrong way
round' category but I don't think it is an issue.

I have tried out the code on the country combo box from the first form and
the new country is passed to the dialog box using OpenArgs. When the dialog
box opens, it displays only one 'New' record with the new country in the
country field. The process of actually creating the new record i.e.
incrementing the primary key, doesn't happen until the new country is
re-typed in the country field and the form closed manually by closing the
window.

Is this what the code is supposed to do? Seems a bit odd having to type it
twice. I've found a few examples of this code now and they all appear almost
identical. Only real difference is that the DoCmd.Close form is not included.

The manual closure of the window seems to make this redundant in any case.

Any ideas?

Iain

Iain:

[quoted text clipped - 57 lines]

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

Iain:

Final words, at least for the moment, by the sound of it:

The difference is that you are setting the Value property (which is the
default property if not specified) whereas my code sets the DefaultValue
property. Setting the Value property has two implications:

1. It cannot be set until the form is loaded, whereas the DefaultValue
property can be set when it opens.

2. It 'dirties' the form, i.e. it initiates a new record, which is then
saved when the form is closed unless the user undoes the record with the Esc
key. The DefaultValue property on the other hand does not dirty the form, so
if the user exits without entering any more data, or without re-entering or
editing the default value, the record is not saved. Setting the Value
property therefore removes the ability of the user to abort the new record
simply by closing the form without entering any data, which is probably not
an issue as they have already confirmed that they want to add the new record
via the message box popped up by the NotInList event procedure's code.

Ken Sheridan
Stafford, England

Ken,

Thanks again.

I don't know what was wromg with my Dialog box, but I've recreated it and
itworks fine now. The only change I made was to use the OnLoad event instead
of OnOpen as follows:

If Not IsNull(Me.OpenArgs) Then
Me.Country = Me.OpenArgs
End If

I think I'm ok with the relationships now so we can call it a day on this
one. Got a bit of tyding up and testing to do now.

Thanks.

Iain:

[quoted text clipped - 110 lines]

 

[iain]

Thanks Ken,

I just assumed that any value dirtied the form and used this code in
addition to setting the close button property of the form to 'No' so that the
user can only use the Cancel & Close Button which I added.

If (Me.Dirty = True) Then
Me.Undo
End If
CoCmd.Close...........

I have a lot other examples of these chains of relationships in the database
I am constructing and plenty of opportunity to experiment with the order of
precedence(the which way round discussion we had) of the relationships, so I
have a lot of work to keep me busy for a while.

Your help has been invaluable on this.

Thanks again.

Iain:

Final words, at least for the moment, by the sound of it:

The difference is that you are setting the Value property (which is the
default property if not specified) whereas my code sets the DefaultValue
property. Setting the Value property has two implications:

1. It cannot be set until the form is loaded, whereas the DefaultValue
property can be set when it opens.

2. It 'dirties' the form, i.e. it initiates a new record, which is then
saved when the form is closed unless the user undoes the record with the Esc
key. The DefaultValue property on the other hand does not dirty the form, so
if the user exits without entering any more data, or without re-entering or
editing the default value, the record is not saved. Setting the Value
property therefore removes the ability of the user to abort the new record
simply by closing the form without entering any data, which is probably not
an issue as they have already confirmed that they want to add the new record
via the message box popped up by the NotInList event procedure's code.

Ken Sheridan
Stafford, England

Ken,

Thanks again.

I don't know what was wromg with my Dialog box, but I've recreated it and
itworks fine now. The only change I made was to use the OnLoad event instead
of OnOpen as follows:

If Not IsNull(Me.OpenArgs) Then
Me.Country = Me.OpenArgs
End If

I think I'm ok with the relationships now so we can call it a day on this
one. Got a bit of tyding up and testing to do now.

Thanks.

Iain:

[quoted text clipped - 110 lines]

Ken Sheridan
Stafford, England