Design question

I need to store the working regions for a job. Often a job has more than one
region and often it's the same group of regions as with other jobs. I could
make a separate Group Table but what do I do if the job only has one region?
If I store that info in the Job Table, I would store region info in 2
different tables. Adding a separate group for every job that has one working
region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars

Are these regions pretty static (i.e. not likely to change)?

If so, you could set up your Region table so it has fields like this:
- RegionID AutoNumber Primary key
- RegionName Text
- ParentRegionID Number

Now you set up your data like this:
RegionID Region Name ParentRegionID
1 South
2 South West 1
3 South East 1
4 North
5 North West 4
5 North East 4
and so on. Effectively, South contains South West and South East, and so on.

Now when you select the combo for a job you can choose a specific region
(such as South West), or a generic region (such as South.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
I need to store the working regions for a job. Often a job has more than
one region and often it's the same group of regions as with other jobs. I
could make a separate Group Table but what do I do if the job only has one
region? If I store that info in the Job Table, I would store region info
in 2 different tables. Adding a separate group for every job that has one
working region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars

Thanks. My example wasn't clear enough. In my case a region (which actually
is a country) could belong to multiple generic regions (groups):

There are jobs that belong to group A which has countries France, Spain and
Italy as their working area.
There are jobs that belong to group B which has countries France and Belgium
as their working area.
There are jobs that don't belong to a group but only to one or more
countries.

The purpose of using groups is that if a country is added to the working
area for a specific type of job the user only has to change this once in
stead of for all jobs that it applies to.

Would you use the table you suggested and add an extra relation table to
link the countries to the groups?

On the other hand, in this way the country table would also hold group
values, which doesn't seem right.

Lars


"Allen Browne" schreef in bericht
...
Are these regions pretty static (i.e. not likely to change)?

If so, you could set up your Region table so it has fields like this:
- RegionID AutoNumber Primary key
- RegionName Text
- ParentRegionID Number

Now you set up your data like this:
RegionID Region Name ParentRegionID
1 South
2 South West 1
3 South East 1
4 North
5 North West 4
5 North East 4
and so on. Effectively, South contains South West and South East, and so
on.

Now when you select the combo for a job you can choose a specific region
(such as South West), or a generic region (such as South.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
I need to store the working regions for a job. Often a job has more than
one region and often it's the same group of regions as with other jobs. I
could make a separate Group Table but what do I do if the job only has
one region? If I store that info in the Job Table, I would store region
info in 2 different tables. Adding a separate group for every job that
has one working region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars

At the simplest level, you could have both a GroupID and a CountryID field
in your table, so the user can select a group or a single country.

There are other alternatives.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
Thanks. My example wasn't clear enough. In my case a region (which
actually is a country) could belong to multiple generic regions (groups):

There are jobs that belong to group A which has countries France, Spain
and Italy as their working area.
There are jobs that belong to group B which has countries France and
Belgium as their working area.
There are jobs that don't belong to a group but only to one or more
countries.

The purpose of using groups is that if a country is added to the working
area for a specific type of job the user only has to change this once in
stead of for all jobs that it applies to.

Would you use the table you suggested and add an extra relation table to
link the countries to the groups?

On the other hand, in this way the country table would also hold group
values, which doesn't seem right.

Lars


"Allen Browne" schreef in bericht
...
Are these regions pretty static (i.e. not likely to change)?

If so, you could set up your Region table so it has fields like this:
- RegionID AutoNumber Primary key
- RegionName Text
- ParentRegionID Number

Now you set up your data like this:
RegionID Region Name ParentRegionID
1 South
2 South West 1
3 South East 1
4 North
5 North West 4
5 North East 4
and so on. Effectively, South contains South West and South East, and so
on.

Now when you select the combo for a job you can choose a specific region
(such as South West), or a generic region (such as South.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
I need to store the working regions for a job. Often a job has more than
one region and often it's the same group of regions as with other jobs.
I could make a separate Group Table but what do I do if the job only has
one region? If I store that info in the Job Table, I would store region
info in 2 different tables. Adding a separate group for every job that
has one working region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars

Thanks. Since some jobs don't belong to a group but do have more than one
country, I think I'll go with an extra relation table for countries:

tblR_JobCountry
JobID*
CountryID*

and a GroupID field in the Job table.

Lars


"Allen Browne" schreef in bericht
...
At the simplest level, you could have both a GroupID and a CountryID field
in your table, so the user can select a group or a single country.

There are other alternatives.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
Thanks. My example wasn't clear enough. In my case a region (which
actually is a country) could belong to multiple generic regions (groups):

