John, guilty as charged. I am pretty sloppy with not explicitly defining parameters.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, January 25, 2016 10:53 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] left substring query
Liz-
It's always a good idea to explicitly define your parameters, but a Crosstab query actually REQUIRES that you define them. You need a PARAMETERS statement at the beginning of your SQL. If you're using the Design grid, choose Parameters under Show/Hide on the Design tab.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jan 25, 2016, at 6:43 PM, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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
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.
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 (3) |
Tidak ada komentar:
Posting Komentar