Joining m-to-m Junction Tables for N-Dim queries

Fair point, Amy, b/c I have been vague up until this point, hoping I
wouldn't have to get too into it.

I'm working this within a defense/intel context. So, yes, certain people
work with specific info types as their area of analysis. Similarly, certain
topics apply to certain countries. Certain infotypes lend themselves to
different topics and countries. Etc. I'm building the database to show how
much time/effort is being spent per variable. I want to ultimately create a
4-d query (kind of like a 4-d cross-tab) of each slice I take, further
parsing and drilling into the data.

E.g.,

For a given topic, how much effort per country, per infotype, and per person.
For a given person, how much effort per country, per infotype and per topic.
For a given infotype, how much effort per person, per topic, and per country.
For a given country, how much effort per person, per topic, and per infotype.

I apologize for leaving this out, especially if this context was crucial to
my framing of the problem. Happy to answer any follow-up questions.

Regards,
Keith


"Amy Blankenship" wrote:

Could you describe this in more detail? For instance, does the person
ACTUALLY have infotypes, or is it the topic related to a person that has
infotypes? I can't for the life of me see where a person could have as a
characteristic an infotype.

Why would a topic have a country? Maybe if you went more into detail about
the actual purpose of your database, we can help you find the best design
for it, but with you talking in the abstract, it doesn't make much sense.

-Amy

"Keith McCarron" wrote in message
...
Jeff, John - appreciate you continuing to engage. I fear you think your
advice is falling on deaf ears, so let me be clear about what I've taken
away. You've advised to think, independent of Access, about the
relationships
I have and how they work in real world. Good and necessary step. Here's
what
I have convinced myself of:

I have 4 variables all inter-related: Person, country, topic, info type
and,
per Jeff's list ...
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes
Similarly ...
A topic can have 1-m Countries
A topic can have 1-m People
A topic can have 1-m InfoTypes
etc.

In my real world situation, it is reasonable for me to slice my entire
data
set by any of these 4. I began the databasing process by creating junction
tables of the pairwise permutations of each of these 4 tables. My question
boils down to whether a junction tbl of junction tbls is necessary to
complete matching my real world scenario to the database structure.

(Of course, the easy answer is to try both ways!)

As I've never had to consider anything more complicated than a two-table
junction table in Access, my intuition is not serving me well here for a
4-table junction case.

Hope you are having a great day.

Keith

I'm not ignoring your response, I'm thinking further.

-Amy

"Keith McCarron" wrote in message
...
Fair point, Amy, b/c I have been vague up until this point, hoping I
wouldn't have to get too into it.

I'm working this within a defense/intel context. So, yes, certain people
work with specific info types as their area of analysis. Similarly,
certain
topics apply to certain countries. Certain infotypes lend themselves to
different topics and countries. Etc. I'm building the database to show how
much time/effort is being spent per variable. I want to ultimately create
a
4-d query (kind of like a 4-d cross-tab) of each slice I take, further
parsing and drilling into the data.

E.g.,

For a given topic, how much effort per country, per infotype, and per
person.
For a given person, how much effort per country, per infotype and per
topic.
For a given infotype, how much effort per person, per topic, and per
country.
For a given country, how much effort per person, per topic, and per
infotype.

I apologize for leaving this out, especially if this context was crucial
to
my framing of the problem. Happy to answer any follow-up questions.

Regards,
Keith


"Amy Blankenship" wrote:

Could you describe this in more detail? For instance, does the person
ACTUALLY have infotypes, or is it the topic related to a person that has
infotypes? I can't for the life of me see where a person could have as a
characteristic an infotype.

Why would a topic have a country? Maybe if you went more into detail
about
the actual purpose of your database, we can help you find the best design
for it, but with you talking in the abstract, it doesn't make much sense.

-Amy

"Keith McCarron" wrote in
message
...
Jeff, John - appreciate you continuing to engage. I fear you think your
advice is falling on deaf ears, so let me be clear about what I've
taken
away. You've advised to think, independent of Access, about the
relationships
I have and how they work in real world. Good and necessary step. Here's
what
I have convinced myself of:

I have 4 variables all inter-related: Person, country, topic, info type
and,
per Jeff's list ...
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes
Similarly ...
A topic can have 1-m Countries
A topic can have 1-m People
A topic can have 1-m InfoTypes
etc.

