By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,935 Members | 1,387 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,935 IT Pros & Developers. It's quick & easy.

Database design question...

P: n/a
I'm developing a database using MS Access and have come across a problem.

The majority of my database is pretty straightforward "many to one"
relationships. I have one relationship that is backwards though... This is
probably easier to diagram than to describe...

Countries -M to1-> Provinces - M to 1-> Cities <-M to 1- Call Centres

With the above relationships I am having trouble implementing a simple data
entry interface. Basically what I have now is a main form, with a child
form, with a child form, with a child form... but linking between the Cities
and Call Centres forms is kinda tricky.

What I thought of doing was reversing the relationship between Cities and
Call Centres to simplify data entry. Then I'd add a field to Cities such as
"Use the Call Centre for this other City" but that could cause a lot of
recursion and possibly a recursion loop (City X uses the Call Centre for
City Y, which uses the Call Centre for City Z - which could be hell to
program in SQL... or worse City X uses the Call Centre for City Y, which
uses the Call Centre for City X)

What this data represents is:
- Each City has a single Call Centre
- A call centre can be used by multiple cities. Changes to a Centre should
be reflected for all its Cities.

This should be straightforward, but I'm having a hard time getting my head
around a data entry interface.

Does anyone have any suggestions that might help me out?


Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
You need cascading listboxes or comboboxes for Countries, Provimces, Cities and
Call Centres. You would first select country which limits the provinces to that
country. Then from those provinces you would select one which limits the cities
to cities within that one province. Finally you would select a city which would
limit the list of Call Centres in the final listbox or combobox to Call Centres
in the selected city.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Noozer" <do*******@me.here> wrote in message
news:p2XEc.925509$Pk3.121900@pd7tw1no...
I'm developing a database using MS Access and have come across a problem.

The majority of my database is pretty straightforward "many to one"
relationships. I have one relationship that is backwards though... This is
probably easier to diagram than to describe...

Countries -M to1-> Provinces - M to 1-> Cities <-M to 1- Call Centres

With the above relationships I am having trouble implementing a simple data
entry interface. Basically what I have now is a main form, with a child
form, with a child form, with a child form... but linking between the Cities
and Call Centres forms is kinda tricky.

What I thought of doing was reversing the relationship between Cities and
Call Centres to simplify data entry. Then I'd add a field to Cities such as
"Use the Call Centre for this other City" but that could cause a lot of
recursion and possibly a recursion loop (City X uses the Call Centre for
City Y, which uses the Call Centre for City Z - which could be hell to
program in SQL... or worse City X uses the Call Centre for City Y, which
uses the Call Centre for City X)

What this data represents is:
- Each City has a single Call Centre
- A call centre can be used by multiple cities. Changes to a Centre should
be reflected for all its Cities.

This should be straightforward, but I'm having a hard time getting my head
around a data entry interface.

Does anyone have any suggestions that might help me out?

Nov 13 '05 #2

P: n/a

"PC Datasheet" <no****@nospam.spam> wrote in message
news:qj****************@newsread2.news.atl.earthli nk.net...
You need cascading listboxes or comboboxes for Countries, Provimces, Cities and Call Centres. You would first select country which limits the provinces to that country. Then from those provinces you would select one which limits the cities to cities within that one province. Finally you would select a city which would limit the list of Call Centres in the final listbox or combobox to Call

Centres
The problem is that when I select the city, I want the Call Centres box to
show the current Center assigned to that city, but to show ALL call centres
since a single call center can service multiple cities.

i.e.

USA -> Texas -> Houston -> HoustonCentre
USA -> Texas -> Dallas -> HoustonCentre
USA -> Nevada-> Reno -> HoustonCentre
USA -> Nevada -> Vegas -> VegasCentre
Canada -> Ontario -> Toronto -> TorontoCentre
Canada -> Ontario -> Ottawa -> TorontoCentre
Canada -> Alberta -> Calgary -> CalgaryCentre
Canada -> Alberta -> Edmonton-> CalgaryCentre
Canada -> Manitoba -> Winnipeg -> HoustonCentre

The database is currently built like this, and seems to work, but I cannot
figure out how to design the MSAccess forms to provide a usable data entry
interface for this structure.

In the City table I have a CentreKeyLink column that is related to the
CentreKey column in the Call Centre table. When I open my Call Centre form
filtered by this relationship, then I cannot see any Centres except the one
linked to the City. If I don't filter I can see all the Centres available,
but it always starts at the first Centre and not the one selected for that
City and I lose the reference.

I have created the relationships in my database but since this one is 1
Center to many Cities, it doesn't work.

Thanks for the reply
I'm developing a database using MS Access and have come across a problem.
The majority of my database is pretty straightforward "many to one"
relationships. I have one relationship that is backwards though... This is probably easier to diagram than to describe...

Countries -M to1-> Provinces - M to 1-> Cities <-M to 1- Call Centres

With the above relationships I am having trouble implementing a simple data entry interface. Basically what I have now is a main form, with a child
form, with a child form, with a child form... but linking between the Cities and Call Centres forms is kinda tricky.

What I thought of doing was reversing the relationship between Cities and Call Centres to simplify data entry. Then I'd add a field to Cities such as "Use the Call Centre for this other City" but that could cause a lot of
recursion and possibly a recursion loop (City X uses the Call Centre for
City Y, which uses the Call Centre for City Z - which could be hell to
program in SQL... or worse City X uses the Call Centre for City Y, which
uses the Call Centre for City X)

What this data represents is:
- Each City has a single Call Centre
- A call centre can be used by multiple cities. Changes to a Centre should be reflected for all its Cities.

This should be straightforward, but I'm having a hard time getting my head around a data entry interface.

Does anyone have any suggestions that might help me out?

Nov 13 '05 #3

P: n/a
Your tables need to look like:

TblCountry
CountryID
Country

TblProvince
ProvinceID
CountryID
Province

TblCity
CityID
ProvinceID
City

TblCallCentre
CallCentreID
CityID
CallCentre

The first combobox would be based on tblCountry
The second combobox would be based on a query based on TblProvince where the
criteria for CountryID is Forms!NameOfYourForm!CountryID. The AfterUpdate event
would requery the third combobox.
The third combobox would be based on a query based on TblCity where the criteria
for ProvinceID is Forms!NameOfYourForm!ProvinceID. The AfterUpdate event would
requery the fourth combobox.
Thefourth combobox would be based on a query based on TblCallCentre where the
criteria for CityID is Forms!NameOfYourForm!CityID.

There would be four records for HoustonCentre in TblCallCentre because the
HoustonCentre is associated with four different cities. So if you selected
Houston, Dallas, Reno or Winnipeg in the third combobox, the fourth combobox
would be the HoustonCentre.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Noozer" <do*******@me.here> wrote in message
news:AMYEc.962568$oR5.711244@pd7tw3no...

"PC Datasheet" <no****@nospam.spam> wrote in message
news:qj****************@newsread2.news.atl.earthli nk.net...
You need cascading listboxes or comboboxes for Countries, Provimces,

Cities and
Call Centres. You would first select country which limits the provinces to

that
country. Then from those provinces you would select one which limits the

cities
to cities within that one province. Finally you would select a city which

would
limit the list of Call Centres in the final listbox or combobox to Call

Centres
The problem is that when I select the city, I want the Call Centres box to
show the current Center assigned to that city, but to show ALL call centres
since a single call center can service multiple cities.

i.e.

USA -> Texas -> Houston -> HoustonCentre
USA -> Texas -> Dallas -> HoustonCentre
USA -> Nevada-> Reno -> HoustonCentre
USA -> Nevada -> Vegas -> VegasCentre
Canada -> Ontario -> Toronto -> TorontoCentre
Canada -> Ontario -> Ottawa -> TorontoCentre
Canada -> Alberta -> Calgary -> CalgaryCentre
Canada -> Alberta -> Edmonton-> CalgaryCentre
Canada -> Manitoba -> Winnipeg -> HoustonCentre

The database is currently built like this, and seems to work, but I cannot
figure out how to design the MSAccess forms to provide a usable data entry
interface for this structure.

In the City table I have a CentreKeyLink column that is related to the
CentreKey column in the Call Centre table. When I open my Call Centre form
filtered by this relationship, then I cannot see any Centres except the one
linked to the City. If I don't filter I can see all the Centres available,
but it always starts at the first Centre and not the one selected for that
City and I lose the reference.

I have created the relationships in my database but since this one is 1
Center to many Cities, it doesn't work.

Thanks for the reply
I'm developing a database using MS Access and have come across a problem.
The majority of my database is pretty straightforward "many to one"
relationships. I have one relationship that is backwards though... This is probably easier to diagram than to describe...

Countries -M to1-> Provinces - M to 1-> Cities <-M to 1- Call Centres

With the above relationships I am having trouble implementing a simple data entry interface. Basically what I have now is a main form, with a child
form, with a child form, with a child form... but linking between the Cities and Call Centres forms is kinda tricky.

What I thought of doing was reversing the relationship between Cities and Call Centres to simplify data entry. Then I'd add a field to Cities such as "Use the Call Centre for this other City" but that could cause a lot of
recursion and possibly a recursion loop (City X uses the Call Centre for
City Y, which uses the Call Centre for City Z - which could be hell to
program in SQL... or worse City X uses the Call Centre for City Y, which
uses the Call Centre for City X)

What this data represents is:
- Each City has a single Call Centre
- A call centre can be used by multiple cities. Changes to a Centre should be reflected for all its Cities.

This should be straightforward, but I'm having a hard time getting my head around a data entry interface.

Does anyone have any suggestions that might help me out?


Nov 13 '05 #4

P: n/a
Still no good...

I want to change data in only one place for the Call Center and affect all
the Cities that use it.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:tg*****************@newsread2.news.atl.earthl ink.net...
Your tables need to look like:

TblCountry
CountryID
Country

TblProvince
ProvinceID
CountryID
Province

TblCity
CityID
ProvinceID
City

TblCallCentre
CallCentreID
CityID
CallCentre

The first combobox would be based on tblCountry
The second combobox would be based on a query based on TblProvince where the criteria for CountryID is Forms!NameOfYourForm!CountryID. The AfterUpdate event would requery the third combobox.
The third combobox would be based on a query based on TblCity where the criteria for ProvinceID is Forms!NameOfYourForm!ProvinceID. The AfterUpdate event would requery the fourth combobox.
Thefourth combobox would be based on a query based on TblCallCentre where the criteria for CityID is Forms!NameOfYourForm!CityID.

There would be four records for HoustonCentre in TblCallCentre because the
HoustonCentre is associated with four different cities. So if you selected
Houston, Dallas, Reno or Winnipeg in the third combobox, the fourth combobox would be the HoustonCentre.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Noozer" <do*******@me.here> wrote in message
news:AMYEc.962568$oR5.711244@pd7tw3no...

"PC Datasheet" <no****@nospam.spam> wrote in message
news:qj****************@newsread2.news.atl.earthli nk.net...
You need cascading listboxes or comboboxes for Countries, Provimces,

Cities and
Call Centres. You would first select country which limits the provinces to
that
country. Then from those provinces you would select one which limits
the cities
to cities within that one province. Finally you would select a city
which would
limit the list of Call Centres in the final listbox or combobox to
Call Centres
The problem is that when I select the city, I want the Call Centres box to show the current Center assigned to that city, but to show ALL call centres since a single call center can service multiple cities.

i.e.

USA -> Texas -> Houston -> HoustonCentre
USA -> Texas -> Dallas -> HoustonCentre
USA -> Nevada-> Reno -> HoustonCentre
USA -> Nevada -> Vegas -> VegasCentre
Canada -> Ontario -> Toronto -> TorontoCentre
Canada -> Ontario -> Ottawa -> TorontoCentre
Canada -> Alberta -> Calgary -> CalgaryCentre
Canada -> Alberta -> Edmonton-> CalgaryCentre
Canada -> Manitoba -> Winnipeg -> HoustonCentre

