Senin, 25 Januari 2016

RE: [MS_AccessPros] left substring query

 

Crystal, Thanks.  Not necessary in this case.  Simple query is all they want. 

 

I’ve had to do reports on crosstab data and then dynamically bind the recordset in and then use conditional formatting for some pretty cool blackout charts. 

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, January 25, 2016 12:41 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] left substring query

 



Hi Liz,

adding on ...

if you want the crosstab to be the source of a form, you MUST define column headings ~ and they must correlate to your data ;) ~

warm regards,
crystal

~ have an awesome day ~

On 1/25/2016 12:14 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] wrote:

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 

(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

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.

 





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 (6)

.

__,_._,___

Tidak ada komentar:

Poskan Komentar