ACCESS FEED: RE: [MS_AccessPros] left substring query

Senin, 25 Januari 2016

RE: [MS_AccessPros] left substring query

Liz,

A couple soap box comments:

- It's always best to enter the column headings if they are known/static. This would be all the unique values of Skill.

- IMO, never use parameter prompts. There is little or no control and it's just unprofessional.

- If you do use the Column Headings, you shouldn't need the parameters but it's still good to use them.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 25 Jan 2016 17:57:02 +0000
Subject: RE: [MS_AccessPros] left substring query

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

http://www.viescas.com/

(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

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

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.