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
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:
Posting Komentar