Want to show related and unrelated records.

[Mabjro]

In short, I want to show all related records from two tables at the top of a report, and then show the left over records at the bottom of the report.

Example:
If Table1 had a field called txtType and there were two records. Record1 = Automobile, Record2 = House

Table2 has a field called txtValue a field called txtColor and a field called txtType

In my report I would have a subreport with the txtType field being the link Master/Child field.

If I had a record whose value in txtType was Boat and that value did not appear in Table1, I would still want it to show at the bottom of the report.

Can this be done?

 

[randysmid]

Hi,
The solution lies in your query when you link the two tables together. If you right click on the line connecting the two tables together, select the Join Properties. If I understand correctly, you will want to select the option where all records are retrieved from Table 2, and those from Table 1 that match.

HTH,
Randy Smith, MCP
California Teachers Association

 

[Mabjro]

Thank you Randy, I am familiar with inner and outer joins, but how can you show all the records that match and all of the records that do not match on one report? For example I want my report to look like the following; where Cape, Contemporary, BMW, VW, Ford, Bayliner and Sea Ray are the values in field1 in 7 records.

<header/master report>
House (from table 1)

<detail/subreport>
Ranch (from table2)
Cape (from table2)
Contemporary (from table2)
------------------------------
Car (from table 1)
VW (from table2)
BMW (from table2)
Ford (from table2)
------------------------------
Bayliner (from table2)
Sea Ray (from table2)

Bayliner and Sea Ray are not a car or a house and are not linked by any fields but I want them to show at the bottom of the report.

Thanks
Jonathan

 

[randysmid]

I guess I didn't explain myself correctly. I assume that you want to show all records from Table 2, regardless of whether they match up with any records in Table 1. Is this correct? If so, then the solution is in the Join Properties of the underlying query.
To make the unmatched records appear at the bottom is a bit trickier, but still possible. Instead of doing one query, you may want to have 2 queries, where the first one (make table) contains the records from Table 2 that do not match any records in Table 1. The query wizard can help you create an Unmatched query, then modify it to be a make table. I would then add a sorting code to make it appear at the bottom of the report. For instance, all the unmatched records would have a new field called SortCode with a value of 2.
The next query would be an Append query, where records that match in both Table 1 and Table 2 would be combined. All of these records would have a SortCode value of 1.
Does this sound feasible?

HTH,
Randy Smith, MCP
California Teachers Association