Can This Be Done?

  • Thread starter Thread starter Ray S.
  • Start date Start date
R

Ray S.

I posted a question early this morning in the Reports section, but got no
response:

I have a query result, call it "qryTemp," that gives me three fields:
functional_area, business_unit, and amount_sum. I want to create a report
that has the functional_areas as details in a column along the left side of
the report. Along the top of the report, I want to put the business_units
(along with "functional area," as header titles. In the details of the
report, I want to display the amount_sum that corresponds to each combination
of functional_area and business_unit. Can I do this? How?

Normally, by now I have received many responses to any question I post, but
not this time. I've racked my brain trying to figure out how to do this, but
it escapes me. I can do it pretty easily in Excel, generating a pivot of my
query result, but I really don't want to have to use Excel.

Does anybody have any idea of how I can get this information from query into
report maintaining the structure I want?
 
I thought I had responded to your other post earlier, but maybe not.

In any case, I would suggest you use a query as the record source for the
report.

Add a calculated field to the query that combines the functional area and
business unit into one string. Make the query a totals query with with a
group by on the calculated field and Sum on the amount. The query needs only
the two columns

If you want to sort it be FA and BU, use the report's sorting and grouping,
but don't include headers or footers. It will then sort it like you want it.
 
Thanks for your response. I understand your solution, and I think it may be
the best for me. I tried basing the report on a cross-tab query, but that
presents problems for me. I have to add one extra column titled OH (for
overhead), and it's source is a completely different query. Also, I have ONE
line in the report (one of the functional areas) that also has to be sourced
from an entirely different query. From what I read, cross-tab queries are
unstable and may not work at all if I do what I'm talking about here. Anyway,
it's a start.
 
This sort of thing can still be done, but it starts to become complex.
Despite the complexity, it's surprisingly quick to actually run.

The gist of what you want to do is to do the basics in multiple queries, but
without actually doing the cross-tab query yet. In those queries, you'd
specify all the data you'd need for each row in the final cross-tab query
(or at least a key you can look up in the final query), plus the column name
and finally the value. You CAN use GROUP BY if you want to at this point,
which will probably speed things up slightly, or you can save it till the
cross-tab, depending on your needs.

Then, you use a UNION query to join them all up, and finally use that UNION
query as the basis for your cross-tab.

So, for example:

QueryHours:
SELECT EmployeeID, "Hours Worked" AS ColumnName, SUM(Hours) AS ValueToUse
FROM EmployeeHours GROUP BY EmployeeID

QueryBenefits:
SELECT EmployeeID, EmployeeName, "Total Benefits" AS ColumnName,
SUM(Benefits) AS ValueToUse
FROM BenefitsTable GROUP BY EmployeeID, EmployeeName

UnionQuery:
SELECT * FROM QueryHours
UNION ALL
SELECT * FROM QueryBenefits

CrosstabQuery:
TRANSFORM FIRST(ValueToUse)
{or use SUM(ValueToUse) if you didn't do so in your original queries}
SELECT EmployeeID
FROM UnionQuery
GROUP BY EmployeeID
PIVOT ColumnName
{Optionally: PIVOT ColumnName IN ("Hours Worked", "Total Benefits")}

As I mentioned, you could also link in another table based on the EmployeeID
key in the above example. Again, you could do this in each sub-query if you
wanted, or more likely, in the final crosstab query:

TRANSFORM FIRST(UnionQuery.ValueToUse)
{or use SUM(UnionQuery.ValueToUse) if you didn't do so in your original
queries}
SELECT Employees.EmployeeID, Employees.EmployeeName
FROM UnionQuery INNER JOIN Employees ON UnionQuery.EmployeeID =
Employees.EmployeeID
GROUP BY Employees.EmployeeID, Employees.EmployeeName
PIVOT UnionQuery.ColumnName
{Optionally: PIVOT UnionQuery.ColumnName IN ("Hours Worked", "Total
Benefits") to get a specific column order}


Clear as mud?


Rob
 
Woops, I re-wrote some of these on-the-fly and neglected to remove some
things. QueryBenefits should look like this:

QueryBenefits:
SELECT EmployeeID, "Total Benefits" AS ColumnName,
SUM(Benefits) AS ValueToUse
FROM BenefitsTable
GROUP BY EmployeeID


Rob
 
Back
Top