John
That query is what I was looking for. I am sorry I was not clear on what I was saying about the hard coded things. The only way I could think of getting the FY in a query was to hard code the FY with an IIf statement for each difference. But I knew that it was wrong because if a new fiscal year would come along I would need to go into the query and add another year. I had the first query that showed the fiscal year but did not know how to get to what you did.
What you said about the subtracting between FY's each Diff1 or Diff2, I am sure they did not mean that. They can look at your query and see if they have made any improvements to the process. What they wanted to find out if from each year how they are doing to get positions filled. Because we are a state entity, filling positions seems to take longer because of the red tape. Many times really good candidates are offered a job and they have already been offered a job somewhere else because we take so long.
Thank You
Jim Wagner
________________________________
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, May 21, 2012 10:43 PM
Subject: RE: [MS_AccessPros] Custom crosstab question
Jim-
What does this query get you?
SELECT qrySummaryMetirics.FYear As [Fiscal Year], Sum([Diff1]) As [Difference 1], Sum([Diff2]) As [Difference 2], … , Sum([Diff13]) As [Difference 13]
FROM qrySummaryMetirics
GROUP BY FYear;
That gets you the total of the sum of the differences in each of the 13 categories for each year. (I assume there are multiple rows per fiscal year in HireActivityFilled.)
I guess I don't understand why you're using hard-coded year values to spread the original Diff1 value across four columns.
And now that I look at it again, I'm not sure what you mean by "Now they want to see a breakdown of the differences for each Fiscal Year of the difference for each date difference." Do you mean they want to subtract Diff1 in 2010 from Diff1 in 2011 to get a "difference" between years of the difference value?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
P.S. Is the query really named qrySummaryMetirics, or is it really qrySummaryMetrics ??
----------------------------------
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Monday, May 21, 2012 9:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Custom crosstab question
John
I took off the totals.
I guess what I meant about hard coded was that in a subsequent query I was trying to get a crosstab type of query started but realized that it would not work. Below is the beginning of what I started. this is what I meant by hard coded.
SELECT qrySummaryMetirics.FYear AS [Fiscal Year], Sum(IIf([FYear]=2009,[Diff1],0)) AS [Difference 1], Sum(IIf([FYear]=2010,[Diff1],0)) AS [Difference 2], Sum(IIf([FYear]=2011,[Diff2],0)) AS [Difference 3], Sum(IIf([FYear]=2012,[Diff1],0)) AS [Difference 4]
FROM qrySummaryMetirics
GROUP BY qrySummaryMetirics.FYear;
Jim Wagner
________________________________
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, May 21, 2012 12:27 PM
Subject: RE: [MS_AccessPros] Custom crosstab question
Jim-
First, there's no reason to make this a Totals query. I don't see any aggregate expressions at all. And you're making the query engine do a lot of unnecessary work.
How is your FYYear calculation hard coded? I assume DateOpen and Vacancy Date are fields in the table.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Monday, May 21, 2012 8:46 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Custom crosstab question
John
Below is the query that I use to get the Fiscal Year Results. The fiscal year is the last column with the expression
FYear: Year([DateOpen])-IIf([Vacancy Date]<DateSerial(Year([DateOpen]),6,16),1,0)
SELECT HireActivityFilled.[Vacancy Date], HireActivityFilled.[Approval to fill], HireActivityFilled.[Submit JO to HR], HireActivityFilled.DateOpen, HireActivityFilled.[Close Date], HireActivityFilled.[AL Completed], HireActivityFilled.[AL Approved], HireActivityFilled.[Interview Date], HireActivityFilled.[Intv Summ Sheet Recd], HireActivityFilled.[Recommended Candidate], HireActivityFilled.[Date Ref Completed], HireActivityFilled.[Date Bkg Completed], HireActivityFilled.[HPR Approval], HireActivityFilled.[Prepare Job Offer to HR], HireActivityFilled.[HR Approval to Hire], HireActivityFilled.[Approval to Hire to HO], DateDiff("d",[Submit JO to HR],[DateOpen]) AS Diff1, DateDiff("d",[DateOpen],[Close Date]) AS Diff2, DateDiff("d",[Close Date],[AL Completed]) AS Diff3, DateDiff("d",[AL Completed],[AL Approved]) AS Diff4, [Diff1]+[Diff2]+[Diff3]+[Diff3] AS Totals, DateDiff("d",[AL Approved],[Interview Date]) AS Diff5, DateDiff("d",[Interview Date],[Intv Summ
Sheet Recd]) AS Diff6, DateDiff("d",[Intv Summ Sheet Recd],[Date Ref Completed]) AS Diff7, DateDiff("d",[Date Ref Completed],[Date Bkg Completed]) AS Diff8, DateDiff("d",[Date Bkg Completed],[HPR Approval]) AS Diff9, DateDiff("d",[HPR Approval],[Prepare Job Offer to HR]) AS Diff10, DateDiff("d",[Prepare Job Offer to HR],[HR Approval to Hire]) AS Diff11, DateDiff("d",[HR Approval to Hire],[Approval to Hire to HO]) AS Diff12, DateDiff("d",[Submit JO to HR],[Approval to Hire to HO]) AS Diff13, Year([DateOpen])-IIf([Vacancy Date]<DateSerial(Year([DateOpen]),6,16),1,0) AS FYear
FROM HireActivityFilled
GROUP BY HireActivityFilled.[Vacancy Date], HireActivityFilled.[Approval to fill], HireActivityFilled.[Submit JO to HR], HireActivityFilled.DateOpen, HireActivityFilled.[Close Date], HireActivityFilled.[AL Completed], HireActivityFilled.[AL Approved], HireActivityFilled.[Interview Date], HireActivityFilled.[Intv Summ Sheet Recd], HireActivityFilled.[Recommended Candidate], HireActivityFilled.[Date Ref Completed], HireActivityFilled.[Date Bkg Completed], HireActivityFilled.[HPR Approval], HireActivityFilled.[Prepare Job Offer to HR], HireActivityFilled.[HR Approval to Hire], HireActivityFilled.[Approval to Hire to HO], DateDiff("d",[Submit JO to HR],[DateOpen]), DateDiff("d",[DateOpen],[Close Date]), DateDiff("d",[Close Date],[AL Completed]), DateDiff("d",[AL Completed],[AL Approved]), HireActivityFilled.[Job Order Status], DateDiff("d",[AL Approved],[Interview Date]), DateDiff("d",[Interview Date],[Intv Summ Sheet Recd]), DateDiff("d",[Intv Summ Sheet
Recd],[Date Ref Completed]), DateDiff("d",[Date Ref Completed],[Date Bkg Completed]), DateDiff("d",[Date Bkg Completed],[HPR Approval]), DateDiff("d",[HPR Approval],[Prepare Job Offer to HR]), DateDiff("d",[Prepare Job Offer to HR],[HR Approval to Hire]), DateDiff("d",[HR Approval to Hire],[Approval to Hire to HO]), DateDiff("d",[Submit JO to HR],[Approval to Hire to HO])
HAVING (((HireActivityFilled.[Vacancy Date]) Is Not Null) AND ((HireActivityFilled.[Approval to fill]) Is Not Null) AND ((HireActivityFilled.[Submit JO to HR]) Is Not Null) AND ((HireActivityFilled.DateOpen) Is Not Null) AND ((HireActivityFilled.[Close Date]) Is Not Null) AND ((HireActivityFilled.[AL Completed]) Is Not Null) AND ((HireActivityFilled.[AL Approved]) Is Not Null) AND ((HireActivityFilled.[Interview Date]) Is Not Null) AND ((HireActivityFilled.[Intv Summ Sheet Recd]) Is Not Null) AND ((HireActivityFilled.[Recommended Candidate]) Is Not Null) AND ((HireActivityFilled.[Date Ref Completed]) Is Not Null) AND ((HireActivityFilled.[Date Bkg Completed]) Is Not Null) AND ((HireActivityFilled.[HPR Approval]) Is Not Null) AND ((HireActivityFilled.[Prepare Job Offer to HR]) Is Not Null) AND ((HireActivityFilled.[HR Approval to Hire]) Is Not Null) AND ((HireActivityFilled.[Approval to Hire to HO]) Is Not Null) AND ((HireActivityFilled.[Job Order
Status]) Is Not Null));
Jim Wagner
________________________________
________________________________
From: John Viescas <JohnV@msn.com <mailto:JohnV%40msn.com> >
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Monday, May 21, 2012 11:34 AM
Subject: RE: [MS_AccessPros] Custom crosstab question
Jim-
It would help to see the SQL of your query to see how you can dynamically
calculate the year from today's date. Also, we need to know what comprises your
fiscal year. A calendar year?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of luvmymelody
Sent: Monday, May 21, 2012 8:14 PM
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] Custom crosstab question
Hello all,
When I built this report for users, all was well. It was exactly what they
wanted. Now there are some things that they are requesting that I am trying to
develop with what I have. The problem is that the original request may need to
be revised and I am not wanting to do that.
The original request was to build a report to show the differences between 2
dates across a date range to see how long it takes to post a job position and
then to fill it. There are about 13 milestones between the begin and end dates
that are also calculated.
So I created a query that has expressions to calculate the differences between
the about 13 different dates across the spectrum of different milestones of a
hiring process.
Now they want to see a breakdown of the differences for each Fiscal Year of the
difference for each date difference. So I created a query that shows this but,
it is hard coded with the FY in the query. I really do not want to manage the
fiscal years in the query every time a new fiscal year comes along.
How do I build this? Unfortunately my design is to create an expression in the
query for each difference. Below is the name of the expressions in the query to
get the differences.
Diff1
Diff2
Diff3 and so forth.
Thank You
Jim Wagner
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar