Add multiple querys

[Infinite]

Hello, I have 4 query's, each of them having the SQL code of:

SELECT tblShowSales.EventID, tblShowSales.Date, Sum(tblShowSales.Quantity) AS Quantity, Sum(Nz([Gross Sales],0)+Nz([Tax],0)) AS [Total Sold5]
FROM tblShowSales
GROUP BY tblShowSales.EventID, tblShowSales.Date
HAVING (((tblShowSales.EventID) Not Like '*AMAZON*' And (tblShowSales.EventID) Not Like '*ECWID*') AND ((Year([Date]))=2015));

Each of the 4 query3 are almost %100 percent like that accept for the :

AND ((Year([Date]))=2015));

the 2015 is 2014, 2013, and 2012. What I want, is to have each of those 4 query's added together. Not quite sure how to do that, any help would be much appreciated.


Also, if any one has a better idea for each years total sales (instead of having 1 query for each year as I will have tons of query's in years to come) that would also be appreciated.

 

[pbaldy]

Not a good idea to have multiple queries like that. I'd have a single query, take the year criteria out of it, and use this:

http://www.baldyweb.com/multiselect.htm

 

[Infinite]

I looked at that, and I have no idea how I would integrate that into what I need.

 

[plog]

First, [Date] is a poor choice for a field name because its a reserve word (I think we've had this conversation in prior threads). Also, you should only use alphanumeric characters and underscores in field names ([Gross Sales]=[GrossSales] or [Gross_Sales]).

For your issue, you simply change your WHERE clause to omit the Year([Date]) criteria. If you want to show all years--then don't limit your query to just one.

Also, I'd add a default value of 0 to [Gross Sales] and [Tax]. That way you ensure every record has a numerical value and don't have to use Nz all over the place.