Unanswered: Can I have both left and right outer joins at the same time?
I’m rather new to Access and have recently been normalizing data while attempted to convert an old flat database to a relational one. Things have gone pretty well except I’m trying to run some query’s and have yet to be able to get the results I’ve wanted. I’m hopeful that someone on the boards may be able to set me back on track.
The three tables of interest that I’m trying to join reflect tb_demo (customer demographics) tb_bill (billing) and tb_payments (payments received). Using a relational database for data entry has been great as it has allowed easy input of numerous bills and payments joined to the customers info using a primary and foreign key to link the tables.
Now, the problem. I’m trying to run a query that will sum a single customer’s billing for the month and his payments for the month so that I can easily subtract the two to get an outstanding balance. The kicker is that many times I may receive payments from a customer from a previous month during the current month. I am able to run query’s that will summarize the combination of a customers vital stats with a summery of his bill OR Payments but, when I attempt to join the queries so I can run the additional equations I run into the following: If I do a left outer join I get all of the current months bills by customer but only see the payments of the customers that had a current bill (payments made by customers who have been previously billed do not show up). Alternatively, if I do a right outer join I get the opposite (current bills do not show up unless that customer made a payment during the current month). So, I want/need it all :-) , a query summarizing all of the bills for the month and all of the payments that have come in during the month so that I can do a few simple equations.
Does anyone have any hints about resources that might help me out or links to sample databases that do this so I can see how?
The key is the "IN (Select....) part. If you look at the problem, it could have been looked at as requiring simultaneous left and right joins.
If not, please upload a database with what you've got so far and I'll have a peek
ok - having just reread - is there any reason you can't do an outer join from tb_demo to tb_bill and the same again to tb_payments, or is the relationship from demo to bill to payments?
One of the tricks of getting what you want with queries is to break the task into small, bite sized pieces. This also makes it easier to debug if something isn't working right.
Use a seperate query for each of the pieces of the "pie" you want. You can even use "summary" queries to save yourself some math.
Then, use a final query to put the pieces together. This query will have the customer table and each piece of "pie" using a "show all records in customer table and only those matching in the other" join for each query.