Impossible query?

  • Thread starter Thread starter gary b
  • Start date Start date
G

gary b

I have searched the newsgroups for a solution to no avail. Perhaps
this is not possible?

I have a large table (tblBTE)including [but not limited to] the
following fields: FirstName, LastName, CatName, dBDev, dBDesign, -->
and approximately 30 more skill names. This table was created by
importing data from an Excel spreadsheet. Each record lists
FirstName, LastName, CatName and a numerical proficiency value for the
skill names used as field names. Example:
ID FirstName LastName CatName dBDesign dBdev dBData --->
1 John Smith Core 1 0 2 --->
2 Mary Jones SME 0 2 0 --->
3 Ted Martin Core 2 0 1 --->
4 Alfred Newman SME 0 3 0 --->
Notice that (skill) fields are associated with only one Skill Category
name (Core, SME, or Mgmt). NO field has values for than one type of
Category.

I have created an Access relational database that specifies the
following relationships between the three tables and their fields:

tblEmpl tblCat tblSkill
SkillID (auto)
CatID (auto) 1 - M >>>> CatID (long int)
EmplID (auto) 1 - M >>>>>> EmplID long int EmplID
FirstName FirstName FirstName
LastName LastName LastName
CatName CatName
SkillName
Proficiency

What I want to do is extract the numerical proficiency values from
tblBTE and place them in tblSkill -- keeping the integrity of the
data. [That is, the VALUE for the FIELD titled dBData in tblBTE needs
to be placed in Proficieny field of tblSkill for the RECORD
corresponding to firstName, LastName, and SkillName(=dBData).

The fact that the skillnames (in tblBTE) are FIELD names has me
stumped. Can anyone tell me how to extract BOTH the field name
(=SkillName) AND the numerical value from tblBTE for each individual
listed in the database??

Thanks for the bandwidth.

gary b
 
Dear Gary:

First, have a query like this:

SELECT ID, FirstName, LastName, CatName,
"dBDesign" AS SkillName, dBDesign AS Proficiency
FROM tblBTE
WHERE dBDesign <> 0

Take a look at the results. Now let's write another:

SELECT ID, FirstName, LastName, CatName,
"dBdev" AS SkillName, dBdev AS Proficiency
FROM tblBTE
WHERE dBdev <> 0

You will need 30 queries like this.

Now look what happens when you put them together like this:

SELECT ID, FirstName, LastName, CatName,
"dBDesign" AS SkillName, dBDesign AS Proficiency
FROM tblBTE
WHERE dBDesign <> 0
UNION ALL
SELECT ID, FirstName, LastName, CatName,
"dBdev" AS SkillName, dBdev AS Proficiency
FROM tblBTE
WHERE dBdev <> 0

Is that what you wanted? Or close?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have searched the newsgroups for a solution to no avail. Perhaps
this is not possible?

I have a large table (tblBTE)including [but not limited to] the
following fields: FirstName, LastName, CatName, dBDev, dBDesign, -->
and approximately 30 more skill names. This table was created by
importing data from an Excel spreadsheet. Each record lists
FirstName, LastName, CatName and a numerical proficiency value for the
skill names used as field names. Example:
ID FirstName LastName CatName dBDesign dBdev dBData --->
1 John Smith Core 1 0 2 --->
2 Mary Jones SME 0 2 0 --->
3 Ted Martin Core 2 0 1 --->
4 Alfred Newman SME 0 3 0 --->
Notice that (skill) fields are associated with only one Skill Category
name (Core, SME, or Mgmt). NO field has values for than one type of
Category.

I have created an Access relational database that specifies the
following relationships between the three tables and their fields:

tblEmpl tblCat tblSkill
SkillID (auto)
CatID (auto) 1 - M >>>> CatID (long int)
EmplID (auto) 1 - M >>>>>> EmplID long int EmplID
FirstName FirstName FirstName
LastName LastName LastName
CatName CatName
SkillName
Proficiency

What I want to do is extract the numerical proficiency values from
tblBTE and place them in tblSkill -- keeping the integrity of the
data. [That is, the VALUE for the FIELD titled dBData in tblBTE needs
to be placed in Proficieny field of tblSkill for the RECORD
corresponding to firstName, LastName, and SkillName(=dBData).

The fact that the skillnames (in tblBTE) are FIELD names has me
stumped. Can anyone tell me how to extract BOTH the field name
(=SkillName) AND the numerical value from tblBTE for each individual
listed in the database??

Thanks for the bandwidth.

gary b
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First you have to re-design your tables - they do not satisfy the
Normalization rules. My suggestions:

tblEmpl
EmplID (auto)
FirstName
LastName

tblCat
CatID (auto)
CatName

tblSkills
CatID -> link to tblCat.CatID
SkillID (auto)
SkillName

tblEmployeeProficiency
EmplID -> link to tblEmpl.EmplID
SkillID -> link to tblSkill.SkillID
Proficiency

The first thing to do is to load the tblEmpl so an EmplID can be
generated (AutoNumber). Then the tblCat to generate the CatID and load
the CatName. Load the tblSkills by hand. Have the tblCat open so you
can see the CatID for each category. Have the imported data table open
in design view so you can see the names of the skills columns.

Once the above tables have been created (except for
tblEmployeeProficiency), add new columns to the import table for each of
the IDs generated (EmplID, CatID). Then write queries to load the newly
generated IDs into the imported data table. E.g. create a column named
"EmplID" in the import table then run the following query:

INSERT INTO <import table> As T (EmplID)
SELECT EmplID
FROM tblEmpl As E
WHERE E.FirstName = T.FirstName
AND E.LastName = T.LastName

This will store the newly generated EmplID (from tblEmpl) in the import
table. Do the same thing for the tblCat (CatID):

INSERT INTO <import table> As T (CatID)
SELECT CatID
FROM tblCat As C
WHERE C.CatName = T.CatName

To get normailzed data out of spread sheet (s/s) format you have to
write a query that will pluck the required identifiers (in your case the
EmplID) and the value of one skill column. You'll have to run the same
query as many times as there are skill columns - changing things for
each run. A template example:

INSERT INTO tblEmployeeProficiency (EmplID, SkillID, Proficiency)
SELECT I.EmplID, # As SkillID, I.<skill column name>
FROM <imported data table> As I

Example of using the template to import the skill value "DBDesign":

0) Keep the tblSkills open so you can see the SkillID for the
appropriate Skill.
1) If the SkillID number for DBDesign = 3 then replace the # character
with the number 3.
2) Replace <skill column name> with DBDesign
3) Be sure to use the correct table name for <imported data table>
4) Run the query

How it should look for DBDesign:

INSERT INTO tblEmployeeProficiency (EmplID, SkillID, Proficiency)
SELECT I.EmplID, 3 As SkillID, I.DBDesign
FROM <imported data table> As I

For the next skill column follow items 1 & 2 (substituting the
appropriate values) and run the query again. Repeat until all skill
columns have been imported.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ6URYechKqOuFEgEQL2HACeLmnR/6HElN8NPYtNeXus+JoNeHMAoK/V
ZWe5sYYKAalbFLmpfUTaO7z9
=Fb0b
-----END PGP SIGNATURE-----


gary said:
I have searched the newsgroups for a solution to no avail. Perhaps
this is not possible?

I have a large table (tblBTE)including [but not limited to] the
following fields: FirstName, LastName, CatName, dBDev, dBDesign, -->
and approximately 30 more skill names. This table was created by
importing data from an Excel spreadsheet. Each record lists
FirstName, LastName, CatName and a numerical proficiency value for the
skill names used as field names. Example:
ID FirstName LastName CatName dBDesign dBdev dBData --->
1 John Smith Core 1 0 2 --->
2 Mary Jones SME 0 2 0 --->
3 Ted Martin Core 2 0 1 --->
4 Alfred Newman SME 0 3 0 --->
Notice that (skill) fields are associated with only one Skill Category
name (Core, SME, or Mgmt). NO field has values for than one type of
Category.

I have created an Access relational database that specifies the
following relationships between the three tables and their fields:

tblEmpl tblCat tblSkill
SkillID (auto)
CatID (auto) 1 - M >>>> CatID (long int)
EmplID (auto) 1 - M >>>>>> EmplID long int EmplID
FirstName FirstName FirstName
LastName LastName LastName
CatName CatName
SkillName
Proficiency

What I want to do is extract the numerical proficiency values from
tblBTE and place them in tblSkill -- keeping the integrity of the
data. [That is, the VALUE for the FIELD titled dBData in tblBTE needs
to be placed in Proficieny field of tblSkill for the RECORD
corresponding to firstName, LastName, and SkillName(=dBData).

The fact that the skillnames (in tblBTE) are FIELD names has me
stumped. Can anyone tell me how to extract BOTH the field name
(=SkillName) AND the numerical value from tblBTE for each individual
listed in the database??
Click to expand...
 
I have searched the newsgroups for a solution to no avail. Perhaps
this is not possible?

I have a large table (tblBTE)including [but not limited to] the
following fields: FirstName, LastName, CatName, dBDev, dBDesign, -->
and approximately 30 more skill names. This table was created by
importing data from an Excel spreadsheet.
Click to expand...

Why am I not surprised at the source of this non-normalized data? said:
Each record lists
FirstName, LastName, CatName and a numerical proficiency value for the
skill names used as field names. Example:
ID FirstName LastName CatName dBDesign dBdev dBData --->
1 John Smith Core 1 0 2 --->
2 Mary Jones SME 0 2 0 --->
3 Ted Martin Core 2 0 1 --->
4 Alfred Newman SME 0 3 0 --->
Notice that (skill) fields are associated with only one Skill Category
name (Core, SME, or Mgmt). NO field has values for than one type of
Category.

I have created an Access relational database that specifies the
following relationships between the three tables and their fields:

tblEmpl tblCat tblSkill
SkillID (auto)
CatID (auto) 1 - M >>>> CatID (long int)
EmplID (auto) 1 - M >>>>>> EmplID long int EmplID
FirstName FirstName FirstName
LastName LastName LastName
CatName CatName
SkillName
Proficiency
Click to expand...

Well... not quite correct. You have a many to many relationship
between Skills and Employees. Each Employee (potentially, perhaps not
in your existing data, but you should allow for the possibility) has
one *or more* skills; each skill can be evinced by zero, one or more
employees.

Consider the design:

tblEmpl tblEmplSkills tblSkill
EmplSkillID Auto
SkillID long int <<n:1 SkillID Auto
EmplID (auto) 1:n >>>>>>> EmplID long int SkillName
FirstName Proficiency
LastName CatName
<other bio data>

This lets you assign each employee one (or more!) skills, each with
*that employee's* proficiency, and avoids redundant storage of names
in multiple tables.
What I want to do is extract the numerical proficiency values from
tblBTE and place them in tblSkill -- keeping the integrity of the
data. [That is, the VALUE for the FIELD titled dBData in tblBTE needs
to be placed in Proficieny field of tblSkill for the RECORD
corresponding to firstName, LastName, and SkillName(=dBData).
Click to expand...

A NORMALIZING UNION query is the ticket here. You'll need first to
populate tblSkill with the skill names now being used as fieldnames.
You'ld then run an Append query filling tblEmpl with the ID's and
person names.

Next, create a UNION query:

SELECT [ID] AS EmplID, tblSkill.SkillID, "Core" AS CatName, [dbDesign]
AS Proficiency
FROM tblBTE, tblSkills
WHERE tblSkills.SkillName = "dbDesign"
AND CatName = "Core"
WHERE [dbDesign] > 0
UNION
SELECT [ID] AS EmplID, tblSkill.SkillID, "Core" AS CatName, [dbDev] AS
Proficiency
FROM tblBTE, tblSkills
WHERE tblSkills.SkillName = "dbDev"
AND CatName = "Core"
WHERE [dbDev] > 0
UNION
<etc etc>

You'ld manually select the appropriate SkillNames for each Cat in this
union query.

Finally, base an Append query on the Union query to populate
tblEmplSkills.
 
Back
Top