Developer Forum

Hi,

I keep running into this same problem when I building my query and Ie
tried several different approaches. It could be that it is beyond my
knowledge but I don know what direction to take to find the solution. What
is the "cleanest" way to build the following query.

I have a table (I simplified the example):
Empl Type THours
Joe Billable 60
Joe Indirect 100
Mary Billable 160

And I want my query to look like this:
Empl Billable Indirect THours Billable% Indirect% T%
Joe 62 100 162 39% 63% 101%
Mary 160 0 160 100% 0% 100%

I tried a cross-tab query but was unable to perform the calculations because
my Type wasn a field and then I read about inserting IIF statements as
columns and tried this as well but then I couldn group the data by employee
(and other problems). It very possible that I writing my IFF statement
wrong but I not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible hours in the
monthn this case 160. When I do my IIF statement for this calculation, it
thinks it is a parameter.

How should I handle building this query? Youe help will be greatly
appreciated.

Thanks,
Kay

I think your data is inconsistent. Also why would you want to calculate the
T% since isn't this always 100%?

Try SQL like the following which hard-codes the two Type values:
SELECT tblKay.Empl,
Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect,
Sum(tblKay.THours) AS THrs,
Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct,
Sum(Abs([Type]="Indirect")*[THours])/Sum([THours]) AS IndirectPct
FROM tblKay
GROUP BY tblKay.Empl;

--
Duane Hookom
Microsoft Access MVP

Duane,

I put in this SQL and it worked perfectlynd yes, it beyond my knowledge
but thank you will use this as a template for similar situations. But I am
trying to learn and you mentioned that my data might be inconsistent. I
pulling this data from Excel and I need to do so monthly. I was hoping not
to have to transpose the data and divide it up into several tables. I gave
two Types in the example, but I actually have six. In your opinion, should
I make separate tables for Type or will I be safe to use the Excel file and
the code you provide me with? Any suggestions?

And to answer your question about it being 100%...no, it used to be that way
but now we are including OT and the percent can exceed 100%.

Thanks,
Kay

mentioned inconsistent because you had Joe's Billable of both 60 and 62.
That isn't consistent. I don't know why you need to do all the calculations
of percent in the query. Typically this would be performed in a report or
form.

I would use a crosstab with the type as the Column Heading. You may need to
enter all types into the Column Headings property.

I would never create separate tables to store similar data.
--
Duane Hookom
Microsoft Access MVP


"AccessKay" wrote:

uane,

I should have put Joe Billable as 60 and not 62 (sorry about that).
Thanks for the input about not creating separate tables. I sure didn want
to do that. I wanting to put all of this in a query because I going to
later transfer it to Excel to populate a graph. I haven got this far yet
but I assuming I can just drag and drop it in thereopefully.

I like the crosstab query results but as I mentioned, I unable to do any
calculations for the type to get my percentage. Can you use SQL to read the
type column and know that I looking for Billable and to then divide that by
THour to get my percentage? In other words, can I create a calculated field
without that field in my table? If so, how would I do this?

A million thanks again,
Kay


"Duane Hookom" wrote:

gave you the SQL with hard-coded types earlier. You could add more types
if necessary. Their expressions would be similar to the ones I already
provided. Otherwise you could create a query based on your crosstab query.

--
Duane Hookom
MS Access MVP


"AccessKay" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

uane,

Thank you, thank you, thank you!!! You are very kind to answer all of my
questions. This was my first post to this forum and it was definitely a
positive experience. Ie been learning Access on my own and I so thankful
that there are people out there like you that are willing to share their
knowledge for no charget a beautiful thing.

Kay

"Duane Hookom" wrote:

ccessKay,
We are glad to provide the free assistance.

Actually, "free" is optional. I would like you to "pay it forward" if you
can offer some assistance to anyone anywhere anytime any way ;-)

--
Duane Hookom
Microsoft Access MVP


"AccessKay" wrote:

i Duane,

I thought I was set with this part of my database but I don want to use
THours for my calculation to get my Billable Pct. I want to use the total
possible hours in a month which will either be 160 or 200 depending if the
month has five Fridays in it or four Fridays. I like to avoid hard coding
and changing this value each month but that might be a separate issue to deal
with down the road.

I inserted a field into my query called TPossHrs and made it equal 160.
Then I tried to use the SQL you gave me to input TPossHrs instead of THours.
It gave me a different percentage. I thinking that possibly the field I
inserted is not being recognized as a value??? Maybe I need to build another
query??? My SQL is as follows:

TRANSFORM Sum(tblKay.THours) AS SumOfTHours
SELECT tblKay.Empl, Sum(tblKay.THours) AS [Total Of THours],
Sum(Abs([Type]="Billable")*[THours])/Sum([THours]) AS BillablePct, 160 AS
TPossHrs, Sum(Abs([Type]="Billable")*[TPossHrs])/Sum([TPossHrs]) AS
BillablePct2
FROM tblKay
GROUP BY tblKay.Empl, 160
PIVOT tblKay.Type;

How can I get this SQL to divide by 160 or do I need to take another
approach to this?

Againhank you for your help.

Kay


"Duane Hookom" wrote:

TW...I posted a seperate thread for the hours issue

"AccessKay" wrote: