Unpaid Balances - How To? Suggestions?

[PhaerieTail]

Hello all! I've posted a bit before asking really dumb questions and y'all have been so nice and helpful, so we're going for it again!



I do the office work for a service company - HVAC, plumbing, electric, etc - and I'm working on making a database of jobs and customers so we can have some more organization. I'm aware that my knowledge of Access is extremely limited, and I have a bunch of really helpful tutorials and resources to play with later this week, but I need to make a proof of concept to justify spending that time learning the program. The last thing I need is a form displaying jobs with unpaid balances.

Please ask for any additional information that I may have missed; I'm not familiar enough yet to identify all the information you may need to help me out.

I currently have a table of Jobs, Customers, and Payments. I have a query that adds the payments together and displays in a Jobs Detail form what the remaining balance is of the job. What I need is a way to display all jobs with unpaid invoices. I've got one working now with jobs with partially paid invoices - it seems to be pulling the information from the Payments table, so it doesn't include jobs with no payment entry made yet.

This is an example of what I have:

Invoice	Amount	Payment	Balance
AZ1001	500	250	250
AZ1002	1,000	800	200
AZ1003	250	150	100

But AZ1004, which has an amount of $2,000 and no payment made, doesn't display at all.

Here is an example of what I'm needing:

Invoice	Amount	Payment	Balance
AZ1001	500	250	250
AZ1002	1,000	800	200
AZ1003	250	150	100
AZ1004	2,000	0	2,000

I don't expect to have issues creating the form once I have the information populating, and I've figured out how to only show records with balances greater than 0, but I just can't seem to figure out how to make it pull all records, even if they don't have an entry on my Payments table. Does anyone have an idea for phrasing to google or a suggestion for how to make this work?

[Micron]

A query for this would require an outer join. Put the required tables into a new query and make your usual join(s) and don't include tables you don't need. Double click on join line(s) and read what it says in the resulting dialog box that presents 3 options and see if you can make sense of what it says. Or ask Google "ms access return records from joined tables that have no records" or similar wording.

[Micron]

A query for this would require an outer join. Put the required tables into a new query and make your usual join(s) and don't include tables you don't need. Double click on join line(s) and read what it says in the resulting dialog box that presents 3 options and see if you can make sense of what it says. Or ask Google "ms access return records from joined tables that have no records" or similar wording.
EDIT -
perhaps another approach is to default to 0 payment field to 0 where no payment has been made. Then you'll have a record regardless, but might have to tweak existing queries to values > 0

[PhaerieTail]

That phrase is exactly what I needed to start figuring this out - thank you so much! I found a quick primer on joins!

[Micron]

I greatly admire your willingness to put work into this. So many want everything handed to them, and some have the audacity to claim they can't figure out a search term, yet they can create a thread title for what they want.