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?
6  1499 
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?
"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?
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?
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?
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?
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Lefevre, Steven |
last post by:
Hey folks -
Thanks to everyone who gave input to my concerns. Of course, we don't intend
to have the mysql port open to the world. We will have Apache/PHP connect on
a unix socket, or to another...
|
by: Josh McFarlane |
last post by:
If this is not the right place to post this, I apologize.
I've taken over work for a few utility programs for a collection of
database / raw files. All the programs but one read from the files,...
|
by: Don Vaillancourt |
last post by:
Hello all,
Over the years as I design more database schemas the more I come up with
patterns in database design. The more patterns I recognize the more I
want to try to design some kind of...
|
by: MP |
last post by:
Greets,
context: vb6/ado/.mdb/jet 4.0 (no access)/sql
beginning learner, first database, planning stages
(I think the underlying question here is whether to normalize or not to
normalize this...
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a...
|
by: arrival123 |
last post by:
Hello,
I'm currently trying to decide on a database design for tags in my web 2.0 application. The problem I'm facing is that I have 3 separate tables i.e. cars, planes, and schools. All three...
|
by: ARC |
last post by:
Hello all,
General question for back-end database that has numerous date fields where
the database will be used in regions that put the month first, and regions
that do not.
Should I save a...
|
by: dgleeson3 |
last post by:
Hello all
I am creating a VB.Net distributed SQL server 2005 application.
Each computer in the system has a database with a table of users and
their telephone numbers.
Each computer has a...
|
by: David |
last post by:
Hi list.
I have a few database-related questions. These aren't Python-specific
questions, but some of my apps which use (or will use) these tables
are in Python :-) Let me know if I should ask...
|
by: Les Desser |
last post by:
In article
<fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The
Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes
Not sure if I quite follow that.
1....
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
