Access Design

[Mail Recipients Changing order of fields]

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

 

[Duane Hookom]

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"

 

[Rita]

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!!!!!!!!!!

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"

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

 

[Duane Hookom]

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

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!!!!!!!!!!

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"

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

 

[Rita]

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 spreadsheet.

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!

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

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!!!!!!!!!!

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"
message 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

 

[Duane Hookom]

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
--

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 spreadsheet.

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!

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

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!!!!!!!!!!

:

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"
in
message 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

 

[Rita]

Well I got it to work. It's not very efficient, but at least it displays what
I need it to.

Sum(Abs(Month([Pay Period])>=12) * Pct) just pulls December--I need the last
12 months so I changed 12 to 1 and that seems to work, but I'll always need
the past 6 months--not always January-June. For February, I'd need Months 2,
1, 12, 11, 10, 9, and 8 as in the following example:

PRODUCTION RATES DECEMBER 2004
12mo 6mo Feb Jan Dec (etc. listing each
month)
Jane Doe 53.27 55.43 36.42 43.64 48.64 76.6

I just used the crosstab query that I had and made adjustments in my
reports...

My report doesn't show detail.
Headings Jan-Dec, Past 6 mo total, 12 mo total and in the FN LN Footer:
JanA=Avg([Jan]), FebA=Avg([Feb]) etc.
NOT VISIBLE FIELDS:
01C=IIf(Count([Jan])>1,1,0)
02C=IIf(Count([Feb])>1,1,0), same thing for each month

6MO=Nz([07C])+Nz([08C])+Nz([09C])+Nz([10C])+Nz([11C])+Nz([12C])

12MO=Nz([01C])+Nz([02C])+Nz([03C])+Nz([04C])+Nz([05C])+Nz([06C])+Nz([07C])+Nz([08C])+Nz([09C])+Nz([10C])+Nz([11C])+Nz([12C])

Then to get the last 6/12 month totals
Last6moavg=(Nz([DecA])+Nz([NovA])+Nz([OctA])+Nz([SepA])+Nz([AugA])+Nz([JulA]))/Nz([6MO]
12moavg=(Nz([DecA])+Nz([NovA])+Nz([OctA])+Nz([SepA])+Nz([AugA])+Nz([JulA])+Nz([JunA])+Nz([MayA])+Nz([AprA])+Nz([MarA])+Nz([FebA])+Nz([JanA]))/Nz([12MO])

This is a pain because for the next month's reports, I have to change all
the formulas--rearranging the fields so last months shows as first column.

I'm setting up 12 reports, but at least they'll always work for next year.

Thanks for all your help!

 

[Duane Hookom]

To get the last 6 months, you can try a row heading of
Sum(Abs(DateDiff("m",[Pay Period],Date())<=6) * Pct)