Dealing with large matrices (1000's of fields)

[Noesis Strategy]

I am attempting to build a database that profiles about 50 companies in a
single industries. In a standard MS Word format, each profile would be
about 20 pages of dense text and tables. In order to standardize our data
gathering procedures, we want to impose specific buckets for analysts, some
of whom will be temporary but highly paid workers, to enter relevant
information. Ideally, we want know them to know without ambiguity where
information should go, so we are using MS Access. However, each profile
translates into about 1500 fields. Some sections grossly amplify the
number of fields because there is historical information, for a number of
years 1999, 2000, 2001,...2009. How is the best way to deal with this?
For instance, in a simple spreadsheet, there are is straightforward table
like:
Subscribers - revenues - EBITDA - capital investment -
operating expenses
2009...
2001
2000

This would lead to 50 fields out of 1500.

So in each of the of the 50 records, there is a 10 x 5 matrix. Is there a
way to create a table for the ten year entry for each of subscribers,
revenues, EBITDA, etc to make 500 financial history records for all 50
company records. This loses some elegance as then there are additional
table that the next developer/administrator, who will be non-technical MBA,
to decifer.
In other examples, there will be matrices like this without sequential
information
Northeast Midwest Central South Canada Mexico
CEO
SVP
VP1
VP2
VP3
Director1
Director2
Director3
Manager1
Manager2
Manager3
11 x 6 for a total of 66 fields.
Any thoughts on the best approach for each or both scenarios?

[PC Datasheet]

I provide the kind of help Chuck recommends.

I am in business to provide customers with a resource for help with Microsoft
Access, Excel and Word
applications. You can view my website at www.pcdatasheet.com. I offer an MBA
degree as one of my qualifications. Email me at the address below and let me
know if you would like my help.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Noesis Strategy" <no**@none.com> wrote in message
news:40**********************@news.rcn.com...

I am attempting to build a database that profiles about 50 companies in a
single industries. In a standard MS Word format, each profile would be
about 20 pages of dense text and tables. In order to standardize our data
gathering procedures, we want to impose specific buckets for analysts, some
of whom will be temporary but highly paid workers, to enter relevant
information. Ideally, we want know them to know without ambiguity where
information should go, so we are using MS Access. However, each profile
translates into about 1500 fields. Some sections grossly amplify the
number of fields because there is historical information, for a number of
years 1999, 2000, 2001,...2009. How is the best way to deal with this?
For instance, in a simple spreadsheet, there are is straightforward table
like:
Subscribers - revenues - EBITDA - capital investment -
operating expenses
2009...
2001
2000

This would lead to 50 fields out of 1500.

So in each of the of the 50 records, there is a 10 x 5 matrix. Is there a
way to create a table for the ten year entry for each of subscribers,
revenues, EBITDA, etc to make 500 financial history records for all 50
company records. This loses some elegance as then there are additional
table that the next developer/administrator, who will be non-technical MBA,
to decifer.
In other examples, there will be matrices like this without sequential
information
Northeast Midwest Central South Canada Mexico
CEO
SVP
VP1
VP2
VP3
Director1
Director2
Director3
Manager1
Manager2
Manager3
11 x 6 for a total of 66 fields.
Any thoughts on the best approach for each or both scenarios?

[Noesis Strategy]

Chuck,

I have actually spent many hours with constructing simpler MS databases and
with other database tools. It appears that you somewhat misunderstand my
inquiry. I realize the limitations of Access (i.e. 255 fields per table)
and how to relate tables, subforms, etc. Moreover, I fully understand that
forms can present data from a collection of tables. However, I don't see an
elegant way of quickly creating matrices of data both sequential or
nonsequential rows.

thanks,

Noesis Strategy

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:eo********************************@4ax.com...

Step one: Hire someone who knows about databases. From what you've
posted, this really doesn't sound like something you want to design in
a newsgroup setting. Not that it can't be done, but it will take a
*LOT* longer if you go this route.

For what it's worth, you're still thinking in terms of a spreadsheet,
rather then a database. A database is *vastly* different! For one
thing, what it "looks" like usually has nothing at all to do with what
the user sees. Just explaining that could take months in a newsgroup,
which is why you want to hire someone who knows what they are doing.
Try and hire someone local to where ever the heck you are.

On Mon, 1 Mar 2004 17:37:50 -0600, "Noesis Strategy" <no**@none.com>
wrote:

I am attempting to build a database that profiles about 50 companies in a
single industries. In a standard MS Word format, each profile would be
about 20 pages of dense text and tables. In order to standardize our datagathering procedures, we want to impose specific buckets for analysts, someof whom will be temporary but highly paid workers, to enter relevant
information. Ideally, we want know them to know without ambiguity where
information should go, so we are using MS Access. However, each profile
translates into about 1500 fields. Some sections grossly amplify the
number of fields because there is historical information, for a number of
years 1999, 2000, 2001,...2009. How is the best way to deal with this?
For instance, in a simple spreadsheet, there are is straightforward table
like:
Subscribers - revenues - EBITDA - capital investment -
operating expenses
2009...
2001
2000
This would lead to 50 fields out of 1500.
So in each of the of the 50 records, there is a 10 x 5 matrix. Is there away to create a table for the ten year entry for each of subscribers,
revenues, EBITDA, etc to make 500 financial history records for all 50
company records. This loses some elegance as then there are additional
table that the next developer/administrator, who will be non-technical MBA,to decifer.
In other examples, there will be matrices like this without sequential
information
Northeast Midwest Central South Canada Mexico
CEO
SVP
VP1
VP2
VP3
Director1
Director2
Director3
Manager1
Manager2
Manager3
11 x 6 for a total of 66 fields.
Any thoughts on the best approach for each or both scenarios?

--
Bugs Come In Through Open Windows.

[Steve Jorgensen]

Based on your statement, "Some sections grossly amplify the
number of fields because there is historical information, for a number of
years 1999, 2000, 2001,...2009", you are not thinking in terms of relational
design which is why we're saying you need to bone up. In a relational data
model, this information would be entered into a related table (1-M), and in
Access, the records would probably be entered into a subform.

On Mon, 1 Mar 2004 18:26:38 -0600, "Noesis Strategy" <no**@none.com> wrote:

Chuck,

I have actually spent many hours with constructing simpler MS databases and
with other database tools. It appears that you somewhat misunderstand my
inquiry. I realize the limitations of Access (i.e. 255 fields per table)
and how to relate tables, subforms, etc. Moreover, I fully understand that
forms can present data from a collection of tables. However, I don't see an
elegant way of quickly creating matrices of data both sequential or
nonsequential rows.

thanks,

Noesis Strategy

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:eo********************************@4ax.com.. .

Step one: Hire someone who knows about databases. From what you've
posted, this really doesn't sound like something you want to design in
a newsgroup setting. Not that it can't be done, but it will take a
*LOT* longer if you go this route.

For what it's worth, you're still thinking in terms of a spreadsheet,
rather then a database. A database is *vastly* different! For one
thing, what it "looks" like usually has nothing at all to do with what
the user sees. Just explaining that could take months in a newsgroup,
which is why you want to hire someone who knows what they are doing.
Try and hire someone local to where ever the heck you are.

On Mon, 1 Mar 2004 17:37:50 -0600, "Noesis Strategy" <no**@none.com>
wrote:

>I am attempting to build a database that profiles about 50 companies in a
>single industries. In a standard MS Word format, each profile would be
>about 20 pages of dense text and tables. In order to standardize ourdata >gathering procedures, we want to impose specific buckets for analysts,some >of whom will be temporary but highly paid workers, to enter relevant
>information. Ideally, we want know them to know without ambiguity where
>information should go, so we are using MS Access. However, each profile
>translates into about 1500 fields. Some sections grossly amplify the
>number of fields because there is historical information, for a number of
>years 1999, 2000, 2001,...2009. How is the best way to deal with this?
>For instance, in a simple spreadsheet, there are is straightforward table
>like:
> Subscribers - revenues - EBITDA - capital investment -
>operating expenses
>2009...
>2001
>2000
>This would lead to 50 fields out of 1500.
>So in each of the of the 50 records, there is a 10 x 5 matrix. Is therea >way to create a table for the ten year entry for each of subscribers,
>revenues, EBITDA, etc to make 500 financial history records for all 50
>company records. This loses some elegance as then there are additional
>table that the next developer/administrator, who will be non-technicalMBA, >to decifer.
>In other examples, there will be matrices like this without sequential
>information
> Northeast Midwest Central South Canada Mexico
>CEO
>SVP
>VP1
>VP2
>VP3
>Director1
>Director2
>Director3
>Manager1
>Manager2
>Manager3
>11 x 6 for a total of 66 fields.
>Any thoughts on the best approach for each or both scenarios?

--
Bugs Come In Through Open Windows.

[Fletcher Arnold]

"Noesis Strategy" <no**@none.com> wrote in message
news:40**********************@news.rcn.com...

Chuck,

I have actually spent many hours with constructing simpler MS databases and with other database tools. It appears that you somewhat misunderstand my
inquiry. I realize the limitations of Access (i.e. 255 fields per table)
and how to relate tables, subforms, etc. Moreover, I fully understand that
forms can present data from a collection of tables. However, I don't see an elegant way of quickly creating matrices of data both sequential or
nonsequential rows.

thanks,

Noesis Strategy

Have you had any thoughts in what possible directions you might proceed?
As you are no doubt aware, there is a big difference in how the back end
data is actually structured and how it is presented to the user on the
screen, but sticking to the actual data, have you considered a long skinny
table of answers?

The questions would have masked values such as:

100-000-000 = Positions
100-001-000 = CEO
100-001-001 = CEO Northeast
100-001-002 = CEO Midwest
100-001-003 = CEO Central
....
.....
100-006-000 = Director1

100-006-001 = Director 1 Northeast
100-006-002 = Director1 Midwest
100-006-003 = Director1 Central
They could also have an answer value and an answer data type, so perhaps you
might have an answer table with about 300 records for a single survey with
each record (survey answer) having 5 fields per record. You then use the
power of masking techniques to help you retrieve / analyse data.

As a completely separate strand, have you considered what xml might do for
you? I keep meaning to implement this in some sensible way into one of my
solutions, but have not yet found the time.

Fletcher

[Lyle Fairfield]

"Noesis Strategy" <no**@none.com> wrote in
news:40**********************@news.rcn.com:

Chuck,

I have actually spent many hours with constructing simpler MS databases
and with other database tools. It appears that you somewhat
misunderstand my inquiry. I realize the limitations of Access (i.e. 255
fields per table) and how to relate tables, subforms, etc. Moreover, I
fully understand that forms can present data from a collection of
tables. However, I don't see an elegant way of quickly creating
matrices of data both sequential or nonsequential rows.

So hire a professional.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Noesis Strategy]

