Outer Join

  • Thread starter Thread starter pape
  • Start date Start date
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.
 
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".
 
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
 
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>
 
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
 
Back
Top