There are jobs that belong to group A which has countries France, Spain
and Italy as their working area.
There are jobs that belong to group B which has countries France and
Belgium as their working area.
There are jobs that don't belong to a group but only to one or more
countries.

The purpose of using groups is that if a country is added to the working
area for a specific type of job the user only has to change this once in
stead of for all jobs that it applies to.

Would you use the table you suggested and add an extra relation table to
link the countries to the groups?

On the other hand, in this way the country table would also hold group
values, which doesn't seem right.

Lars


"Allen Browne" schreef in bericht
...
Are these regions pretty static (i.e. not likely to change)?

If so, you could set up your Region table so it has fields like this:
- RegionID AutoNumber Primary key
- RegionName Text
- ParentRegionID Number

Now you set up your data like this:
RegionID Region Name ParentRegionID
1 South
2 South West 1
3 South East 1
4 North
5 North West 4
5 North East 4
and so on. Effectively, South contains South West and South East, and so
on.

Now when you select the combo for a job you can choose a specific region
(such as South West), or a generic region (such as South.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
I need to store the working regions for a job. Often a job has more
than one region and often it's the same group of regions as with other
jobs. I could make a separate Group Table but what do I do if the job
only has one region? If I store that info in the Job Table, I would
store region info in 2 different tables. Adding a separate group for
every job that has one working region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars

Lars,

I am not exactly sure what you are trying to do but if you are running
Access 2007, this seems to be a perfect chore for a multivalue field. You
would be able to assign countries to each job, as needed.

David

"Lars Brownies" wrote:

Thanks. Since some jobs don't belong to a group but do have more than one
country, I think I'll go with an extra relation table for countries:

tblR_JobCountry
JobID*
CountryID*

and a GroupID field in the Job table.

Lars


"Allen Browne" schreef in bericht
...
At the simplest level, you could have both a GroupID and a CountryID field
in your table, so the user can select a group or a single country.

There are other alternatives.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
Thanks. My example wasn't clear enough. In my case a region (which
actually is a country) could belong to multiple generic regions (groups):

There are jobs that belong to group A which has countries France, Spain
and Italy as their working area.
There are jobs that belong to group B which has countries France and
Belgium as their working area.
There are jobs that don't belong to a group but only to one or more
countries.

The purpose of using groups is that if a country is added to the working
area for a specific type of job the user only has to change this once in
stead of for all jobs that it applies to.

Would you use the table you suggested and add an extra relation table to
link the countries to the groups?

On the other hand, in this way the country table would also hold group
values, which doesn't seem right.

Lars


"Allen Browne" schreef in bericht
...
Are these regions pretty static (i.e. not likely to change)?

If so, you could set up your Region table so it has fields like this:
- RegionID AutoNumber Primary key
- RegionName Text
- ParentRegionID Number

Now you set up your data like this:
RegionID Region Name ParentRegionID
1 South
2 South West 1
3 South East 1
4 North
5 North West 4
5 North East 4
and so on. Effectively, South contains South West and South East, and so
on.

Now when you select the combo for a job you can choose a specific region
(such as South West), or a generic region (such as South.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
I need to store the working regions for a job. Often a job has more
than one region and often it's the same group of regions as with other
jobs. I could make a separate Group Table but what do I do if the job
only has one region? If I store that info in the Job Table, I would
store region info in 2 different tables. Adding a separate group for
every job that has one working region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars

.

This is not an option. First because it's an A2003 application and secondly
because I need to be able to link groups of countries to different jobs.
When a group changes I need to be able to edit the group otherwise I would
have to go to every job and change the country there. Also for entering data
I would have to add the same countries to different jobs over and over
again. On top of that, experts recommend against multi value fields. See for
instance:
http://www.eggheadcafe.com/software/...s-and-nor.aspx

Lars

"roccogrand" schreef in bericht
...
Lars,

I am not exactly sure what you are trying to do but if you are running
Access 2007, this seems to be a perfect chore for a multivalue field. You
would be able to assign countries to each job, as needed.

David

"Lars Brownies" wrote:

Thanks. Since some jobs don't belong to a group but do have more than one
country, I think I'll go with an extra relation table for countries:

tblR_JobCountry
JobID*
CountryID*

and a GroupID field in the Job table.

Lars


"Allen Browne" schreef in bericht
...
At the simplest level, you could have both a GroupID and a CountryID
field
in your table, so the user can select a group or a single country.

There are other alternatives.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
Thanks. My example wasn't clear enough. In my case a region (which
actually is a country) could belong to multiple generic regions
(groups):

