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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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:
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
All Courses

From novice to tech pro — start learning today.