I have two queries: 1. a sum of payments by individuals 2. a sum of charges of individuals. I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).
Combining queries without matching ID Values
- Thread starter Pooks_35
- Start date
- Local time
- Today, 18:15
- Joined
- Jan 23, 2006
- Messages
- 15,498
You mention queries, not tables. Can you show readers a jpg of your relationships window?
Why are there no fields (keys) to relate the tables?
Readers only know what you tell them and they can only offer focused comments once they understand your database and your issue. Perhaps you could provide an overview of what you are trying to accomplish in plain English.
Why are there no fields (keys) to relate the tables?
Readers only know what you tell them and they can only offer focused comments once they understand your database and your issue. Perhaps you could provide an overview of what you are trying to accomplish in plain English.
Last edited:
I had to create queries from the Charges and Payments tables so that I could sum the amounts as each customer will have multiple charges and payments. I just need a sum of each and an overall balance for the report. This is what I currently have which isn't working. I only have the individuals that have both a charge and a payment showing up in the table.
Attachments
Unfortunately, I can't make that type of table. The database has already been created and those tables are tied with an online registration. I'm trying to fix some of the errors made my the developers of this database. Any suggestions on how I can create this report when I can't recreate the table and have to work with what I have available?
You should create a query that mimics the proper structure then. I would UNION your Charges and Payments tables into a query that you can then treat as a properly structured table:
Code:
SELECT *, "Charge" AS TransType FROM Charges
UNION ALL
SELECT *, "Payment" AS TransType FROM Payments
