Report Question with Crosstab Queries

[Guest]

Hello!

I have a report that is using a crosstab query...where the column headings
are determined by VBA code -- A,B,C,D, etc etc depending on the amount of
categories that particular month. In my report, I have a calculated field
called "BIllable Percent" which is the project billable hours divided by the
total hours. In this situation where I ran the report today, the project
billable hours is assigned field "D" but next week, it could be assigned
field "E" depending on if another category comes into play that happens to be
earlier in the alphabet, which would screw up the calculation. (since it's in
alphabetical order)

Can anyone help me with this?

Thank you!
MN

 

[Duane Hookom]

How does your data store/identify billable hours vs total hours?

 

[Guest]

"Total" is a field that is solely created on the report. "Project Billable"
is a field identified in the crosstab of A,B,C,D,E...etc etc based on how
many categories there happen to be. (project billable is one category, etc)

 

[Duane Hookom]

If you can't tell me what value will be in what field to identify "Project
Billable" then I can't help you.

 

[Guest]

I'm sorry, I guess I'm not understanding your question? That is the problem
I am having as you never know which "letter" project billable will be
associated with in the crosstab. Is there any way I can get around that or
assign project billable to a specific letter (field) each time?

 

[Duane Hookom]

If you open the datasheet view of a query (standard select query or table),
can you tell by looking at the records, which records/values represent
"Project Billable" hours?

 

[Guest]

I see what you mean. No, you cannot tell which values represent Project
Billable hours in the query because there are column aliases for those
values. Can only tell which ones are project billable on the report because
I have a subreport that shows those actual headings.

 

[Duane Hookom]

So, you are stating that in your actual data (a table), you don't have any
indication of what is Project Billable and what is not. I think the alias
values come from a crosstab report solution that I created but the aliases
are not what I am asking about now. I am only asking about how your
application stores hours. Don't tell me anything about alias and A-F.

 

[Guest]

No....I CAN tell in my actual table which values are project billable....as
those are in the category field in that table.and the hours are then in the
"hours" field in that table with that corresponding record. Yes, you are
correct, there are only aliases because of that crosstab report solution that
you created.

 

[Duane Hookom]

Ok, so now you state that you can tell from looking at your actual records
which are project billable and which are not. This is exactly what I have
been after. Now, if you would just provide a few sample records from your
table and tell us how Project Billable is determined. I want to see real
table and field names and sample records.

 

[Guest]

Great, sorry I didn't understand that more clearly. Here are some sample
records from the main table with some of the data:

Month Sector Project Manager Project # Category Status
Hours
01-Apr-06 ABC Smith, Joe AB-123456-78 Project Bill PLAN 2
01-Apr-06 BBC Doe, Jane AB-55555-78 Project Bill GRN 70
01-Apr-06 BBC Doe, Jane Admin
GRN 10
01-Apr-06 BBC Doe, John PTO
GRN 20

 

[Duane Hookom]

And something in this table/fields suggests the Hours is Project Billable?
Can we make the assumption that a category of "Project Bill" determines
this?

How is this table used in your crosstab query? Can we see some SQL?

I don't have time to ask lots of questions in order to get the required
information. I am leaving on a fishin' trip tonight and you will be at the
mercy of other responders at least until next week.

 

[Guest]

Yes, the category field shows a category of project bill.

Here is my SQL.

PARAMETERS [Forms]![Master Menu]![Month] DateTime;
TRANSFORM Val(Nz(Sum([BY PM].SumOfHours),0)) AS SumOfSumOfHours
SELECT [BY PM].Month, [BY PM].TeamLead, [BY PM].[Project Manager]
FROM tblCategoryHeadings LEFT JOIN [BY PM] ON tblCategoryHeadings.Category =
[BY PM].Category
GROUP BY [BY PM].Month, [BY PM].TeamLead, [BY PM].[Project Manager]
PIVOT tblCategoryHeadings.ColumnAlias;

 

[Duane Hookom]

I was really hoping to see anything you your crosstab that looks like your
unnamed table that you provided earlier. Clearly, there is not enough
information in your postings so far to tie your table view to anything else.

I'm outta here 'til next Tuesday.

--
Duane Hookom
MS Access MVP

Yes, the category field shows a category of project bill.

Here is my SQL.

PARAMETERS [Forms]![Master Menu]![Month] DateTime;
TRANSFORM Val(Nz(Sum([BY PM].SumOfHours),0)) AS SumOfSumOfHours
SELECT [BY PM].Month, [BY PM].TeamLead, [BY PM].[Project Manager]
FROM tblCategoryHeadings LEFT JOIN [BY PM] ON tblCategoryHeadings.Category
=
[BY PM].Category
GROUP BY [BY PM].Month, [BY PM].TeamLead, [BY PM].[Project Manager]
PIVOT tblCategoryHeadings.ColumnAlias;

And something in this table/fields suggests the Hours is Project
Billable?
Can we make the assumption that a category of "Project Bill" determines
this?

How is this table used in your crosstab query? Can we see some SQL?

I don't have time to ask lots of questions in order to get the required
information. I am leaving on a fishin' trip tonight and you will be at
the
mercy of other responders at least until next week.