There are jobs that belong to group A which has countries France,
Spain
and Italy as their working area.
There are jobs that belong to group B which has countries France and
Belgium as their working area.
There are jobs that don't belong to a group but only to one or more
countries.

The purpose of using groups is that if a country is added to the
working
area for a specific type of job the user only has to change this once
in
stead of for all jobs that it applies to.

Would you use the table you suggested and add an extra relation table
to
link the countries to the groups?

On the other hand, in this way the country table would also hold group
values, which doesn't seem right.

Lars


"Allen Browne" schreef in bericht
...
Are these regions pretty static (i.e. not likely to change)?

If so, you could set up your Region table so it has fields like this:
- RegionID AutoNumber Primary key
- RegionName Text
- ParentRegionID Number

Now you set up your data like this:
RegionID Region Name ParentRegionID
1 South
2 South West 1
3 South East 1
4 North
5 North West 4
5 North East 4
and so on. Effectively, South contains South West and South East, and
so
on.

Now when you select the combo for a job you can choose a specific
region
(such as South West), or a generic region (such as South.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
I need to store the working regions for a job. Often a job has more
than one region and often it's the same group of regions as with
other
jobs. I could make a separate Group Table but what do I do if the
job
only has one region? If I store that info in the Job Table, I would
store region info in 2 different tables. Adding a separate group for
every job that has one working region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars

.

Yes Lars,

Many of the pundits don't like MVFs but I have using them since Access 2007
beta was first released and I have never had a problem with them. In fact,
just about every table that I create has at least one MVF.

When Microsoft first introduced MVF in Access 2007, I researched the term
and found that it pre-dated Access 2007 by many years. If other database
manufacturers don't use them then it is probably because some folks are hung
up on staying with principles that are 20 years old, IMHO.

But it is academic if you are on A2003 and don't plan to upgrade to A2007 or
A2010. Try A2007 or A2010 and see for yourself if they work in your
situation. They may not be as good as sliced bread but they are pretty close
in my opinion.

David

"Lars Brownies" wrote:

This is not an option. First because it's an A2003 application and secondly
because I need to be able to link groups of countries to different jobs.
When a group changes I need to be able to edit the group otherwise I would
have to go to every job and change the country there. Also for entering data
I would have to add the same countries to different jobs over and over
again. On top of that, experts recommend against multi value fields. See for
instance:
http://www.eggheadcafe.com/software/...s-and-nor.aspx

Lars

"roccogrand" schreef in bericht
...
Lars,

I am not exactly sure what you are trying to do but if you are running
Access 2007, this seems to be a perfect chore for a multivalue field. You
would be able to assign countries to each job, as needed.

David

"Lars Brownies" wrote:

Thanks. Since some jobs don't belong to a group but do have more than one
country, I think I'll go with an extra relation table for countries:

tblR_JobCountry
JobID*
CountryID*

and a GroupID field in the Job table.

Lars


"Allen Browne" schreef in bericht
...
At the simplest level, you could have both a GroupID and a CountryID
field
in your table, so the user can select a group or a single country.

There are other alternatives.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
Thanks. My example wasn't clear enough. In my case a region (which
actually is a country) could belong to multiple generic regions
(groups):

There are jobs that belong to group A which has countries France,
Spain
and Italy as their working area.
There are jobs that belong to group B which has countries France and
Belgium as their working area.
There are jobs that don't belong to a group but only to one or more
countries.

The purpose of using groups is that if a country is added to the
working
area for a specific type of job the user only has to change this once
in
stead of for all jobs that it applies to.

Would you use the table you suggested and add an extra relation table
to
link the countries to the groups?

On the other hand, in this way the country table would also hold group
values, which doesn't seem right.

Lars


"Allen Browne" schreef in bericht
...
Are these regions pretty static (i.e. not likely to change)?

If so, you could set up your Region table so it has fields like this:
- RegionID AutoNumber Primary key
- RegionName Text
- ParentRegionID Number

Now you set up your data like this:
RegionID Region Name ParentRegionID
1 South
2 South West 1
3 South East 1
4 North
5 North West 4
5 North East 4
and so on. Effectively, South contains South West and South East, and
so
on.

Now when you select the combo for a job you can choose a specific
region
(such as South West), or a generic region (such as South.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
I need to store the working regions for a job. Often a job has more
than one region and often it's the same group of regions as with
other
jobs. I could make a separate Group Table but what do I do if the
job
only has one region? If I store that info in the Job Table, I would
store region info in 2 different tables. Adding a separate group for
every job that has one working region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars

.

.