Solved

Crosstab query question

Posted on 2014-01-17
3
342 Views
Last Modified: 2014-01-18
I have created a crosstab query which work fine and displays all records.  But in the query designer I want to set a criteria using a year number (like 2013) which I'm getting from a form combobox.

Here is the query criteria:

[Forms]![frmSelectYearForMonthlySumReports]![cboYear]

But it doesn't work because that field on the query is:

Format([DateWorked],"mmm")

What do I have to change in the criteria line?

--Steve
0
Comment
Question by:SteveL13
  • 2
3 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 39789433
You can't use this field since it has already been formatted to be a month name.  Open the query in SQL View and add this to the WHERE clause -

Where ....
AND [Forms]![frmSelectYearForMonthlySumReports]![cboYear] = Year([DateWorked]);

There is no need to add the additional column to the select clause.
0
 

Author Comment

by:SteveL13
ID: 39789486
There is no where clause to add this to.  Here is the SQL:

TRANSFORM Sum(qryMonthlySumReportPublicHrs.TotPublicHours) AS SumOfTotPublicHours
SELECT qryMonthlySumReportPublicHrs.DocentID, qryMonthlySumReportPublicHrs.LastName, qryMonthlySumReportPublicHrs.FirstName, tblDocents.AssignedDay, Sum(qryMonthlySumReportPublicHrs.TotPublicHours) AS [Total Of TotPublicHours]
FROM qryMonthlySumReportPublicHrs LEFT JOIN tblDocents ON qryMonthlySumReportPublicHrs.DocentID = tblDocents.DocentID
GROUP BY qryMonthlySumReportPublicHrs.DocentID, qryMonthlySumReportPublicHrs.LastName, qryMonthlySumReportPublicHrs.FirstName, tblDocents.AssignedDay
PIVOT Format([DateWorked],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39789699
So add one.


TRANSFORM Sum(qryMonthlySumReportPublicHrs.TotPublicHours) AS SumOfTotPublicHours
SELECT qryMonthlySumReportPublicHrs.DocentID, qryMonthlySumReportPublicHrs.LastName, qryMonthlySumReportPublicHrs.FirstName, tblDocents.AssignedDay, Sum(qryMonthlySumReportPublicHrs.TotPublicHours) AS [Total Of TotPublicHours]
FROM qryMonthlySumReportPublicHrs LEFT JOIN tblDocents ON qryMonthlySumReportPublicHrs.DocentID = tblDocents.DocentID
Where [Forms]![frmSelectYearForMonthlySumReports]![cboYear] = Year([DateWorked])
GROUP BY qryMonthlySumReportPublicHrs.DocentID, qryMonthlySumReportPublicHrs.LastName, qryMonthlySumReportPublicHrs.FirstName, tblDocents.AssignedDay
PIVOT Format([DateWorked],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question