Adding a column to a table based on a command

[Matt Hemsley]

Hello there.

I'm trying to get my database to automatically add a new
field to an existing table with the name bing inputted by
the user.

The name is entered in a text box with the control name
"newname" on a form called "addnewskill", with the table
I'm trying to add to being "Skills"

I've a feeling this is something for SQL to take care of,
but I cannot seem to get the expression to refer to the
command contents.
I have tried ALTER TABLE Skills ADD COLUMN
[Form!addnewskill!newname] TEXT (255);
but this will ot work - I have also tried with many
combinations of brackets and quote marks.

a) Is what I am attepting possible? and if so
b) How do I do it?

Thanks for your time.
Matt

 

[John Vinson]

Hello there.

I'm trying to get my database to automatically add a new
field to an existing table with the name bing inputted by
the user.

This would be a VERY rarely needed feature.

The name is entered in a text box with the control name
"newname" on a form called "addnewskill", with the table
I'm trying to add to being "Skills"

And in this case, it would be A VERY BAD IDEA. Your table design is
simply incorrectly normalized! Storing data - a skill - in a fieldname
is *not* correct.

If you have multiple Skills for each employee, and multiple Employees
for each skill, you have a many to many relationship. You need three
tables:

Employees
EmployeeID
LastName
<etc etc>

Skills
SkillID
Skill <e.g. "Carpentry", "Relational Design">

EmployeeSkills
EmployeeID <link to Employees>
SkillID <link to Skills>
<other info, e.g. level of skill>

Rather than having a new *field* when you have a new skill defined,
you would simply add a record to the Skills table; and rather than
entering a check into one of a large and growing number of yes/no
fields, you would add a record to the EmployeeSkills table, using a
subform.

 

[Matt Hemsley]

-----Original Message-----


This would be a VERY rarely needed feature.


And in this case, it would be A VERY BAD IDEA. Your table design is
simply incorrectly normalized! Storing data - a skill - in a fieldname
is *not* correct.

If you have multiple Skills for each employee, and multiple Employees
for each skill, you have a many to many relationship. You need three
tables:

Employees
EmployeeID
LastName
<etc etc>

Skills
SkillID
Skill <e.g. "Carpentry", "Relational Design">

EmployeeSkills
EmployeeID <link to Employees>
SkillID <link to Skills>
<other info, e.g. level of skill>

Rather than having a new *field* when you have a new skill defined,
you would simply add a record to the Skills table; and rather than
entering a check into one of a large and growing number of yes/no
fields, you would add a record to the EmployeeSkills table, using a
subform.


.

Thanks for that, John. I suspected something upon the same
lines and have started a new design - just trying to save
myself having to re-format masses of data that's already
there.

With the above example, though, as each employee can have
many skills am I right in assuming each employee would
have to have several entries in the "EMPLOYEE SKILLS"
table, or is there a way to have one record for each
employee to avoid this kind of duplication?

Thanks again
M

 

[John Vinson]

Thanks for that, John. I suspected something upon the same
lines and have started a new design - just trying to save
myself having to re-format masses of data that's already
there.

You won't need to reenter data: a "Normalizing Union Query" can
extract the data from your wide-flat table and pull it into the
EmployeeSkills table in one swell foop.

Let's say you have a table with EmployeeID, biographical fields, and
ten Yes/No fields for seven skills (I don't know your business so I'll
just name them Batting, Pitching, Catching, Base Stealing, Running,
Infield, Outfield, and Coaching). You could create an Append query
based on the table to append all the bio data to a new (skill-free)
Employee table, and manually create a Skills table with seven rows,
one for each skill.

You'ld then create a query in the SQL window:

SELECT EmployeeID, (1) AS SkillID
FROM yourtable WHERE [Batting] = True
UNION ALL
SELECT EmployeeID, (2) AS SkillID
FROM yourtable WHERE [Pitching] = True
UNION ALL
SELECT EmployeeID, (3) AS SkillID
FROM yourtable WHERE [Catching] = True
UNION ALL
<etc etc, you get the idea>

Save this Query as qryAllSkills, and then base an Append query upon it
to populate EmployeeSkills.

With the above example, though, as each employee can have
many skills am I right in assuming each employee would
have to have several entries in the "EMPLOYEE SKILLS"
table, or is there a way to have one record for each
employee to avoid this kind of duplication?

You're using a relational database. Use it relationally! This is about
REDUCING duplication, not creating it.

There's an old saying: "Fields are expensive, records are cheap". You
WANT each employee to have *one* record in the Employee table, with
all their biographical data; you want each type of skill to have *one*
record in the Skills table, with a clear description of that skill
(perhaps with a field or fields defining what's meant by 'qualified
forklift operator' or 'advanced relational database designer'); and as
many records as are needed in Employee Skills. These records would not
have ANY duplication - each record would consist of *only two fields*,
an EmployeeID and a SkillID; they should be a joint two-field Primary
Key ensuring that no two records in the EmployeeSkills table are
identical.

 

[TC]

(snip)

in one swell foop.

Was that deliberate?!

TC

 

[John Vinson]

(snip)



Was that deliberate?!

A habitual wordplay (or a wabbitual herd play, one or the other).