Re: Access Design

From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 12/29/04 

Date: Wed, 29 Dec 2004 11:28:51 -0600

To get the average by 6 month periods, I would create a new column in the
crosstab that would total the PCT for the first and last 6 months. I think
you could just divide by 6.

TRANSFORM Avg([q Production].Pct) AS AvgOfPct
SELECT [q Production].[FN LN], Sum(Abs(Month([Pay Period])<=6) * Pct) As
First6,
Sum(Abs(Month([Pay Period])>=12) * Pct) As Last6
FROM [q Production]
WHERE ((([q Production].[CM FN])=[Enter Case Manager's First Name]) AND (([q
Production].[Pay Period])>#1/1/2003#))
GROUP BY [q Production].[FN LN], [q Production].[Pay Period]
PIVOT Format([Pay Period],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"); 

-- 
Duane Hookom
MS Access MVP
--
"Rita" <Rita@discussions.microsoft.com> wrote in message 
news:24C965D1-9369-4AB3-8CD9-B1E506522B53@microsoft.com...
>I did name the field % and I just changed it to Pct. I am using a crosstab
> query, and it's working beautifully! Just what I want. I've never used the
> SQL until your email--I always use just the design mode. Here's my SQL:
>
> TRANSFORM Avg([q Production].Pct) AS AvgOfPct
> SELECT [q Production].[FN LN]
> FROM [q Production]
> WHERE ((([q Production].[CM FN])=[Enter Case Manager's First Name]) AND 
> (([q
> Production].[Pay Period])>#1/1/2003#))
> GROUP BY [q Production].[FN LN], [q Production].[Pay Period]
> PIVOT Format([Pay Period],"mmm") In
> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
>
> I originally did this in Excel, listed name, production averages for month
> and the first 2 columns were 6 and 12 month averages. I'd like to be able 
> for
> the people to just enter their first name and see their clients listed row 
> by
> row with their averages.
>
> I got the report to look just like the excel spread***.
>
> PRODUCTION RATES DECEMBER 2004
>                   12mo  6mo       Jan    Feb       Mar    April (etc.
> listing each month)
> Jane Doe      53.27  55.43   36.42   43.64   48.64   76.6
>
> My report doesn't show detail. My FN LN footer is
> FN LN    and for each month:   =Avg([Jan])   =Avg([Feb])
>
> It does exactly what I want it to for each month.
> I just need to show the 6 mo and 12 mo total.
> What I did for 6 mo is =([Dec]+[Nov]+[Oct]+[Sep]+[Aug]+[Jul])/6
> and for 12 mo the same thing with all 12 months.
>
> Problem is it doesn't work if there's nothing listed for the month and
> haven't work with Nz (changing null to 0, but I don't want it to be 
> figured
> as 0).
>
> I've just created a couple databases for my husband's nonprofit
> organization. I'm ok with text fields--just creating tables and reports. I
> just wanted to create a production database where case managers would have 
> a
> history, could easily generate reports with their client's production 
> rates.
>
> Originally, I created fields 12/10/04, 11/26/04, 11/12/04, etc. that I 
> would
> enter everyone's production rate in for that date. Put then I found 
> creating
> the reports tedious. I like being able to sort/analyze by having all
> production rates in one field (Pct) versus all the different months. 
> Anyway,
> I ran into the same problem with getting a 6 mo/12 mo average. I listed FN 
> LN
> (client's name) and then [12/10/14]. It displayed like I wanted but then 
> how
> do you average????
>
> Thanks for all your help!!!!!!! I guess I'm out of my league with all 
> this!
>
>
> "Duane Hookom" wrote:
>
>> Have you considered using a crosstab query? Do you actually have a field
>> named "%"? If so, I wouldn't go any further until it was changed to
>> something like "Pct". Also, I never use a derived column (Month) in other
>> calculations in a query. I would recommend against creating columns with
>> names of functions "Month", "Year" or numbers as names (especially with / 
>> in
>> the middle).
>> -- 
>> Duane Hookom
>> MS Access MVP
>>
>>
>> "Rita" <Rita@discussions.microsoft.com> wrote in message
>> news:DC93C3AB-DA2C-4BFC-87C8-B90C374B19C8@microsoft.com...
>> > THANK YOU SO MUCH! I'm sure my request was confusing. I used the year 
>> > and
>> > month to really help me!
>> >
>> > SELECT Production.[Pay Period], IIf([Month]=12,[%]) AS [12/04],
>> > IIf([Month]=11,[%]) AS [11/04], IIf([Month]=10,[%]) AS [10/04],
>> > IIf([Month]=9,[%]) AS [09/04], IIf([Month]=8,[%]) AS [08/04],
>> > IIf([Month]=7,[%]) AS [07/04], IIf([Month]=6,[%]) AS [06/04],
>> > IIf([Month]=5,[%]) AS [05/04], IIf([Month]=4,[%]) AS [04/04],
>> > IIf([Month]=3,[%]) AS [03/04], IIf([Month]=2,[%]) AS [02/04],
>> > IIf([Month]=1,[%]) AS [01/04], Year([Production]![Pay Period]) AS 
>> > [Year],
>> > Month([Production]![Pay Period]) AS [Month], Production.[%], [Case
>> > Managers]![CM FN] & " " & [Case Managers]![CM LN] AS [Case Manager],
>> > [Client]![FN] & " " & [Client]![LN] AS [FN LN] FROM [Case Managers] 
>> > RIGHT
>> > JOIN (Production LEFT JOIN Client ON Production.ClientID = 
>> > Client.CCode)
>> > ON
>> > [Case Managers].CMID = Client.CMID;
>> >
>> > Now I've got a beautiful report that lists Case Managers on a separate
>> > page.
>> > I display the name of the client. I put =Avg([12/04]) in the client 
>> > name
>> > footer so I'm able to show each month's total for each client!!!
>> >
>> > Now I'm stumped again. All I need now is to display my 6 and 12 month
>> > average. I want each row to display client's name, ie. Sally Smith her 
>> > 12
>> > month average, 6 month average, then production rates, ie. 12/04, 
>> > 11/04,
>> > 10/04, etc. How can I average what's in the columns to the right. I'd 
>> > like
>> > to
>> > be able to pull all the information from 1 query.
>> >
>> > THANKS!!!!!!!!!!
>> >
>> > "Duane Hookom" wrote:
>> >
>> >> Does this work:
>> >> SELECT Year([PayrollEnding]) as Yr, Month([PayrollEnding]) As Mth,
>> >> Avg([ProductionRate]) as WeekAvg
>> >> FROM tblYourTable
>> >> GROUP BY Year([PayrollEnding]), Month([PayrollEnding]);
>> >>
>> >> Your report can group in the report by
>> >>    Yr
>> >>    =Mth<7
>> >>    Mth
>> >> In the group footer, simple use
>> >> =Avg(WeekAvg)
>> >>
>> >> -- 
>> >> Duane Hookom
>> >> MS Access MVP
>> >>
>> >>
>> >> "Mail Recipients Changing order of fields"
>> >> <MailRecipientsChangingorderoffields@discussions.microsoft.com> wrote 
>> >> in
>> >> message news:6C901B0D-C1E7-428F-935E-C65554B728C6@microsoft.com...
>> >> > I'm having problems setting up a database to calculate production
>> >> > averages. I
>> >> > want to be able to enter date of payroll ie. 3/4/2004 in field 
>> >> > payroll
>> >> > ending. I want to enter production rate, ie. 49 in Production Rate
>> >> > Field.
>> >> > I
>> >> > will average two payroll ending fields to get March Total. What I'm
>> >> > trying
>> >> > to
>> >> > do in a query is create field
>> >> > 3/4/2004:IIF[Payroll]=3/4/2004,[Production
>> >> > Rate]). Then I need to Average 3/4/2004 and 3/18/2004. March
>> >> > Average:Avg([3/4/2004]+[3/18/2004]). HELP!!!!!!!
>> >> >
>> >> > Then I want to get a 6 and 12 month average.
>> >> >
>> >> > End report would list name, 12 month average, 6 month average, then
>> >> > list
>> >> > each month's average.
>> >> >
>> >> > THANKS!!!!!!
>> >> >
>> >> > Rita
>> >>
>> >>
>> >>
>>
>>
>>