3-dimentional tables ???

[ZBC]

Can you build a 3-dimentional table in Access.
I need a look-up table that is based on 3 pieces of information.

 

[Rebecca Riordan]

All depends on what you mean by "dimensional". A look-up based on three
matching fields is trivial, but somehow I don't think that's what you meant.
Want to describe the situation in a little more detail?

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[ZBC]

I have a situation where I have a 'Rate' which is based on three items:
User (001, 002, 003, ... 125)
Year (e.g. 1995, 1996, ... 2004)
PrimaryType (e.g. LIAB, PROP, ... 8 different types)

Given a User, aYear and a PrimaryType, I need to look up a Rate.
Rate (1200, 200, 300, 900, 45000, ... )

 

[Armen Stein]

I have a situation where I have a 'Rate' which is based on three items:
User (001, 002, 003, ... 125)
Year (e.g. 1995, 1996, ... 2004)
PrimaryType (e.g. LIAB, PROP, ... 8 different types)

Given a User, aYear and a PrimaryType, I need to look up a Rate.
Rate (1200, 200, 300, 900, 45000, ... )

Hi Linda,

You can create a rate table, say tblRate.

It will have foreign key relationships to the User table and the
PrimaryType table. In addition, it will have a Year field, and a Rate
field.

I also recommend that it have its own Primary Key consisting of an
AutoNumber field.

You can create a unique index on the User, PrimaryType and Year fields
so that no duplicates will be allowed.

On a User form, you can create a subform that shows all the Rates for
that User. It would have the PrimaryType in a combobox, the Year, and
the Rate.

Hope this helps,

 

[ZBC]

I'm confused ... will I then have 125 tables?

Hi Linda,

You can create a rate table, say tblRate.

It will have foreign key relationships to the User table and the
PrimaryType table. In addition, it will have a Year field, and a Rate
field.

I also recommend that it have its own Primary Key consisting of an
AutoNumber field.

You can create a unique index on the User, PrimaryType and Year fields
so that no duplicates will be allowed.

On a User form, you can create a subform that shows all the Rates for
that User. It would have the PrimaryType in a combobox, the Year, and
the Rate.

Hope this helps,

 

[Armen Stein]

I'm confused ... will I then have 125 tables?

Hi Linda,

No. I've mentioned only a few tables. They might look something like
this:

[tblUser]
UserKey (AutoNumber primary key)
UserFirstName
UserLastName
TypeKey (Long Integer foreign key)

[tblType]
TypeKey (AutoNumber primary key)
TypeName

[tblRate]
RateKey (AutoNumber primary key)
UserKey (Long Integer foreign key)
TypeKey (Long Integer foreign key)
RateYear
RateAmount

The tblUser table contains a record for each User.
The tblType table contains one record for each Type (you mentioned that
there are about 8 of them).
The tblRate table contains one record for each *combination* of User,
Type and Year. For example, User A for LIAB in 2001 is in one record,
while User B for PROP in 2002 is in another record.

Also, this design would allow you to track rates for multiple types of
coverage for the same User - for example, a User may have both LIAB and
PROP, each in multiple years.

This is a relatively complex design to code in forms and reports. Do
you know a more experienced developer that can help you out?

 

[John Vinson]

Can you build a 3-dimentional table in Access.
I need a look-up table that is based on 3 pieces of information.

You can use up to *ten* fields as a joint Primary Key - in this case,
your table would have these three fields defined as a joint Primary
Key. In some other table you could have the same three fields, and
create a Query linking the two tables on the three fields.

Don't think of it as "a lookup table" though, and ESPECIALLY don't try
to do this in a table datasheet... it *won't* work, and Lookup Fields
are a misdesigned abomination in any case. But a Query joining the two
tables will retrieve the data from this table for any combination of
the three values.

 

[ZBC]

John,

I initially described my problem as needing a 3-Dimentional lookup
table as that seemed like be simplest way to describe what I want to
do. After reading your response, I think it might be better for me to
describe my problem more directly ...

I currently have a query that ties two queries and a table together.
The table contains a Year field (Primary Key) along with 8 PrimaryType
fields, each containing rate information. This essentially constitutes
a 2-Dimentional lookup table ... given a Year and a PrimaryType, I can
lookup the Rate information for any of the 8 PrimaryTypes. Now, I am
asked to add one other variable ... "User" ... there are currently 125
of them contained in another table (2 fields ... User and User#). User#
is contained in the existing Query, but the rates are currently the same
for all users. What was a 2-Dimentional issue now has must be repeated
"with different Rate information" for each user. I sometimes work in
'C' and this would be ... a Year(11) x PrimaryType(8) x User(125) array
containing the Rate information. I am having some problems visualizing
this concept in a query!

After reading your response, I am getting the feeling that a query might
be able to deal with this ... ???
Appreciate any help on this ...

Bob


I have a situation where I have a 'Rate' which is based on three items:
User (001, 002, 003, ... 125)
Year (e.g. 1995, 1996, ... 2004)
PrimaryType (e.g. LIAB, PROP, ... 8 different types)

Given a User, aYear and a PrimaryType, I need to look up a Rate.
Rate (1200, 200, 300, 900, 45000, ... )

 

[John Vinson]

After reading your response, I am getting the feeling that a query might
be able to deal with this ... ???

well... yes.

If you want to look up a rate given three criteria, create a query
with three criteria. It's not really "three dimensional" - it's just
that you need three inputs to locate a record.

You'll just need a table with four fields (your three criteria fields
and the corresponding rate), with 11,000 rows. Not a very big table
actually. If you want to think of it as an Array, you can; but it's
simply a table that you're searching, in Access jargon!

 

