subreport with crosstab query

[ineedahelp]

I need to create a report using a crosstab query that summarizes by month fees incurred. there are three types:license fees, maintenance fees and consulting fees. because consulting fees need to display different information than the other two, I thought of doing a subreport linked to the main report. If I ultimately want to sum YTD totals for all three types of fees, where would I put the subreport and how should I link it to the main report. I have tried using Child and Master fields but am having trouble getting the subreport to display. Thanks very much...I am totally lost when it comes to subreports!

 

[dhookom]

because consulting fees need to display different information than the other two

Can you tell us more about the SQL of your crosstab and how the calcs are different?

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Yes, here is the SQL for one part of the report (software maintenance fees and license fees)

.... TRANSFORM Sum(TaskPerformed.FeeAmount) AS SumOfFeeAmount
SELECT TaskPerformed.Description, Sum(TaskPerformed.FeeAmount) AS YTDTotal
FROM ProjectMember LEFT JOIN TaskPerformed ON ProjectMember.MemberID = TaskPerformed.MemberID
WHERE (((TaskPerformed.DatePerformed) Between #1/1/2009# And #12/31/2009#) AND ((TaskPerformed.Description)="Software Maintenance") AND ((ProjectMember.VendorName)="company")) OR (((TaskPerformed.Description)="Software License"))
GROUP BY TaskPerformed.Description
ORDER BY TaskPerformed.Description
PIVOT Format([DatePerformed],"mm-yyyy");

.....

here is the SQL for the final part (consulting fees and expense fees)

TRANSFORM Sum(IIf([Description]="Travel Expense",[TaskPerformed]![FeeAmount],[ProjectMember]![Rate]*[TaskPerformed]![HoursWorked])) AS TotFeeAmount
SELECT TaskPerformed.Description, [Lastname] & " " & [Firstname] AS Fullname, Sum(IIf([Description]="Travel Expense",[TaskPerformed]![FeeAmount],[ProjectMember]![Rate]*[TaskPerformed]![HoursWorked])) AS YTDTotal
FROM ProjectMember LEFT JOIN TaskPerformed ON ProjectMember.MemberID = TaskPerformed.MemberID
WHERE (((TaskPerformed.DatePerformed) Between #1/1/2009# And #12/31/2009#) AND ((TaskPerformed.Description)="Consulting Services") AND ((ProjectMember.VendorName)="company")) OR (((TaskPerformed.Description)="Travel Expense"))
GROUP BY TaskPerformed.Description, [Lastname] & " " & [Firstname]
ORDER BY TaskPerformed.Description
PIVOT Format([DatePerformed],"mm-yyyy");

.....

I need the report to look like the three 'groups': license fees, software maintenance and consulting fees (travel expenses and hours worked)all came from one report. Thanks for any thoughts...I'm lost right now...

 

[dhookom]

Is "company" the only Vendor that uses Consulting Services?

Also, it looks like your WHERE clause only limits the date range against "company" and not against Travel Expense.

If you want both of these to feed into a single report, I would probably add a dummy column aliased as "FullName" to the first crosstab as a Row Heading.

I would also use the faq703-5466 to get constant columns. Then you could use a union query to combine the two crosstabs into a single record set for your report.

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

The Vendors will be supplied via a combo box. I will ultimately need to incorporate this variable into my crosstab query. The Where clause SHOULD be selecting only those records that have "Travel Expense" or "Consulting Services" in the field DESCRIPTION. I have fixed this since I last sent to you. I wasn't sure how to create just ONE query as the "value" column is different for the two types of data...license or maintenance fees versus hours worked and travel expense. I am going to look at the reference you made regarding "constant columns"...I am really unsure about how to proceed! Thank you for your help!

 

[dhookom]

It looks like you have a couple "value" fields:
[FeeAmount] and [HoursWorked]
Is only one of these greater than 0 or is one >0 and the other 0?

Do you understand how to create a new Row Heading in one query so they would return the same number of columns?

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Vendors can have three "fees": software license fees (FeeAmount), software maintenance fees (FeeAmount) and consulting fees. My ONE report needs to display these three GROUPS of fees. The trouble comes for me with the consulting fees...they consist of TWO expense items: travel expense (FeeAmount) and billable hours (HoursWorked). This could be where I am getting into trouble. Maybe I need to have one query with the Value Field for FeeAmount and another query with the Value Field for HoursWorked*Rate? What do you think?

 

[dhookom]

ineedaanswer: "Is only one of these greater than 0 or is one >0 and the other 0?"

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

FeeAmount will be greater than 0 if a Vendor is utilized for software maintenance or license fees or a Vendor Consultant travels thus generating a Travel Expense. If a Vendor Consultant "works" then HoursWorked*Rate will be greater than 0 as well. I hope this answers your question. Can you tell me where subreports are generally placed?

 

[dhookom]

As I stated earlier, I would attempt to combine the crosstabs with a union query. This would/should require only one report. I don't think you would need a subreport.

If you can't figure this out, it might help if you provided some sample records and desired output in your report.

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Thank you I will look into Union queries and try to figure it out. As with most questions I have had with Access, the answer is quite simple it is just trying to figure out how to get there that slows me down...thanks for your help thus far.

 

[ineedahelp]

I think the union query is going to be my answer...THANK YOU!! I did read somewhere that "don't use aggregate function in union query in Access as it may drop records". Have you had any issues using Aggegate (sum) functions in a union query? I am using ms access 2000 and would like to upgrade...any thoughts on which version to upgrade to? Lots of issues with 2000. thanks again!

 

[dhookom]

I haven't heard of issues with aggregates in union queries. There is a significant difference with using "UNION" vs "UNION ALL". "UNION ALL" will allow duplicate records in the output. "UNION" will remove duplicates much like a GROUP BY.

I use mostly Access 2003 and don't have much for issues. I have Access 2007 at home and it takes some getting used to.

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

I keep getting the error message "cannot use the crosstab of a non-fixed columm as a subquery" when I try to view my report. The SQL for my crosstab is.....TRANSFORM Sum(IIf([FeeAmount]=0,[ProjectMember]![Rate]*[taskperformed]![HoursWorked],[TaskPerformed]![FeeAmount])) AS TotCap
SELECT IIf([RtypeClass]="Internal","Internal",[VendorName]) AS NameBreakdown, TaskPerformed.Action
FROM ProjectMember LEFT JOIN TaskPerformed ON ProjectMember.MemberID = TaskPerformed.MemberID
GROUP BY IIf([RtypeClass]="Internal","Internal",[VendorName]), TaskPerformed.Action
PIVOT Format([DatePerformed],"mm-yyyy");
.....

what is meant by NON-FIXED COLUMN?

 

[ineedahelp]

It seems that if I create a query and select IT as my control source I am ok. I only get this error when I put the SQL as the control source...is this a bug or am I missing something?

 

[dhookom]

You can't use SQL as any control source.

As I stated before:
1) I would use the datediff() to create fixed columns
2) I would attempt to avoid using a subreport

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Hi, I am back after trying many attempts to get this report working. As you have stated the subreport is causing some issues, and I did successfully create a union query with my 2 xtab queries, but am having a tough time getting the layout my customer wants. Any suggestions will be very much appreciated...my brain is fried on this particular report right now! Please find a brief detail of desired layout below...

VENDOR NAME: (name of vendor)
software license data monthly data...
software maintenance data monthly data...
consulting services(a heading)
consultant name (name of consultants for this vendor)
travel expenses: monthly data...
hours worked: monthly data...
MONTHLY TOTALS FOR ALL CATEGORIES OF DATA.....

Thanks for any suggestions!!

 

[dhookom]

I have an FAQ in the queries forum on how to create a single crosstab with multiple values. I think your record sorce would need two values. Another FAQ in this forum suggests how to create a monthly crosstab report.

I think you could combine the two FAQs to create your report.

Duane
Hook'D on Access
MS Access MVP