relating rows to columns

[KWhamill]

I was wondering if anybody has done this or wanted to do this. I want to
relate row headings in table a to Column headings in table b. or put another
way I have a primary key in one table i want to use for column headings in
another table. I can create the tables in Excel and then import them as
tables to Access but i can't Relate them. any ideas?

 

[Jeff Boyce]

You may be misconstruing how a well-normalized relational database operates.

For one thing, it would be a very poor design idea to generate "Column
headings in table B" from data in rows of table A.

And when you say "row headings", in Access there's nothing comparable to the
row headings you'd see in Excel.

Excel is a spreadsheet.

Access is a relational database.

Access is NOT a "spreadsheet on steroids".

To get the best of Access' features and functions, you'll need to understand
(and implement) relational table design and normalization.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

A second thought...

You've described "how" you are trying to do something, but not what that
something is.

If you'll explain a bit more about the underlying business need (i.e., the
"what" and "why"), folks here in the newsgroup may be able to offer
alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jaeger]

You might be able to achieve what I think you want to see by doing a crosstab
query on your main table. I had to handle parent-teacher conference
scheduling for several years and we had a table with these fields: TeacherID
(primary key), ConfTime, ParentID. The crosstab query produced a scheduling
grid of rows with TeacherID and columns of ConfTime. The ParentID was in the
cell. As parents called in, they were assigned an ID, we viewed a filtered
grid for the teachers they wanted, and we put in their ParentID in a form
based on the main table. It was easy to see the openings possible for their
meeting times using the crosstab grid.

So consider whether a crosstab query will work to do the view you want of
the main table.

 

[Fred]

Again, we'd need to know what you want to do.

But a couple thoughts if you question is taken literally.

Databases are collections of data where the contents of the cells in a
horizontal row are related. And links between tables connect related data,
which inherently means horizontal rows to horizontal rows. Which means that
linking is inherent from fields to fields. These conventions are what make
the concept useful enough to be running the world in this area.

My first thought when hearing your question is that if you have such a need
that one of your tables was built 90 degrees out of proper rotation, and
needs a 90 degree rotation in order to make it a real database table. Again,
just a vague thought without actually knowing your application.

 

[Jaeger]

Sorry for the error--the table with TeacherID, ConfTime and ParentID, had an
autonumber ID and TeacherID was a foreign key.

 

[KWhamill]

Ok Ideally I would to be able to update this table very quickly adding new
columns and new rows using about as much sophistication as is available in an
append query. I've used access long enough to know it won't do this unless I
rebuild the table everytime. so I figured that i can approach this in a
layered fashion, by relating the primary key in one table to the column
heading in another table. The one containing the static data the other
containing that data which needs daily updateing. The tables would be small
enough to be considered manageable.
I'm certain that there is some clever solution to this conundrum.

 

[Jeff Boyce]

Again, you are (still) describing a "how" ("using primary key ... column
heading in another table ...").

I was asking about the business itself. Are we talking about auto parts,
school class registrations, contracts managements, or what?

A well-normalized relational database won't need to "add columns" ... the
design will allow the addition of new data as new rows in already
established tables.

Let me offer an example of wrong and right approaches...

You could be storing individual store/outlet sales figures for 16 different
products for each month of the year. A common way to try to handle this is
to use "January", "February", "March", ... as column names. When the end of
the year is reached, new columns would be added for "January2009",
"February2009", ...And there might even be a table-per-store.

This is pretty much what you would have to do ... if you were limited to
using a spreadsheet.

Access is not a spreadsheet!

Another approach would be to create a table as follows:

tblSalesFigure
StoreID
ProductID
SalesDate
SalesTotal

This table of data can "add another month" or "add another store" or "add
another Product" by adding another record.

If you'll provide more specific descriptions of the domain in which you are
working, and examples of the kind of data you are already keeping and
considering "adding another column" to incorporate...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KWhamill]

we are talking about contracts, everymonth we take a "position" or mark the
value of those contracts. Almost everymonth, i add new contracts & on fewer
Months I close them which means i no longer track them. So i need to be able
to add and remove contracts and add positions. Which is why i say that i need
a table that can expand in both numbers of rows and columns. or back to my
original question, i need a way to relate the primary key in one table with
the columns in another table. And it needs to be easy to maintain because i
will not be maintaining it.

Respectfully,
Karl

 

[John W. Vinson]

we are talking about contracts, everymonth we take a "position" or mark the
value of those contracts. Almost everymonth, i add new contracts & on fewer
Months I close them which means i no longer track them. So i need to be able
to add and remove contracts and add positions. Which is why i say that i need
a table that can expand in both numbers of rows and columns. or back to my
original question, i need a way to relate the primary key in one table with
the columns in another table. And it needs to be easy to maintain because i
will not be maintaining it.

So... Each Contract has zero, one or more Positions, and each position has a
date as an attribute.

You can do this with two permanent, unchanging tables:

Contracts
ContractNo <Primary Key>
InitialDate <Date/Time>
TerminatedDate <date/time, NULL for currently active contracts>
<descriptive information about the contract as an entity>

Positions
PositionID <Primary Key>
ContractNo <link to Contracts>
PositionDate <date/time>
Valuation <currency, value of this contract as of this date>
<any other information pertaining to this position>


A Query selecting contracts with a non-NULL TerminatedDate will show active
contracts (a date criterion will let you retain a historical record of past
contracts).

Each Contract can be linked to any desired number of positions; you can add a
position at any point in time, search for it, etc.

"Fields are expensive. Records are cheap". It is NOT necessary - in fact it's
not a good idea and not good design! - to add a new column every time you have
a new position, or a new contract.

You're using a relational database - use it relationally!

 

[Jeff Boyce]

John said it much better than I could.

If you want to use Access and get good use of the relationally-oriented
features and functions, DON'T "add another column".

Regards

Jeff Boyce
Microsoft Office/Access MVP