Union Join: Add records based on criteria

I'm working on this application that combines commissions data with payments by way of a union query.  This query includes a record for the balance.  What I'd like to do is introduce a new table to the union that checks for any minimum payments due.  Specifically, I'd like to total all minimums due for each account, but for only the records with dates prior to today's (the date the summary statement is ran) date.  

For example:
In the current union query, Account 12345 might show
01/03 $500 Commission
02/01 $1000 Commission
02/15 ($700) Pmt
04/07 $800 Balance

So if I have a table with minimums due to this account as follows:
1/15 $500
2/15 $500
3/15 $500
4/15 $500

I'd like the balance, instead of showing $800, to show$1500 (I would exclude summing the 4/15 min. because it's after the date the summary is ran (today's date)

Any ideas how I might approach that?  I basically need to check if the balance is less than the total of the minimums to date, returning the larger of the two.  But I don't know how to accomplish that.
BBluAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

select sum([amountField]) from tableX
where [datefield]<= date()
0
BBluAuthor Commented:
I have this now

SELECT Member, Amount, Type, ActDate
From Q_AllActivity
UNION Select Member, SumOfAmount, "Balance" as Type, Today as ActDate
From Q_Balances
Union Select Member_ID, Sum ([Minimum]), "Minimum" as Type, DueDate
From T_VIP_Minimums
Where [Minimum]<=date()
ORDER BY Member, ActDate;

But I get this error:
"You tried to execute a query that does not include the specified expression 'Member_ID' as part of an aggregate function."
0
Rey Obrero (Capricorn1)Commented:
SELECT Member, Amount, Type, ActDate
From Q_AllActivity
UNION Select Member, SumOfAmount, "Balance" as Type, Today as ActDate
From Q_Balances
Union Select Member_ID, Sum ([Minimum]), "Minimum" as Type, DueDate
From T_VIP_Minimums
Where [Minimum]<=date()
group by Member_ID
ORDER BY Member, ActDate;
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BBluAuthor Commented:
is it easy to explain why I have to put the "group" in there first?
0
BBluAuthor Commented:
I get the same error on 'DueDate' now
0
Rey Obrero (Capricorn1)Commented:
SELECT Member, Amount, Type, ActDate
From Q_AllActivity
UNION Select Member, SumOfAmount, "Balance" as Type, Today as ActDate
From Q_Balances
Union Select Member_ID, Sum ([Minimum]), "Minimum" as Type, DueDate
From T_VIP_Minimums
Where [Minimum]<=date()
group by Member_ID, DueDate
ORDER BY Member, ActDate;

or

SELECT Member, Amount, Type, ActDate
From Q_AllActivity
UNION Select Member, SumOfAmount, "Balance" as Type, Today as ActDate
From Q_Balances
Union Select Member_ID, Sum ([Minimum]), "Minimum" as Type, last(DueDate)
From T_VIP_Minimums
Where [Minimum]<=date()
group by Member_ID, DueDate
ORDER BY Member, ActDate;

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
or

SELECT Member, Amount, Type, ActDate
From Q_AllActivity
UNION Select Member, SumOfAmount, "Balance" as Type, Today as ActDate
From Q_Balances
Union Select Member_ID, Sum ([Minimum]), "Minimum" as Type, last(DueDate)
From T_VIP_Minimums
Where [Minimum]<=date()
group by Member_ID
ORDER BY Member, ActDate;

0
BBluAuthor Commented:
They all run...but can you help me understand what I was doing wrong..so I can learn from my inexperience.
0
Rey Obrero (Capricorn1)Commented:
when you use an aggregate functions like sum,avg etc, in one of the columns in your query, you need to use group by for the rest ofthe columns that did not use any aggregate functions..

see this video

http://www.youtube.com/watch?v=AykV47ObmFA

see this link
http://www.sql-tutorial.com/sql-group-by-sql-tutorial/
0
BBluAuthor Commented:
Thanks, Capricorn1
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.