Query set-up not using cross-tab

A

AccessKay

Hi,

I keep running into this same problem when I’m building my query and I’ve
tried several different approaches. It could be that it is beyond my
knowledge but I don’t 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’t a field and then I read about inserting IIF statements as
columns and tried this as well but then I couldn’t group the data by employee
(and other problems). It’s very possible that I’m writing my IFF statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible hours in the
month…in 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? You’re help will be greatly
appreciated.

Thanks,
Kay
 
D

Duane Hookom

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

AccessKay

Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my knowledge
but thank you…I 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’m
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


Duane Hookom said:
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


AccessKay said:
Hi,

I keep running into this same problem when I’m building my query and I’ve
tried several different approaches. It could be that it is beyond my
knowledge but I don’t 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’t a field and then I read about inserting IIF statements as
columns and tried this as well but then I couldn’t group the data by employee
(and other problems). It’s very possible that I’m writing my IFF statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible hours in the
month…in 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? You’re help will be greatly
appreciated.

Thanks,
Kay
 
D

Duane Hookom

I 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 said:
Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my knowledge
but thank you…I 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’m
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


Duane Hookom said:
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


AccessKay said:
Hi,

I keep running into this same problem when I’m building my query and I’ve
tried several different approaches. It could be that it is beyond my
knowledge but I don’t 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’t a field and then I read about inserting IIF statements as
columns and tried this as well but then I couldn’t group the data by employee
(and other problems). It’s very possible that I’m writing my IFF statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible hours in the
month…in 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? You’re help will be greatly
appreciated.

Thanks,
Kay
 
A

AccessKay

Duane,

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

I like the crosstab query results but as I mentioned, I’m 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’m 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 said:
I 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 said:
Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my knowledge
but thank you…I 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’m
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


Duane Hookom said:
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


:

Hi,

I keep running into this same problem when I’m building my query and I’ve
tried several different approaches. It could be that it is beyond my
knowledge but I don’t 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’t a field and then I read about inserting IIF statements as
columns and tried this as well but then I couldn’t group the data by employee
(and other problems). It’s very possible that I’m writing my IFF statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible hours in the
month…in 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? You’re help will be greatly
appreciated.

Thanks,
Kay
 
D

Duane Hookom

I 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 said:
Duane,

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

I like the crosstab query results but as I mentioned, I’m 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’m 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 said:
I 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 said:
Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my
knowledge
but thank you…I 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’m
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


:

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


:

Hi,

I keep running into this same problem when I’m building my query
and I’ve
tried several different approaches. It could be that it is beyond
my
knowledge but I don’t 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’t a field and then I read about inserting IIF
statements as
columns and tried this as well but then I couldn’t group the data
by employee
(and other problems). It’s very possible that I’m writing my IFF
statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible
hours in the
month…in 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? You’re help will be
greatly
appreciated.

Thanks,
Kay
 
A

AccessKay

Duane,

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. I’ve been learning Access on my own and I’m so thankful
that there are people out there like you that are willing to share their
knowledge for no charge…it’s a beautiful thing.

Kay

Duane Hookom said:
I 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 said:
Duane,

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

I like the crosstab query results but as I mentioned, I’m 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’m 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 said:
I 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


:

Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my
knowledge
but thank you…I 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’m
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


:

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


:

Hi,

I keep running into this same problem when I’m building my query
and I’ve
tried several different approaches. It could be that it is beyond
my
knowledge but I don’t 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’t a field and then I read about inserting IIF
statements as
columns and tried this as well but then I couldn’t group the data
by employee
(and other problems). It’s very possible that I’m writing my IFF
statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible
hours in the
month…in 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? You’re help will be
greatly
appreciated.

Thanks,
Kay
 
D

Duane Hookom

AccessKay,
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 said:
Duane,

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. I’ve been learning Access on my own and I’m so thankful
that there are people out there like you that are willing to share their
knowledge for no charge…it’s a beautiful thing.

Kay

Duane Hookom said:
I 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 said:
Duane,

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

I like the crosstab query results but as I mentioned, I’m 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’m 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


:

I 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


:

Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my
knowledge
but thank you…I 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’m
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


:

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


:

Hi,

I keep running into this same problem when I’m building my query
and I’ve
tried several different approaches. It could be that it is beyond
my
knowledge but I don’t 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’t a field and then I read about inserting IIF
statements as
columns and tried this as well but then I couldn’t group the data
by employee
(and other problems). It’s very possible that I’m writing my IFF
statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible
hours in the
month…in 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? You’re help will be
greatly
appreciated.

Thanks,
Kay
 
A

AccessKay

Hi Duane,

I thought I was set with this part of my database but I don’t 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’d 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’m 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?

Again…thank you for your help.

Kay


Duane Hookom said:
AccessKay,
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 said:
Duane,

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. I’ve been learning Access on my own and I’m so thankful
that there are people out there like you that are willing to share their
knowledge for no charge…it’s a beautiful thing.

Kay

Duane Hookom said:
I 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


Duane,

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

I like the crosstab query results but as I mentioned, I’m 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’m 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


:

I 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


:

Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my
knowledge
but thank you…I 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’m
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


:

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


:

Hi,

I keep running into this same problem when I’m building my query
and I’ve
tried several different approaches. It could be that it is beyond
my
knowledge but I don’t 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’t a field and then I read about inserting IIF
statements as
columns and tried this as well but then I couldn’t group the data
by employee
(and other problems). It’s very possible that I’m writing my IFF
statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible
hours in the
month…in 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? You’re help will be
greatly
appreciated.

Thanks,
Kay
 
A

AccessKay

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

AccessKay said:
Hi Duane,

I thought I was set with this part of my database but I don’t 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’d 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’m 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?

Again…thank you for your help.

Kay


Duane Hookom said:
AccessKay,
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 said:
Duane,

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. I’ve been learning Access on my own and I’m so thankful
that there are people out there like you that are willing to share their
knowledge for no charge…it’s a beautiful thing.

Kay

:

I 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


Duane,

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

I like the crosstab query results but as I mentioned, I’m 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’m 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


:

I 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


:

Duane,

I put in this SQL and it worked perfectly…and yes, it’s beyond my
knowledge
but thank you…I 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’m
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


:

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


:

Hi,

I keep running into this same problem when I’m building my query
and I’ve
tried several different approaches. It could be that it is beyond
my
knowledge but I don’t 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’t a field and then I read about inserting IIF
statements as
columns and tried this as well but then I couldn’t group the data
by employee
(and other problems). It’s very possible that I’m writing my IFF
statement
wrong but I’m not even sure if I should be taking this approach.

Note: I want my %Columns to be Billable hours / total possible
hours in the
month…in 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? You’re help will be
greatly
appreciated.

Thanks,
Kay
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top