question: automatically generating field names from record values

[HD]

Hello. For the following, I would appreciate if anyone could tell me:
if it can be done, how it might done, and/or what search terms I could
use to find the solution myself.

I would like to create a database in which _adding a new record
automatically creates a new field in the table_ (ideally, deleting a
record would also delete the field that was created when that record
was added). Thus, the number of fields in the table would always
equal the number of records. In essence, I want Access to
automatically generate a crosstab, but one that will allow for data
entry (and thus, a crosstab query will not work for my purposes - at
least not as I understand them).

Here is a hypothetical example. Suppose I have a database table that
contains a list of employee names:

Mary
Ed
John
Rick
Kathy

And suppose that I would like to create a table that will allow me to
specify which employees work well with each other, via a simple yes/no
entry. Ultimately, I want a table that will look like this:

Mary Ed John Rick Kathy

Mary - y n y y

Ed (y) - y y y

John (n) (y) - n n

Rick (y) (y) (n) - y

Kathy (y) (y) (n) (y) -
The values in parentheses would (ideally) be automatically generated,
as they have already been entered above the diagonal.

Any ideas? It has occurred to me that perhaps I do not need to
"generate" the field names from values. If it is possible to
"autonumber" field names (i.e., Employee1, Employee2, Employee3...),
perhaps I could have the field names correspond to an autonumber
field, without actually trying to generate column names from field
values.

I would appreciate any advice you can offer about how I might
accomplish this without having to manually add and delete fields each
time I add and delete the corresponding record.

Thanks.

HD

[XMVP]

"HD" <he******@yahoo.com> wrote in message
news:cc**************************@posting.google.c om...

Hello. For the following, I would appreciate if anyone could tell me:
if it can be done, how it might done, and/or what search terms I could
use to find the solution myself.

I would like to create a database in which _adding a new record
automatically creates a new field in the table_ (ideally, deleting a
record would also delete the field that was created when that record
was added). Thus, the number of fields in the table would always
equal the number of records. In essence, I want Access to
automatically generate a crosstab, but one that will allow for data
entry (and thus, a crosstab query will not work for my purposes - at
least not as I understand them).

Here is a hypothetical example. Suppose I have a database table that
contains a list of employee names:

Mary
Ed
John
Rick
Kathy

And suppose that I would like to create a table that will allow me to
specify which employees work well with each other, via a simple yes/no
entry. Ultimately, I want a table that will look like this:

Mary Ed John Rick Kathy

Mary - y n y y

Ed (y) - y y y

John (n) (y) - n n

Rick (y) (y) (n) - y

Kathy (y) (y) (n) (y) -
The values in parentheses would (ideally) be automatically generated,
as they have already been entered above the diagonal.

Any ideas? It has occurred to me that perhaps I do not need to
"generate" the field names from values. If it is possible to
"autonumber" field names (i.e., Employee1, Employee2, Employee3...),
perhaps I could have the field names correspond to an autonumber
field, without actually trying to generate column names from field
values.

I would appreciate any advice you can offer about how I might
accomplish this without having to manually add and delete fields each
time I add and delete the corresponding record.

Thanks.

HD

You're joking, right? If not, send your question to:

ac***********@hotmail.com

[Tony Toews]

he******@yahoo.com (HD) wrote:

I would like to create a database in which _adding a new record
automatically creates a new field in the table_ (ideally, deleting a
record would also delete the field that was created when that record
was added). Thus, the number of fields in the table would always
equal the number of records. In essence, I want Access to
automatically generate a crosstab, but one that will allow for data
entry (and thus, a crosstab query will not work for my purposes - at
least not as I understand them).

Here is a hypothetical example. Suppose I have a database table that
contains a list of employee names:

Mary
Ed
John
Rick
Kathy

And suppose that I would like to create a table that will allow me to
specify which employees work well with each other, via a simple yes/no
entry. Ultimately, I want a table that will look like this:

Mary Ed John Rick Kathy

Mary - y n y y

Ed (y) - y y y

John (n) (y) - n n

Rick (y) (y) (n) - y

Kathy (y) (y) (n) (y) -
You really should be using two tables for this purpose. One containing the employee
names and another containg two employee IDs along with other data such as your y/n.

Your report can be generated by programmatically creating the cross tab query and
updating the heading labels on the report. It's a bit of work requiring VBA
knowledge but doable.
I would appreciate any advice you can offer about how I might
accomplish this without having to manually add and delete fields each
time I add and delete the corresponding record.

If you really, really, want to do this see the TempTables.MDB page at my website
which illustrates how to use a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm This will have the necessary code to
create fields.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"XMVP" <ac***********@hotmail.com> wrote:

X-DMCA-Complaints-To: ab***@vnet-inc.com

Please ignore the above posting and others which are somewhat bizarre.

Note to persons new to this newsgroup. One person, with many identities, is being
rather disruptive. If you see a bizarre posting it's probably the work of this
disruptive person..

Check the headers of the posting. If you see the following the posting likely can be
ignored. Of course, there will likely be other headers to be added to this list.

Comments: This message did not originate from the Sender address above.
It was remailed automatically by anonymizing remailer software.
or
X-Abuse-Report: ab***@teranews.com
or
Organization: Posted via Supernews, http://www.supernews.com
or
Organization: 100ProofNews.com - Unlimited Downloads - $8.95/Month
or
X-Complaints-To: ab***@vnet-inc.com
or
Message-ID: <something>.nntpserver.com

You can also change your NewsReader program settings to ignore off-topic posts. See
http://www.hyphenologist.co.uk/killfile/ for more information.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[XMVP]

If you really, really, want to do this see the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm This will have the necessary code to create fields.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Please ignore the illegal advertising by the above poster and other
violations of this newsgroup's charter.

Tony is no longer an Access MVP or a member of this newsgroup.

[Tony Toews]

"XMVP" <ac***********@hotmail.com> wrote:

Tony is no longer an Access MVP or a member of this newsgroup.

Rubbish.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[John Winterbottom]

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:uo********************************@4ax.com...

You really should be using two tables for this purpose. One containing the employee names and another containg two employee IDs along with other data such as

your y/n.
I wonder how to write the ddl for this. If you do this:-

create table employees
(
empID int not null primary key,
empName varchar(255) not null unique
)

create table employeeCompatabilties
(
empID int not null references employees(empID),
empID2 int not null references employees(empID),
isCompatible char(1) not null,
primary key (empID, empID2),
check (isCompatible in ('y', 'n'))
)
Then you could have the situation where John is compatible with Mary but
Mary is not compatible with John. Well, maybe that could be true :)

Anyway, something to think about..