In my real world situation, it is reasonable for me to slice my entire
data
set by any of these 4. I began the databasing process by creating
junction
tables of the pairwise permutations of each of these 4 tables. My
question
boils down to whether a junction tbl of junction tbls is necessary to
complete matching my real world scenario to the database structure.

(Of course, the easy answer is to try both ways!)

As I've never had to consider anything more complicated than a
two-table
junction table in Access, my intuition is not serving me well here for
a
4-table junction case.

Hope you are having a great day.

Keith

No worries. I can only be appreciative of that!

Keith

"Amy Blankenship" wrote:

I'm not ignoring your response, I'm thinking further.

-Amy

"Keith McCarron" wrote in message
...
Fair point, Amy, b/c I have been vague up until this point, hoping I
wouldn't have to get too into it.

I'm working this within a defense/intel context. So, yes, certain people
work with specific info types as their area of analysis. Similarly,
certain
topics apply to certain countries. Certain infotypes lend themselves to
different topics and countries. Etc. I'm building the database to show how
much time/effort is being spent per variable. I want to ultimately create
a
4-d query (kind of like a 4-d cross-tab) of each slice I take, further
parsing and drilling into the data.

E.g.,

For a given topic, how much effort per country, per infotype, and per
person.
For a given person, how much effort per country, per infotype and per
topic.
For a given infotype, how much effort per person, per topic, and per
country.
For a given country, how much effort per person, per topic, and per
infotype.

I apologize for leaving this out, especially if this context was crucial
to
my framing of the problem. Happy to answer any follow-up questions.

Regards,
Keith


"Amy Blankenship" wrote:

Could you describe this in more detail? For instance, does the person
ACTUALLY have infotypes, or is it the topic related to a person that has
infotypes? I can't for the life of me see where a person could have as a
characteristic an infotype.

Why would a topic have a country? Maybe if you went more into detail
about
the actual purpose of your database, we can help you find the best design
for it, but with you talking in the abstract, it doesn't make much sense.

-Amy

"Keith McCarron" wrote in
message
...
Jeff, John - appreciate you continuing to engage. I fear you think your
advice is falling on deaf ears, so let me be clear about what I've
taken
away. You've advised to think, independent of Access, about the
relationships
I have and how they work in real world. Good and necessary step. Here's
what
I have convinced myself of:

I have 4 variables all inter-related: Person, country, topic, info type
and,
per Jeff's list ...
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes
Similarly ...
A topic can have 1-m Countries
A topic can have 1-m People
A topic can have 1-m InfoTypes
etc.

In my real world situation, it is reasonable for me to slice my entire
data
set by any of these 4. I began the databasing process by creating
junction
tables of the pairwise permutations of each of these 4 tables. My
question
boils down to whether a junction tbl of junction tbls is necessary to
complete matching my real world scenario to the database structure.

(Of course, the easy answer is to try both ways!)

As I've never had to consider anything more complicated than a
two-table
junction table in Access, my intuition is not serving me well here for
a
4-table junction case.

Hope you are having a great day.

Keith

OK, here's what I think. You actually want to sum up the "efforts" that
combine to become the level of effort for a specific combination.

So what you actually need is an "efforts" table that allows you to insert
the key for each possible thing the effort could apply to, where you insert
the primary key of one or more of the fields to that particular effort. If
the level of effort will only make sense in certain combinations, your forms
could validate those combinations. So, for instance, if the level of effort
for a country makes no sense unless it also applies to a topic and
information type, you'd want to insist on at least that amount of data. If
it makes no sense without a person to apply to, you should require all.

Then you could use queries to sum the individual efforts any way you need.

Now, you might need additional relationships defined to make your forms
function correctly. For instance, if one person can only have InfoType A or
InfoType B, then a table containing that relationship would allow you to
filter infotype based on person. Now, if that person can only have InfoType
A in country A or InfoType B in country b, you might want to have a table
that defines that relationship. However, the more relationships you define
like this, the more data entry is needed, to define what is possible in a
combobox on a form. So you get accuracy, but you add data entry headaches.

Helps?

-Amy

"Keith McCarron" wrote in message
...
Fair point, Amy, b/c I have been vague up until this point, hoping I
wouldn't have to get too into it.

