Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

subreport with crosstab query

Status
Not open for further replies.
ineedahelp

ineedahelp

Technical User
Joined
Mar 31, 2009
Messages
27
Location
US
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!
 
Sort by date Sort by votes
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...
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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!!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lameid
  • Locked
  • Question Question
Docmd.OutputTo Not Exporting Subreport
Replies
2
Views
696
lameid
lameid
RonMcIntire
  • Locked
  • Question Question
Page Numbering a report with vba
Replies
6
Views
1K
dhookom
dhookom
Kennelbloke
  • Locked
  • Question Question
Problems with image on report
Replies
8
Views
750
dhookom
dhookom
breezett93
  • Locked
  • Question Question
What should be multiple reports is becoming one report with multiple pages
Replies
13
Views
975
dhookom
dhookom
lameid
  • Locked
  • Question Question
Need Pagination - Subreport - Rich Text Format
Replies
2
Views
556
lameid
lameid

Part and Inventory Search

Sponsor

Back
Top