Windows 10 
Access 2016
Hello all!
I work for a service company - plumbing, HVAC, etc, and I'm making a DB for my boss, but I've never used Access so we're all struggling through this together.
I've got a table of Jobs with costs and a table of Payments referencing the invoice number(PK in the Jobs table.)
I can make a query that gives me the cost of the job, minus the payment made, and displays the balance. For most jobs this works perfectly. Some jobs have multiple payments and it seems to be very confusing for Access - I'm sure there's a way to combine duplicate invoice numbers, but I can't seem to figure it out.
For example:
Invoice number AZ1234 is billed out to the customer at $2,000. The customer pays via 2 checks - one for $1,500, one for $500. The query will display this:
InvNumber Amount PmtAmount Balance AZ1234 $2,000 $1,500 $500 AZ1234 $2,000 $500 $1,500
The end goal is to get this to be represented in a JobDetail form, but that not working is it's own special problem I'll probably make a post about on another day. Can anyone give tips & tricks on how to make this continuous? I'd like it to look like this:
InvNumber Amount PmtAmount Balance AZ1234 $2,000 $1,500 $500 AZ1234 $500 $500 $0
But I will also accept this:
InvNumber Amount PmtAmount Balance AZ1234 $2,000 $1,500 $500 AZ1234 $2,000 $500 $0
As long as it works. Please help!
Reply With Quote
