The SQL is below - basically I want to format the STDRateRealiz to
percent
and all the other fields as integer in the expression
IIf([ctabmeasure]="STDRateRealiz",[STDRateRealiz]
TRANSFORM
Sum(IIf([ctabmeasure]="EXTBillRate",[EXTBillRate],IIf([ctabmeasure]="EFFBillRate",[EFFBillRate],IIf([ctabmeasure]="STDRateRealiz",[STDRateRealiz],IIf([ctabmeasure]="EXTRateRealiz",[EXTRateRealiz],IIf([ctabmeasure]="STDTimeBill",[StdTimeBilled],IIf([ctabmeasure]="EXTTimewoff",[TimeWriteoff],IIf([ctabmeasure]="EXTTimeBill",[ExtTimeBilled],IIf([ctabmeasure]="BillWoff",[BillingWoff],IIf([ctabmeasure]="FeeBill",[FeeBillCredit],IIf([ctabmeasure]="UnbillFees",[UnbilledFees],IIf([ctabmeasure]="Unbill60",[UnbilledFeesO60],IIf([ctabmeasure]="Unbill120",[UnbilledFeesO120],0)))))))))))))
AS data
SELECT WAandBAStatswithMonths.tklast, WAandBAStatswithMonths.tkfirst,
WAandBAStatswithMonths.tktitle, WAandBAStatswithMonths.tkloc,
WAandBAStatswithMonths.ldesc, WAandBAStatswithMonths.tkdept,
WAandBAStatswithMonths.head1, WAandBAStatswithMonths.tkemdate,
WAandBAStatswithMonths.thtk, WAandBAStatswithMonths.ATTY,
billingmeasure.ctabmeasure AS measure,
Sum(IIf([ctabmeasure]="EXTBillRate",[EXTBillRate],IIf([ctabmeasure]="EFFBillRate",[EFFBillRate],IIf([ctabmeasure]="STDRateRealiz",[STDRateRealiz],IIf([ctabmeasure]="EXTRateRealiz",[EXTRateRealiz],IIf([ctabmeasure]="STDTimeBill",[StdTimeBilled],IIf([ctabmeasure]="EXTTimewoff",[TimeWriteoff],IIf([ctabmeasure]="EXTTimeBill",[ExtTimeBilled],IIf([ctabmeasure]="BillWoff",[BillingWoff],IIf([ctabmeasure]="FeeBill",[FeeBillCredit],IIf([ctabmeasure]="UnbillFees",[UnbilledFees],IIf([ctabmeasure]="Unbill60",[UnbilledFeesO60],IIf([ctabmeasure]="Unbill120",[UnbilledFeesO120],0)))))))))))))
AS Total
FROM billingmeasure, WAandBAStatswithMonths
GROUP BY WAandBAStatswithMonths.tklast, WAandBAStatswithMonths.tkfirst,
WAandBAStatswithMonths.tktitle, WAandBAStatswithMonths.tkloc,
WAandBAStatswithMonths.ldesc, WAandBAStatswithMonths.tkdept,
WAandBAStatswithMonths.head1, WAandBAStatswithMonths.tkemdate,
WAandBAStatswithMonths.thtk, WAandBAStatswithMonths.ATTY,
billingmeasure.ctabmeasure
PIVOT WAandBAStatswithMonths.mthname;
--
deodev
Duane Hookom said:
I haven't been able to find the SQL view of your current query.
--
Duane Hookom
MS Access MVP
--
In the report I would have to add iif(hourstype ="STD", format(STD as
percentage), STd) for each of the months - however, I can't add Sept
until
Sept - I would like to automate this - without having to go in each
month
and add a field.
That's why I thought of formatting in the query.
Any other suggestions?
Thanks
--
deodev
:
I don't do any formatting in queries. I prefer to do this in report or
form
controls.
--
Duane Hookom
MS Access MVP
--
Thanks - I am working on the report.
Is it possible to format a number to percent in a query?
for example iif(hourstype ="STD", format(STD as percentage), STd)
--
deodev
:
You add a field to your Hours Type lookup table that stores the
sort
order
you want on the report. Then include this field in your report's
record
source so you can sort on it.
--
Duane Hookom
MS Access MVP
Hi Duane,
I am able to create my report as follows:
HoursType Jan Feb Mar Apr May
ext val 50 60 70 80 90
Hours Worked 20 40 60 100 50
Hours Billed 20 40 60 100 0
std realization 100 100 100 100 0
How do get it to so that the hours type "ext val" is below the
"Hours
Billed" as follows (since the "ext val " is not a field name that
I
can
move
- )
HoursType Jan Feb Mar Apr May
Hours Worked 20 40 60 100 50
Hours Billed 20 40 60 100 0
ext val 50 60 70 80 90
std realization 100 100 100 100 0
I though of renaming the fields so that if I sort alpha it would
be
in
the
order I want - but I am sure there is a better way to do this.
Thanks for all your help.
--
deodev
:
You can arrange your columns as you wish in a report.
--
Duane Hookom
MS Access MVP
Thanks - I think I am missing something
When I do the crosstab I get the data in the report format
however, I can only arrange the the hours type field alpha.
So for example if I want YTDRelization to show up before Hours
Worked
I then have a problem.
any way to resolve this.
--
deodev
:
SELECT Employee, Sum(Abs(HoursType="Billed") *
Hours)/Sum(Abs(HoursType="Worked") * Hours) As YTDRealization
FROM tblCorrectStructure
WHERE SomeYearField = 2005
GROUP BY Employee;
To get your report design, you can use a crosstab query.
--
Duane Hookom
MS Access MVP
Hello Duane,
I am able to create the table as you suggested that is
Employee Month HoursType Hours
Duane 7 Worked 160
Duane 7 Billed 250
However, I am having a problem with my report that I am
trying
to
create
from the new table.
I need to arange the "hoursType" field and also make
calculations
on
specific values.
for example - I need to calculate the ytd realization which
is
the
total
hrs
billed divided by total hours worked.
Some fields will need to be substracted from another etc
report layout:
Emplyee Name Jan Feb Mar
Apr
May
June
etc Total
Hours Worked 20 40 60 100
50
100
Hours Billed
std realization
ext val
How can I accomplished this.
Thanks for your help.
Deo.
--
deodev
:
Your new table should not have separate fields for
HoursWorked
and
HoursBilled. This should create two records where once
there
was
one.
Employee Month HoursWorked HoursBilled
Duane 7 160 250
Should be changed to the following possibly using a union
query.
Employee Month HoursType Hours
Duane 7 Worked 160
Duane 7 Billed 250
--
Duane Hookom
MS Access MVP
--
message
Thanks Duane - I will try your suggestion -
I am assuming this will not exceed the 255 fields as I
have
at
least
25
dat
elements(hoursworked, hrsbilled etc) so
:
There are several solution to creating your report
based
on a
crosstab.
The
first thing I would do rather than creating a less
normalized
table
is
to
create a more normalized table that has HoursWorked,
HoursBilled,
etc
as
HoursType field values rather than as separate fields.
You
can
then
create a
crosstab that has just the months as column headings.
HoursType
becomes a
Row Heading.
