Query based on Excel link

[David]

1. I have a linked Excel Table containing standard job assessment
ratings. Each column is a particular Job description i.e. 'Administrator',
'Accountant', 'Secretary' etc. and each of 27 rows contains a specific job
requirement i.e. Typing, Project Management, Database Management etc. Each
cell contains a value representing the standard ability or skill level that
a person in a specific job should have.

2. I also have an Employee job assessment table where their assessed
aptitude or competence is recorded for each specific job requirement.

3. I am trying to create a query that will link the Employee with his/her
Job Description and current aptitude to the linked table, to determine
training requirements.

4. Now the problem!!! How can I use the Employee's 'Job Description' to
lookup 'the correct column' in the linked table in order to return the
standard that will be compared to the assessed value. Another way to put
this might be 'how do I put a variable on the left side of WHERE clause is
SQL? (Select * from --- WHERE ExcelTable.variable (i.e. Accountant) =
Employee.JobDescription;

(I can not change the layout of the linked Excel table, as it is used
elsewhere, and may also be updated from time to time beyond my control -
therefore the link.)

If anyone has suggestions, I would be much appreciate some help, or any
other ideas on how to get values out of a table when both the row and column
are variables.

David

 

[John Vinson]

1. I have a linked Excel Table containing standard job assessment
ratings. Each column is a particular Job description i.e. 'Administrator',
'Accountant', 'Secretary' etc. and each of 27 rows contains a specific job
requirement i.e. Typing, Project Management, Database Management etc. Each
cell contains a value representing the standard ability or skill level that
a person in a specific job should have.

Ok... good spreadsheet design, but *NOT* good relational design.
You're storing data in fieldnames, which makes queries MUCH harder to
implement. See below.

2. I also have an Employee job assessment table where their assessed
aptitude or competence is recorded for each specific job requirement.

3. I am trying to create a query that will link the Employee with his/her
Job Description and current aptitude to the linked table, to determine
training requirements.

Easily done with a "tall-thin" normalized table where you have three
fields - job description, requirement, and value. It's MUCH harder
since you don't have such a table!

4. Now the problem!!! How can I use the Employee's 'Job Description' to
lookup 'the correct column' in the linked table in order to return the
standard that will be compared to the assessed value. Another way to put
this might be 'how do I put a variable on the left side of WHERE clause is
SQL? (Select * from --- WHERE ExcelTable.variable (i.e. Accountant) =
Employee.JobDescription;

You can't. The only way with this structure is to build the entire SQL
statement in VBA code; you can't use parameters to specify a
fieldname.

(I can not change the layout of the linked Excel table, as it is used
elsewhere, and may also be updated from time to time beyond my control -
therefore the link.)

An alternative would be to - as needed - run a "Normalizing Union
Query" to move the data from the spreadsheet into the tall-thin table.
Empty the table first, then run an Append query based on a UNION query
such as

SELECT [Requirement], "Secretary" AS Job, [Secretary] AS Value
FROM spreadsheet
WHERE [Secretary] IS NOT NULL
UNION ALL
SELECT [Requirement], "Administrator", [Administrator]
FROM spreadsheet
WHERE [Administrator] IS NOT NULL
UNION ALL
<etc. for 25 more fields>

Then just join this table.

 

[Jamie Collins]

John Vinson wrote ...

you can't use parameters to specify a
fieldname.

You can but it's not recommended; you end up with some ugly SQL e.g.

CREATE PROCEDURE
MyBadProc
(ColName VARCHAR(50))
AS
SELECT
IIF(ColName = 'MyCol2', MyCol2, MyCol1)
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet$]

Jamie

--

 

[John Vinson]

John Vinson wrote ...

you can't use parameters to specify a
fieldname.

You can but it's not recommended; you end up with some ugly SQL e.g.

CREATE PROCEDURE
MyBadProc
(ColName VARCHAR(50))
AS
SELECT
IIF(ColName = 'MyCol2', MyCol2, MyCol1)
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet$]

Jamie

Works for the SQL/Server (MSDE) engine - but Jet doesn't support
stored procedures.

 

[David]

Thanks for all the response and ideas.
I had pretty much come to the same conclusion regarding turning the
spreadsheet into a tall thin table, and then updating when needed, but was
hoping there might be a neat trick I could use.
Thanks again.

David Blasko

1. I have a linked Excel Table containing standard job assessment
ratings. Each column is a particular Job description i.e. 'Administrator',
'Accountant', 'Secretary' etc. and each of 27 rows contains a specific job
requirement i.e. Typing, Project Management, Database Management etc. Each
cell contains a value representing the standard ability or skill level that
a person in a specific job should have.

Ok... good spreadsheet design, but *NOT* good relational design.
You're storing data in fieldnames, which makes queries MUCH harder to
implement. See below.

2. I also have an Employee job assessment table where their assessed
aptitude or competence is recorded for each specific job requirement.

3. I am trying to create a query that will link the Employee with his/her
Job Description and current aptitude to the linked table, to determine
training requirements.

Easily done with a "tall-thin" normalized table where you have three
fields - job description, requirement, and value. It's MUCH harder
since you don't have such a table!