Well, I figured it out. As I assumed, it was a form/subform issue.
Essentially, I created a HistoricalVector table with the fields:
OperatorID,Year, Subscribers, EBITDA, opex, capex and primary key
IndependentID. The IndependentID is not used on any form but exists
because primary keys do not allow duplicates. OperatorID in the main table
(Primary Key) relates to OperatorID in the HistoricalVector (one to many).
It doesn't appear on the subform, which happens to be sorted by year. This
was the one minor hassle that is now quite clear.

The form wizard's subform generation does the rest. Only one minor issue, in
some cases I will have to enter the field values manually so as to elminate
any error. It would be nice if Access had a Matrix function or automatic
field-value generation for non-sequential data such as
West-Midwest-South-Northeast-Canada-Mexico

"Noesis Strategy" <no**@none.com> wrote in message
news:40**********************@news.rcn.com...

I am attempting to build a database that profiles about 50 companies in a
single industries. In a standard MS Word format, each profile would be
about 20 pages of dense text and tables. In order to standardize our data gathering procedures, we want to impose specific buckets for analysts, some of whom will be temporary but highly paid workers, to enter relevant
information. Ideally, we want know them to know without ambiguity where
information should go, so we are using MS Access. However, each profile
translates into about 1500 fields. Some sections grossly amplify the
number of fields because there is historical information, for a number of
years 1999, 2000, 2001,...2009. How is the best way to deal with this?
For instance, in a simple spreadsheet, there are is straightforward table
like:
Subscribers - revenues - EBITDA - capital investment -
operating expenses
2009...
2001
2000

