Using Subreports

[jonesy29847]

I am creating a database for my business, I am a service provider.



I wish to incorporate travel expenses into a report, I need to relate those travel expense to the date range of the report.

The report records will consist of Task records and there may not be a cooresponding expense for a given date.

Typical ranges will be two week periods, but may extend into more. Expenses may/may not be accrued for a given date.

The problem I am having with subreports is that only the date where I have expenses are included in the report.

I am using Access 2007.

Thank you in advance for your help.

[TheShabz]

If your subreport is based off a query, try running the query first to see if it returns what you want. I think your query joins are off. I think you may need an outer join for your dates, that way it will return nulls as well and just show as blank (or whatever you format it as) on the report.

[jonesy29847]

Thanks for the quick reply. I created a query that uses the expense date and the task date as the relationship. When I run the query the task record repeats itself for each expense from that date. I also need to add =1 header to my report and cannot find how to do that.

[TheShabz]

this really seems like a join issue. Can you provide the SQL (feel free to change table names or field names for security/privacy)? Also, I dont understand what you mean by "need to add =1 header".

[jonesy29847]

This query returns only records where the dates match.

SELECT Task.*, [Project Expenses].*
FROM Task INNER JOIN [Project Expenses] ON Task.TaskDate = [Project Expenses].[Expense Date];

This query returns multiple copies of Task where there are multiple expenses.

SELECT Task.*, [Project Expenses].*
FROM Task LEFT JOIN [Project Expenses] ON Task.TaskDate = [Project Expenses].[Expense Date];

This query returns only tasks where there is an expense associated, it also creates multiple tasks if there are multiple expenses for a given date

SELECT Task.*, [Project Expenses].*
FROM Task RIGHT JOIN [Project Expenses] ON Task.TaskDate = [Project Expenses].[Expense Date];

The =1 header is from a Microsoft example "Time and Billing", My objective is print all expenses as a separate report.

Thanks again for prompt reply.

[TheShabz]

Ok, now going back to your previous post, "task record repeats itself for each expense from that date." I take it you want the report to be hierarchical having one date, one record, multiple expenses. If so, that's not a problem with your query, rather a formatting requirement on your subreport. Any recommendations I make about reports would be purely off the top of my head as I have EXTREMELY little experience with reports. I'm going to let someone else take care of the reporting aspect of it. Again, if I understand your requirement correctly, your queries look fine to me.