The database is currently built like this, and seems to work, but I cannot figure out how to design the MSAccess forms to provide a usable data entry interface for this structure.

In the City table I have a CentreKeyLink column that is related to the
CentreKey column in the Call Centre table. When I open my Call Centre form filtered by this relationship, then I cannot see any Centres except the one linked to the City. If I don't filter I can see all the Centres available, but it always starts at the first Centre and not the one selected for that City and I lose the reference.

I have created the relationships in my database but since this one is 1
Center to many Cities, it doesn't work.

Thanks for the reply
> I'm developing a database using MS Access and have come across a

problem.
>
> The majority of my database is pretty straightforward "many to one"
> relationships. I have one relationship that is backwards though...
This is
> probably easier to diagram than to describe...
>
> Countries -M to1-> Provinces - M to 1-> Cities <-M to 1- Call
Centres >
> With the above relationships I am having trouble implementing a simple data
> entry interface. Basically what I have now is a main form, with a
child > form, with a child form, with a child form... but linking between the Cities
> and Call Centres forms is kinda tricky.
>
> What I thought of doing was reversing the relationship between
Cities and
> Call Centres to simplify data entry. Then I'd add a field to Cities
such as
> "Use the Call Centre for this other City" but that could cause a lot
of > recursion and possibly a recursion loop (City X uses the Call Centre for > City Y, which uses the Call Centre for City Z - which could be hell to > program in SQL... or worse City X uses the Call Centre for City Y, which > uses the Call Centre for City X)
>
> What this data represents is:
> - Each City has a single Call Centre
> - A call centre can be used by multiple cities. Changes to a Centre

should
> be reflected for all its Cities.
>
> This should be straightforward, but I'm having a hard time getting

my head
> around a data entry interface.
>
> Does anyone have any suggestions that might help me out?



Nov 13 '05 #5

P: n/a
Change TblCallCentre To:

TblCallCentre
CallCentreID
CallCentre
<< Your Data Field >>

TblCallCentreLocations
CallCentreLocationID
CallCentreID
CityID

Base the fourth combobox on a query that joins these two tables.
"Noozer" <do*******@me.here> wrote in message
news:_g0Fc.927514$Pk3.270978@pd7tw1no...
Still no good...

I want to change data in only one place for the Call Center and affect all
the Cities that use it.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:tg*****************@newsread2.news.atl.earthl ink.net...
Your tables need to look like:

TblCountry
CountryID
Country

TblProvince
ProvinceID
CountryID
Province

TblCity
CityID
ProvinceID
City

TblCallCentre
CallCentreID
CityID
CallCentre

The first combobox would be based on tblCountry
The second combobox would be based on a query based on TblProvince where

the
criteria for CountryID is Forms!NameOfYourForm!CountryID. The AfterUpdate

event
would requery the third combobox.
The third combobox would be based on a query based on TblCity where the

criteria
for ProvinceID is Forms!NameOfYourForm!ProvinceID. The AfterUpdate event

would
requery the fourth combobox.
Thefourth combobox would be based on a query based on TblCallCentre where

the
criteria for CityID is Forms!NameOfYourForm!CityID.

There would be four records for HoustonCentre in TblCallCentre because the
HoustonCentre is associated with four different cities. So if you selected
Houston, Dallas, Reno or Winnipeg in the third combobox, the fourth

combobox
would be the HoustonCentre.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Noozer" <do*******@me.here> wrote in message
news:AMYEc.962568$oR5.711244@pd7tw3no...

"PC Datasheet" <no****@nospam.spam> wrote in message
news:qj****************@newsread2.news.atl.earthli nk.net...
> You need cascading listboxes or comboboxes for Countries, Provimces,
Cities and
> Call Centres. You would first select country which limits the provinces to that
> country. Then from those provinces you would select one which limits the cities
> to cities within that one province. Finally you would select a city which would
> limit the list of Call Centres in the final listbox or combobox to Call Centres
The problem is that when I select the city, I want the Call Centres box to show the current Center assigned to that city, but to show ALL call centres since a single call center can service multiple cities.

