Outer Join

P

pape

Hello gurus/MVPs I am new to this so please bear with me.

I have a billing database with tblStaff, tblClient and tblTransactions.
I have set up a query that returns details of all billing between
certain dates (which the user selects). I have then done a cross tab
query on this to give me the monthly totals sorted by staff. I now need
to change the query so I get all clients from tblClient whether they
have been billed or not and all the bills within the specified dates. I
know I need to create an outer join somewhere but just cannot get the
desired results. I have tried creating 2 queries and using UNION but
clearly this is not the solution as I simply get all the records from
both queries.
I had seen a similar query some years ago on another group but I cannot
now find it.

Any help would be most appreciated.

Thanks.
 
J

Jeff Boyce

If you need everyone from table1, regardless of whether they have a record
in table2, you can modify the join line property in your query to say just
that. Highlight the line joining the two tables, right-click on it, set the
properties as above. This also works if the "table1" and "table2" mentioned
above are actually "query1" and "query2".
 
P

pape

Thank you Jeff. That was the first thing I tried - changed the
equi-join to left outer and even right outer (which was of course not
what I wanted) but got the same result each time. Could it be because
the select query has the date range criteria? I can run two separate
queries in Excel and using VBA get the result but I have been asked to
design an Access report.

Pape
 
J

Jeff Boyce

I've seen similar results when the criterion is in the outer table.

Have you tried "chaining" queries together. First create a query with the
date range criteria and return the records that match. Then create a second
query that joins (outer) to the first query.

Good luck!

Jeff Boyce
<Office/Access MVP>
 
P

pape

Thanks Jeff - That's exactly what I did after my last post and it
worked. In fact I just logged in to say I had cracked it
 
Top