I'm working this within a defense/intel context. So, yes, certain people
work with specific info types as their area of analysis. Similarly,
certain
topics apply to certain countries. Certain infotypes lend themselves to
different topics and countries. Etc. I'm building the database to show how
much time/effort is being spent per variable. I want to ultimately create
a
4-d query (kind of like a 4-d cross-tab) of each slice I take, further
parsing and drilling into the data.

E.g.,

For a given topic, how much effort per country, per infotype, and per
person.
For a given person, how much effort per country, per infotype and per
topic.
For a given infotype, how much effort per person, per topic, and per
country.
For a given country, how much effort per person, per topic, and per
infotype.

I apologize for leaving this out, especially if this context was crucial
to
my framing of the problem. Happy to answer any follow-up questions.

Regards,
Keith


"Amy Blankenship" wrote:

Could you describe this in more detail? For instance, does the person
ACTUALLY have infotypes, or is it the topic related to a person that has
infotypes? I can't for the life of me see where a person could have as a
characteristic an infotype.

Why would a topic have a country? Maybe if you went more into detail
about
the actual purpose of your database, we can help you find the best design
for it, but with you talking in the abstract, it doesn't make much sense.

-Amy

"Keith McCarron" wrote in
message
...
Jeff, John - appreciate you continuing to engage. I fear you think your
advice is falling on deaf ears, so let me be clear about what I've
taken
away. You've advised to think, independent of Access, about the
relationships
I have and how they work in real world. Good and necessary step. Here's
what
I have convinced myself of:

I have 4 variables all inter-related: Person, country, topic, info type
and,
per Jeff's list ...
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes
Similarly ...
A topic can have 1-m Countries
A topic can have 1-m People
A topic can have 1-m InfoTypes
etc.

In my real world situation, it is reasonable for me to slice my entire
data
set by any of these 4. I began the databasing process by creating
junction
tables of the pairwise permutations of each of these 4 tables. My
question
boils down to whether a junction tbl of junction tbls is necessary to
complete matching my real world scenario to the database structure.

(Of course, the easy answer is to try both ways!)

As I've never had to consider anything more complicated than a
two-table
junction table in Access, my intuition is not serving me well here for
a
4-table junction case.

Hope you are having a great day.

Keith

Amy,

I have to chew on that a bit. You actually have several ideas in there, so I
may try several of them. I'm not at the form making stage yet, as I'm doing
all of the data entry myself and find the tables easier, for now. I've
resigned myself to a lot of data entry. As I complete each table, I'll check
to see what conclusions I can draw from the current level of joining. If
you're right, I'll ultimately be combining all of the "effort" contribution
tables into one.

Thanks for noodling this and offering some ideas. I guess I have to bring it
home now!

Keith

"Amy Blankenship" wrote:

OK, here's what I think. You actually want to sum up the "efforts" that
combine to become the level of effort for a specific combination.

So what you actually need is an "efforts" table that allows you to insert
the key for each possible thing the effort could apply to, where you insert
the primary key of one or more of the fields to that particular effort. If
the level of effort will only make sense in certain combinations, your forms
could validate those combinations. So, for instance, if the level of effort
for a country makes no sense unless it also applies to a topic and
information type, you'd want to insist on at least that amount of data. If
it makes no sense without a person to apply to, you should require all.

Then you could use queries to sum the individual efforts any way you need.

Now, you might need additional relationships defined to make your forms
function correctly. For instance, if one person can only have InfoType A or
InfoType B, then a table containing that relationship would allow you to
filter infotype based on person. Now, if that person can only have InfoType
A in country A or InfoType B in country b, you might want to have a table
that defines that relationship. However, the more relationships you define
like this, the more data entry is needed, to define what is possible in a
combobox on a form. So you get accuracy, but you add data entry headaches.

Helps?

-Amy

You're welcome :-)

-Amy

"Keith McCarron" wrote in message
news
Amy,

I have to chew on that a bit. You actually have several ideas in there, so
I
may try several of them. I'm not at the form making stage yet, as I'm
doing
all of the data entry myself and find the tables easier, for now. I've
resigned myself to a lot of data entry. As I complete each table, I'll
check
to see what conclusions I can draw from the current level of joining. If
you're right, I'll ultimately be combining all of the "effort"
contribution
tables into one.

