Union Query?

[Guest]

I am trying to write a query that will result in getting the name, address
and trade of my firm being added to the name address and trade of the
consultants for every job (the other consultants vary by the job).

This is in order to write a multiple column subreport that will list our
firm first, followed by the names, address and trade of all the other
consultants on a specific job).

I have to have the job number in the query so that the Parent-Child
relationship works on the report.

How do I get my firm to show up for all jobs?

Thanks.

 

[[MVP] S.Clark]

Without knowing your table structures, a concrete answer can't really be
given. But, as a WAG, if you perform a Cross(Cartesian) Product, you can
match ALL data in one table to ALL data in another table.

As an example, in Northwind, add both the Customer and Product tables to the
query. Add ProductID and CustomerID to the grid. The result will be one
record for every permutation of Product to Customer.

Thus, if you have a table with one record for your company info, then cross
that with another table, which contains the subs data, then you'll get your
data crossed with their data.

 

[Guest]

Thanks. A night's sleep helped me find a simpler solution. I wrote one
query that selected all the distinct contract numbers, and added our firm's
data as expressions, in the matching fields to my other query.

Then I wrote a union query that joined my original query with this second
query that added our firm's name.

I appreciate your suggestions, though.