To union query or not to union query

[Seth Schwarm]

Win XP Pro
Access 2002 XP

I am working in a Payment form which allows users to apply a payment to an
invoice. The form has a combo box named Payment Method. I am currently
using a Value List to populate the combo box ("Cash";"Check";"Credit
Card";"Project Dep.";"House Credit").

I am adding new functionality to this application by tracking the balance of
Project Deposits and Account credits.

What I want to end up with . . .
I want a combo box that displays each of the Payment Type entries it always
has. I also want it to have a second column where it lists the available
funds beside the Project Deposit and Account Credit.

I want the other Payment Types to have nothing in the second column, or if I
have to settle I will accept $0.00.

I have the queries built to return a balance per invoice number (separate
query for each of the two types of payment).

How do I put these queries together to get the result I want?

I have considered creating a union query where I have the two output fields
mentioned and the invoice # (need it to enter a where statement to return the
proper balance for the Project Deposit and Account Credit). For each of the
other payment types not returning a balance I planned on having the Invoice#,
PaymentType: "the text value currently in the Value List" and Amount: Null.

I am not sure if this is the best way to accomplish this or if I have over
thought so bad and made it 10 times harder than it really is.

Thanks,

Seth

 

[[MVP] S.Clark]

For best results:
1. Do not use Union Queries
2. Do not use Value Lists

Create a table to hold the desired values that you want to display. Use it
in a query to match up your calculated values. Display this info to the
user on the form.

 

[Seth Schwarm]

Thanks. I need some further instruction on how to do this. The reason I ask
is because the available project deposit changes with each invoice a payment
is applied to because each invoice is related to a different project.

All help is appreciated, thanks.

Seth