Thanks for noodling this and offering some ideas. I guess I have to bring
it
home now!

Keith

"Amy Blankenship" wrote:

OK, here's what I think. You actually want to sum up the "efforts" that
combine to become the level of effort for a specific combination.

So what you actually need is an "efforts" table that allows you to insert
the key for each possible thing the effort could apply to, where you
insert
the primary key of one or more of the fields to that particular effort.
If
the level of effort will only make sense in certain combinations, your
forms
could validate those combinations. So, for instance, if the level of
effort
for a country makes no sense unless it also applies to a topic and
information type, you'd want to insist on at least that amount of data.
If
it makes no sense without a person to apply to, you should require all.

Then you could use queries to sum the individual efforts any way you
need.

Now, you might need additional relationships defined to make your forms
function correctly. For instance, if one person can only have InfoType A
or
InfoType B, then a table containing that relationship would allow you to
filter infotype based on person. Now, if that person can only have
InfoType
A in country A or InfoType B in country b, you might want to have a table
that defines that relationship. However, the more relationships you
define
like this, the more data entry is needed, to define what is possible in a
combobox on a form. So you get accuracy, but you add data entry
headaches.

Helps?

-Amy

Amy's right: you probably need to take a step back and think even harder
about the subset of the real world that you are modelling and the
entities you'll need.

So far it sounds as if you have "real" entities including
People
Topics
Countries

Maybe you also need an entity such as "program" or "project" or
"investigation"; a Program might involve one or more topics, zero or
more countries, and zero or more people.

I'm not sure whether "effort" is an entity in its own right or merely an
attribute of a relationship: e.g. if Jim is spending all his time on
Project X the fact would be stored in a record in ProjectsAndPeople:

Project, Person, Effort, StartDate, EndDate
X, Jim, 100%, ...


On Mon, 26 Sep 2005 11:33:03 -0700, "Keith McCarron"
wrote:

Fair point, Amy, b/c I have been vague up until this point, hoping I
wouldn't have to get too into it.

I'm working this within a defense/intel context. So, yes, certain people
work with specific info types as their area of analysis. Similarly, certain
topics apply to certain countries. Certain infotypes lend themselves to
different topics and countries. Etc. I'm building the database to show how
much time/effort is being spent per variable. I want to ultimately create a
4-d query (kind of like a 4-d cross-tab) of each slice I take, further
parsing and drilling into the data.

E.g.,

For a given topic, how much effort per country, per infotype, and per person.
For a given person, how much effort per country, per infotype and per topic.
For a given infotype, how much effort per person, per topic, and per country.
For a given country, how much effort per person, per topic, and per infotype.

I apologize for leaving this out, especially if this context was crucial to
my framing of the problem. Happy to answer any follow-up questions.

Regards,
Keith


"Amy Blankenship" wrote:

Could you describe this in more detail? For instance, does the person
ACTUALLY have infotypes, or is it the topic related to a person that has
infotypes? I can't for the life of me see where a person could have as a
characteristic an infotype.

Why would a topic have a country? Maybe if you went more into detail about
the actual purpose of your database, we can help you find the best design
for it, but with you talking in the abstract, it doesn't make much sense.

-Amy

"Keith McCarron" wrote in message
...
Jeff, John - appreciate you continuing to engage. I fear you think your
advice is falling on deaf ears, so let me be clear about what I've taken
away. You've advised to think, independent of Access, about the
relationships
I have and how they work in real world. Good and necessary step. Here's
what
I have convinced myself of:

I have 4 variables all inter-related: Person, country, topic, info type
and,
per Jeff's list ...
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes
Similarly ...
A topic can have 1-m Countries
A topic can have 1-m People
A topic can have 1-m InfoTypes
etc.

In my real world situation, it is reasonable for me to slice my entire
data
set by any of these 4. I began the databasing process by creating junction
tables of the pairwise permutations of each of these 4 tables. My question
boils down to whether a junction tbl of junction tbls is necessary to
complete matching my real world scenario to the database structure.

(Of course, the easy answer is to try both ways!)

As I've never had to consider anything more complicated than a two-table
junction table in Access, my intuition is not serving me well here for a
4-table junction case.

Hope you are having a great day.

Keith




--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.