[John Nurick]

I'm confused ... will I then have 125 tables?

You won't have 125 Access tables, no. But do you now have separate "rate
cards" for each of your 125 users? Do you want to be able to set the
rate for each PrimaryType for each Year for each User quite
independently of the rate for every other User? If so, 125 Users times 8
PrimaryTypes means you have to store 1000 separate rates for each Year.

Looking things up based on three fields in a table containing thousands
or tens of thousands of records is absolutely no problem for Access. The
table would be like this:

tblRates
User - foreign key into Users table
Year
PrimaryType
Rate
(Primary key of tblRates contains the 3 fields
User, Year and PrimaryType).

But it seems unlikely that you really need to have a separate rate
structure for every user. In most situations with this many users there
would be some sort of categorisation of users and the rates would depend
on the category the user was placed in. So there might be a
tblCategories with categories such as
Senior Partner
Partner
Junior Partner
Senior Consultant
Consultant
Junior Consultant
Research Consultant
Junior Research Consultant
Office Cat
and

tblUsersCategories
User - foreign key into Users table
Category - foreign key into tblCategories

and then tblRates would be
Category - FK into tblCategories
Year
PrimaryType
Rate

and you would use a query joining tblRates and tblUsersCategories to
retrieve the rate for a particular User, PrimaryType and Year.

Does this make things any clearer? (Another possibility is that the
rates are based on formulas of some kind; if so it's usually best to
have Access store the underlying values and formula(s) and calculate
each actual rate "on the fly" whenever it's needed.)

[snip]

 

[ZBC]

Thank You!

well... yes.

If you want to look up a rate given three criteria, create a query
with three criteria. It's not really "three dimensional" - it's just
that you need three inputs to locate a record.

You'll just need a table with four fields (your three criteria fields
and the corresponding rate), with 11,000 rows. Not a very big table
actually. If you want to think of it as an Array, you can; but it's
simply a table that you're searching, in Access jargon!

 

[ZBC]

You won't have 125 Access tables, no. But do you now have separate "rate
cards" for each of your 125 users?

I currently have only one rate table (for all users) that varies by
year. There now seems to be a need to have separate rates for each user
and the rates will change each year ... This is the basis of my original
question.

Do you want to be able to set the
rate for each PrimaryType for each Year for each User quite
independently of the rate for every other User? If so, 125 Users times 8
PrimaryTypes means you have to store 1000 separate rates for each Year.

Looking things up based on three fields in a table containing thousands
or tens of thousands of records is absolutely no problem for Access. The
table would be like this:

tblRates
User - foreign key into Users table
Year
PrimaryType
Rate
(Primary key of tblRates contains the 3 fields
User, Year and PrimaryType).

But it seems unlikely that you really need to have a separate rate
structure for every user. In most situations with this many users there
would be some sort of categorisation of users and the rates would depend
on the category the user was placed in. So there might be a
tblCategories with categories such as
Senior Partner
Partner
Junior Partner
Senior Consultant
Consultant
Junior Consultant
Research Consultant
Junior Research Consultant
Office Cat
and

tblUsersCategories
User - foreign key into Users table
Category - foreign key into tblCategories

and then tblRates would be
Category - FK into tblCategories
Year
PrimaryType
Rate

and you would use a query joining tblRates and tblUsersCategories to
retrieve the rate for a particular User, PrimaryType and Year.

Does this make things any clearer? (Another possibility is that the
rates are based on formulas of some kind; if so it's usually best to
have Access store the underlying values and formula(s) and calculate
each actual rate "on the fly" whenever it's needed.)

[snip]

 

[ZBC]

I'm confused ... will I then have 125 tables?

Armen Stein wrote:

Hi Linda,

No. I've mentioned only a few tables. They might look something like
this:

[tblUser]
UserKey (AutoNumber primary key)
UserFirstName
UserLastName
TypeKey (Long Integer foreign key)

[tblType]
TypeKey (AutoNumber primary key)
TypeName

[tblRate]
RateKey (AutoNumber primary key)
UserKey (Long Integer foreign key)
TypeKey (Long Integer foreign key)
RateYear
RateAmount

The tblUser table contains a record for each User.
The tblType table contains one record for each Type (you mentioned that
there are about 8 of them).
The tblRate table contains one record for each *combination* of User,
Type and Year. For example, User A for LIAB in 2001 is in one record,
while User B for PROP in 2002 is in another record.

Also, this design would allow you to track rates for multiple types of
coverage for the same User - for example, a User may have both LIAB and
PROP, each in multiple years.

This is a relatively complex design to code in forms and reports. Do
you know a more experienced developer that can help you out?

Only you guys (and gals)! ... If it were not for these newgroups, I
would still be in the starting block!
Thanks!