4. Now the problem!!! How can I use the Employee's 'Job Description' to
lookup 'the correct column' in the linked table in order to return the
standard that will be compared to the assessed value. Another way to put
this might be 'how do I put a variable on the left side of WHERE clause is
SQL? (Select * from --- WHERE ExcelTable.variable (i.e. Accountant) =
Employee.JobDescription;

You can't. The only way with this structure is to build the entire SQL
statement in VBA code; you can't use parameters to specify a
fieldname.

(I can not change the layout of the linked Excel table, as it is used
elsewhere, and may also be updated from time to time beyond my control -
therefore the link.)

An alternative would be to - as needed - run a "Normalizing Union
Query" to move the data from the spreadsheet into the tall-thin table.
Empty the table first, then run an Append query based on a UNION query
such as

SELECT [Requirement], "Secretary" AS Job, [Secretary] AS Value
FROM spreadsheet
WHERE [Secretary] IS NOT NULL
UNION ALL
SELECT [Requirement], "Administrator", [Administrator]
FROM spreadsheet
WHERE [Administrator] IS NOT NULL
UNION ALL
<etc. for 25 more fields>

Then just join this table.

 

[Jamie Collins]

John Vinson wrote ...

John Vinson wrote ...

you can't use parameters to specify a
fieldname.

You can but it's not recommended; you end up with some ugly SQL e.g.

CREATE PROCEDURE
MyBadProc
(ColName VARCHAR(50))
AS
SELECT
IIF(ColName = 'MyCol2', MyCol2, MyCol1)
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet$]

Jamie

Works for the SQL/Server (MSDE) engine - but Jet doesn't support
stored procedures.

John,
With due respect, you are mistaken. They may be called 'stored
queries' but the the above *is* Jet 4.0 syntax! See the 'Procedures'
section of the following MSDN article:

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp?frame=true#acadvsql_procs

"In previous versions of Access, you could use either a procedure
clause or a parameter query to create SQL statements that use
parameters. Although both types of queries are still supported [in
Access2000], another syntax you can use is the new CREATE PROCEDURE
statement (its synonym is CREATE PROC). The new syntax is more ANSI-92
compliant, and when scaling up to a Microsoft SQL Server database or
MSDE, the syntax can be converted more easily."

Jamie.

--

 

[Jamie Collins]

John Vinson wrote ...

John Vinson wrote ...

you can't use parameters to specify a
fieldname.

You can but it's not recommended; you end up with some ugly SQL e.g.

CREATE PROCEDURE
MyBadProc
(ColName VARCHAR(50))
AS
SELECT
IIF(ColName = 'MyCol2', MyCol2, MyCol1)
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet$]

Jamie

Works for the SQL/Server (MSDE) engine - but Jet doesn't support
stored procedures.

John,
With due respect, you are mistaken. They may be called 'stored
queries' but the the above *is* Jet 4.0 syntax! See the 'Procedures'
section of the following MSDN article:

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp?frame=true#acadvsql_procs

"In previous versions of Access, you could use either a procedure
clause or a parameter query to create SQL statements that use
parameters. Although both types of queries are still supported [in
Access2000], another syntax you can use is the new CREATE PROCEDURE
statement (its synonym is CREATE PROC). The new syntax is more ANSI-92
compliant, and when scaling up to a Microsoft SQL Server database or
MSDE, the syntax can be converted more easily."

Jamie.

--

 

[Jamie Collins]

John Vinson wrote ...

John Vinson wrote ...

you can't use parameters to specify a
fieldname.

You can but it's not recommended; you end up with some ugly SQL e.g.

CREATE PROCEDURE
MyBadProc
(ColName VARCHAR(50))
AS
SELECT
IIF(ColName = 'MyCol2', MyCol2, MyCol1)
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet$]

Jamie

Works for the SQL/Server (MSDE) engine - but Jet doesn't support
stored procedures.

John,
With due respect, you are mistaken. They may be called 'stored
queries' but the the above *is* Jet 4.0 syntax! See the 'Procedures'
section of the following MSDN article:

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp?frame=true#acadvsql_procs

"In previous versions of Access, you could use either a procedure
clause or a parameter query to create SQL statements that use
parameters. Although both types of queries are still supported [in
Access2000], another syntax you can use is the new CREATE PROCEDURE
statement (its synonym is CREATE PROC). The new syntax is more ANSI-92
compliant, and when scaling up to a Microsoft SQL Server database or
MSDE, the syntax can be converted more easily."

Jamie.

--

 

[Jamie Collins]

John Vinson wrote ...

John Vinson wrote ...

you can't use parameters to specify a
fieldname.

You can but it's not recommended; you end up with some ugly SQL e.g.

CREATE PROCEDURE
MyBadProc
(ColName VARCHAR(50))
AS
SELECT
IIF(ColName = 'MyCol2', MyCol2, MyCol1)
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet$]

Jamie

Works for the SQL/Server (MSDE) engine - but Jet doesn't support
stored procedures.

John,
With due respect, you are mistaken. They may be called 'stored
queries' but the the above *is* Jet 4.0 syntax! See the 'Procedures'
section of the following MSDN article:

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp?frame=true#acadvsql_procs

"In previous versions of Access, you could use either a procedure
clause or a parameter query to create SQL statements that use
parameters. Although both types of queries are still supported [in
Access2000], another syntax you can use is the new CREATE PROCEDURE
statement (its synonym is CREATE PROC). The new syntax is more ANSI-92
compliant, and when scaling up to a Microsoft SQL Server database or
MSDE, the syntax can be converted more easily."

Jamie.

--

 

[John Vinson]

John,
With due respect, you are mistaken. They may be called 'stored
queries' but the the above *is* Jet 4.0 syntax! See the 'Procedures'
section of the following MSDN article:

Now that's why I love the newsgroups... never know when I'll learn
something new!

Thanks, Jamie, I've got some learning to do!