This would lead to 50 fields out of 1500.

So in each of the of the 50 records, there is a 10 x 5 matrix. Is there a
way to create a table for the ten year entry for each of subscribers,
revenues, EBITDA, etc to make 500 financial history records for all 50
company records. This loses some elegance as then there are additional
table that the next developer/administrator, who will be non-technical MBA, to decifer.
In other examples, there will be matrices like this without sequential
information
Northeast Midwest Central South Canada Mexico
CEO
SVP
VP1
VP2
VP3
Director1
Director2
Director3
Manager1
Manager2
Manager3
11 x 6 for a total of 66 fields.
Any thoughts on the best approach for each or both scenarios?

[Peter Penfold]

"Noesis Strategy" <no**@none.com> wrote in message
news:40**********************@news.rcn.com...

Well, I figured it out. As I assumed, ....

<blah>
<blah>
<snip>

Well you're obviously pretty happy with your own sub-standard solution that
you seem to have come up with a couple of hours later. Perhaps you could
consider what other solutions you could offer yourself before you come here
asking real database designers to waste their time on people like you. As
far as I can tell, you are what we in the UK would call a twat.

[Bas Cost Budde]

Noesis Strategy wrote:

Well, I figured it out. As I assumed, it was a form/subform issue.
Essentially, I created a HistoricalVector table with the fields:
OperatorID,Year, Subscribers, EBITDA, opex, capex and primary key
IndependentID. The IndependentID is not used on any form but exists
because primary keys do not allow duplicates. OperatorID in the main table
(Primary Key) relates to OperatorID in the HistoricalVector (one to many).
It doesn't appear on the subform, which happens to be sorted by year. This
was the one minor hassle that is now quite clear.

The form wizard's subform generation does the rest. Only one minor issue, in
some cases I will have to enter the field values manually so as to elminate
any error. It would be nice if Access had a Matrix function or automatic
field-value generation for non-sequential data such as
West-Midwest-South-Northeast-Canada-Mexico

You can change any text field into a combobox, and base its rowsource on
the existing values. For the field yourField in table yourTable, the
rowsource expression for this combobox is

select distinct yourfield from yourtable where yourfield is not null
order by yourfield

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl