Either take Merchant out of the query or change the Totals row from Group By to Where.
Thanks! Now I have 2 Queries. One Query pulls the sum of all the fields and all the merchant #s.
The other Query just pulls the sums of one specific merchant #.
How can I take the 2nd Query and subtract it from the first Query.
Basically I am saying I want all the data EXCEPT a certain merchant #
I'm not sure you need the second query. Can't you just exclude the merchant from the first query? The criteria would look like
<>12345
But then that would give me separate rows for each merchant per store. I removed merchant to get one line per store
I'm back to my first answer; change Group By to Where for the Merchant field.
Ok I tried doing the <>900016 for the criteria under MERCHANT when its set as Where, however it's still adding it to the sum of everything.
Can you post the db, or at least the SQL of that query?
Code:SELECT STREG.DIV, [USSS POS Daily].STORE, Sum([USSS POS Daily].[WTD REDEEM AMOUNT]) AS [SumOfWTD REDEEM], Sum([USSS POS Daily].[WTD TOTAL CREDIT]) AS [SumOfWTD TOTAL CREDIT], Sum([USSS POS Daily].[WTD NET AMOUNT]) AS [SVS NET], Sum([USSS POS Daily].[WTD RELOAD AMOUNT]) AS [SVS RELOAD], Sum([USSS POS Daily].[WTD ISSUE AMOUNT]) AS [SVS ISSUED] FROM STREG INNER JOIN [USSS POS Daily] ON STREG.STR = [USSS POS Daily].STORE WHERE ((([USSS POS Daily].MERCHANT)<>900016) AND (([USSS POS Daily].[Report Date])=[Enter Report Date])) GROUP BY STREG.DIV, [USSS POS Daily].STORE ORDER BY [USSS POS Daily].STORE;
That looks okay offhand, though perhaps something in the join is causing the problem. Can you test without the other table involved?
Wow, I feel dumb. It was working, sorry, I was just looking at the wrong line.
Thanks for all your help!
No problem, glad we got it sorted out. Welcome to the site by the way!
Thanks! This looks like a great site.
One more question regarding this Query. Is there anyway to exclude a row where all the values = 0? For instance, look at the picture at store 5608. What can I do to not have 5608 in my query results.
A criteria of <>0 on each field should work, on different lines. In SQL view, that would translate to "OR", where if you put them on the same line it becomes "AND", which you don't want (I assume). Also in SQL view, it may make it a HAVING instead of a WHERE. In your case, I think either would work. WHERE is applied before aggregation, HAVING after.
I am working in the same database and have another question. My query calls to a table "STREG" that has a list of every store. Then that is linked to another table with that has the "merchant" numbers and stores. I am trying to run a query that pulls all the stores out with a specific merchant #. However, some of those stores don't have that merchant # but I want them to come up anyways. How can I make it so that ALL the stores show up and if that merchant number doesnt exist for that store it just fills in zeros for the rest of the columns?
Here is my SQL for the query.
SELECT STREG.DIV, STREG.STR, Sum([USSS POS Daily].[WTD REDEEM AMOUNT]) AS [SumOfWTD REDEEM], Sum([USSS POS Daily].[WTD TOTAL CREDIT]) AS [SumOfWTD TOTAL CREDIT], Sum([USSS POS Daily].[WTD NET AMOUNT]) AS [SVS NETgamer], Sum([USSS POS Daily].[WTD RELOAD AMOUNT]) AS [SVS RELOAD], Sum([USSS POS Daily].[WTD ISSUE AMOUNT]) AS [SVS ISSUED]
FROM STREG LEFT JOIN [USSS POS Daily] ON STREG.STR = [USSS POS Daily].STORE
WHERE ((([USSS POS Daily].[Report Date])=[Enter Report Date]))
GROUP BY STREG.DIV, STREG.STR, [USSS POS Daily].MERCHANT
HAVING ((([USSS POS Daily].MERCHANT)=66771));