i.e.

USA -> Texas -> Houston -> HoustonCentre
USA -> Texas -> Dallas -> HoustonCentre
USA -> Nevada-> Reno -> HoustonCentre
USA -> Nevada -> Vegas -> VegasCentre
Canada -> Ontario -> Toronto -> TorontoCentre
Canada -> Ontario -> Ottawa -> TorontoCentre
Canada -> Alberta -> Calgary -> CalgaryCentre
Canada -> Alberta -> Edmonton-> CalgaryCentre
Canada -> Manitoba -> Winnipeg -> HoustonCentre

The database is currently built like this, and seems to work, but I cannot figure out how to design the MSAccess forms to provide a usable data entry interface for this structure.

In the City table I have a CentreKeyLink column that is related to the
CentreKey column in the Call Centre table. When I open my Call Centre form filtered by this relationship, then I cannot see any Centres except the one linked to the City. If I don't filter I can see all the Centres available, but it always starts at the first Centre and not the one selected for that City and I lose the reference.

I have created the relationships in my database but since this one is 1
Center to many Cities, it doesn't work.

Thanks for the reply

> > I'm developing a database using MS Access and have come across a
problem.
> >
> > The majority of my database is pretty straightforward "many to one"
> > relationships. I have one relationship that is backwards though... This is
> > probably easier to diagram than to describe...
> >
> > Countries -M to1-> Provinces - M to 1-> Cities <-M to 1- Call Centres > >
> > With the above relationships I am having trouble implementing a simple data
> > entry interface. Basically what I have now is a main form, with a child > > form, with a child form, with a child form... but linking between the Cities
> > and Call Centres forms is kinda tricky.
> >
> > What I thought of doing was reversing the relationship between Cities and
> > Call Centres to simplify data entry. Then I'd add a field to Cities such as
> > "Use the Call Centre for this other City" but that could cause a lot of > > recursion and possibly a recursion loop (City X uses the Call Centre for > > City Y, which uses the Call Centre for City Z - which could be hell to > > program in SQL... or worse City X uses the Call Centre for City Y, which > > uses the Call Centre for City X)
> >
> > What this data represents is:
> > - Each City has a single Call Centre
> > - A call centre can be used by multiple cities. Changes to a Centre
should
> > be reflected for all its Cities.
> >
> > This should be straightforward, but I'm having a hard time getting my head
> > around a data entry interface.
> >
> > Does anyone have any suggestions that might help me out?



Nov 13 '05 #6

P: n/a
"Noozer" <do*******@me.here> wrote in message news:<_g0Fc.927514$Pk3.270978@pd7tw1no>...
Still no good...

I want to change data in only one place for the Call Center and affect all
the Cities that use it.


Give this a try mentally:

Like shown above:
tblCountry: CountryID Country
tblProvince: ProvinceID CountryID Province
tblCity: CityID ProvinceID City

Now...
tblCallCentre: CallCentreID CallCentre
tblCCCity: CallCentreID CityID

E.g.,

tblCity
1 33 Houston
2 33 Dallas
3 44 Reno
4 44 Vegas
5 54 Toronto
6 54 Ottawa
7 57 Calgary
8 57 Edmonton
9 58 Winnipeg

tblCallCentre
1 HoustonCentre
2 VegasCentre
3 TorontoCentre
4 CalgaryCentre

tblCCCity
1 1
1 2
1 3
2 4
3 5
3 6
4 7
4 8
1 9

See if you can do everything you need with that structure. Note: make
sure any call centre specific information is in tblCallCentre.

James A. Fortune

(Whenever I use sendkeys, I feel like I'm firing a shotgun at a black
bear at midnight <grin>...) ---Dan McGarry OverByte Software
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.