Senin, 25 Januari 2016

[MS_AccessPros] left substring query

 

Pros, it seems like we ought to be able to prompt the user for a leftmost substring of a field.  So I’m trying this:

 

TRANSFORM Last(CompletionsNogales.CompleteDate) AS LastOfCompleteDate

SELECT EmployeesNogales.EmployeeNumber, EmployeesNogales.Department, CategoryRequirementsNogales.Category, EmployeesNogales.FirstName, EmployeesNogales.LastName, EmployeesNogales.Shift, EmployeesNogales.Supervisor

FROM ((((EmployeesNogales LEFT JOIN EmployeeRequirementsNogales ON EmployeesNogales.EmployeeNumber = EmployeeRequirementsNogales.EmployeeNumber) LEFT JOIN CompletionsNogales ON EmployeesNogales.EmployeeNumber = CompletionsNogales.EmployeeNumber) LEFT JOIN SkillsNogales ON CompletionsNogales.SkillID = SkillsNogales.SkillID) INNER JOIN unionCategoriesOfEmployeesNogales ON EmployeesNogales.EmployeeNumber = unionCategoriesOfEmployeesNogales.EmployeeNumber) INNER JOIN CategoryRequirementsNogales ON unionCategoriesOfEmployeesNogales.CategoryID = CategoryRequirementsNogales.CategoryID

WHERE (((SkillsNogales.ExcludeFromMatrices)=False) AND ((CompletionsNogales.Skill) Like [Enter left substring:] & "*") AND ((SkillsNogales.ExcludeFromMatrices)=False) AND ((EmployeesNogales.Status)<>"Inactive"))

GROUP BY SkillsNogales.ExcludeFromMatrices, EmployeesNogales.EmployeeNumber, EmployeesNogales.Department, CategoryRequirementsNogales.Category, EmployeesNogales.FirstName, EmployeesNogales.LastName, EmployeesNogales.Shift, EmployeesNogales.Supervisor

ORDER BY EmployeesNogales.Department, EmployeesNogales.FirstName, EmployeesNogales.LastName

PIVOT CompletionsNogales.Skill;

 

And this:

 

TRANSFORM Last(CompletionsNogales.CompleteDate) AS LastOfCompleteDate

SELECT EmployeesNogales.EmployeeNumber, EmployeesNogales.Department, CategoryRequirementsNogales.Category, EmployeesNogales.FirstName, EmployeesNogales.LastName, EmployeesNogales.Shift, EmployeesNogales.Supervisor

FROM ((((EmployeesNogales LEFT JOIN EmployeeRequirementsNogales ON EmployeesNogales.EmployeeNumber = EmployeeRequirementsNogales.EmployeeNumber) LEFT JOIN CompletionsNogales ON EmployeesNogales.EmployeeNumber = CompletionsNogales.EmployeeNumber) LEFT JOIN SkillsNogales ON CompletionsNogales.SkillID = SkillsNogales.SkillID) INNER JOIN unionCategoriesOfEmployeesNogales ON EmployeesNogales.EmployeeNumber = unionCategoriesOfEmployeesNogales.EmployeeNumber) INNER JOIN CategoryRequirementsNogales ON unionCategoriesOfEmployeesNogales.CategoryID = CategoryRequirementsNogales.CategoryID

WHERE (((InStr([CompletionsNogales]![Skill],[Enter leftmost substring]))=1) AND ((SkillsNogales.ExcludeFromMatrices)=False) AND ((EmployeesNogales.Status)<>"Inactive"))

GROUP BY SkillsNogales.ExcludeFromMatrices, EmployeesNogales.EmployeeNumber, EmployeesNogales.Department, CategoryRequirementsNogales.Category, EmployeesNogales.FirstName, EmployeesNogales.LastName, EmployeesNogales.Shift, EmployeesNogales.Supervisor

ORDER BY EmployeesNogales.Department, EmployeesNogales.FirstName, EmployeesNogales.LastName

PIVOT CompletionsNogales.Skill;

 

But it is erroring out.  I could programmatically prompt and set a query def, but I just figured there was an easy sql that I’m missing.

 

Respectfully,

Liz Ravenwood

Programmer / Analyst

B/E Aerospace | Super First Class Environments

 

1851 S Pantano Road | Tucson, Arizona 85710

Office +1.520.239.4808 | Internal 814-4808

beaerospace.com

Passion to Innovate. Power to Deliver

 



This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

__._,_.___

Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

.

__,_._,___

Tidak ada komentar:

Posting Komentar