Query or Sub-Report Question

[combs]

Using Access 2007 SP2

I have a report that I'm trying to get to work with data from a couple of different tables....

BACKGROUND: The database manages Work Orders for construction projects. It keeps track of property owners and the work that is being assessed to them. Both individually and jointly for items like any alley work (i.e. the alley is used by everyone on the block, so they must all contribute towards those costs)

Okay, so the report lists the owners name and property information in the main header. The information here comes from the tblOwnerInfo table. In the detail section, there is a sub-report (based on a query) that shows the individual work items that are associated with that property. This information is pulled from the tblWorkListing table.

I'd also like to display the alley information (if needed) for each property. All the alley information is stored in the tblAlleyWorkListing table. Is there any way to:
a) append that information to the existing sub-report
b) alter the existing query to include the alley data if it's present?


In the tblOwnerInfo table, there is a checkbox/yes-no field that indicates alley information is present for the ownwer.

I'd appreciate any help or guidance people may offer.

 

[MajP]

without knowing the actual fields in your tables it gets hard to say. However, you can simply put another sub report below the current one. With subreports I always shrink them down to 0 height and set the can grow to yes. So if there is not alley information you do not get a gap. If you want to get alley information into the original subreport you would have to use a union query to append information from two different queries. This only makes sense if you have similar fields to display.

 

[MajP]

if using a union query the field data needs to be similar, but the fields to not have to be the same.

http://office.microsoft.com/en-us/a...eries-by-using-a-union-query-HA010206109.aspx