Is this possible to SUM?

  • Thread starter Thread starter Support
  • Start date Start date
S

Support

New to access and queries
I have created a query that will give me negative stock items from our
database.
The query is as follows:

SELECT detail.PLU, Style.Description, detail.Stock, Location.Store
FROM (Location INNER JOIN detail ON Location.Location = detail.Location)
INNER JOIN Style ON detail.UniqueRef = Style.uniqueref
WHERE (((detail.Stock)<0))
ORDER BY detail.Location;

I have created a module and macro that will export this into excel.

The results of the query - and what is exported into excel look like:
PLU Description Stock Store
12345 shoe -1 warehouse
12545 sock -1 warehouse
12345 shoe -1 london
12346 boot -1 london
12347 trouser -1 london

I'd like to also be able to SUM the amount of stock for each location
(store). Is this possible within the same query without changing the look
too much?
I'm currently investingating COUNT also but not getting the results I want.
many thanks
 
Hi,


That is probably a total query. Click on the summation button, from the
query designer. That is the button with a capital Sigma, a kind of 90 degree
rotated M. That makes an extra line, in the query designer, called Total.
Drag all the fields in the grid, except the stock, and keep the proposed
GROUP BY. Next, drag the Stock field in the grid, and change the proposed
Group to a SUM.

That's all.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel however I had tried this already and it does not give the
output that I want (unless I have done this incorrectly although I don't
think so).
I wanted to keep the stock in there showing which item was <0 but also to
add a further column that summed the totals for each location. I can do
this manually within excel however I'd like to automate the process. FYI,
the sql query after I did what you suggest is:
SELECT detail.PLU, Style.Description, Sum(detail.Stock) AS SumOfStock,
Location.Store
FROM (Location INNER JOIN detail ON Location.Location = detail.Location)
INNER JOIN Style ON detail.UniqueRef = Style.uniqueref
GROUP BY detail.PLU, Style.Description, Location.Store, detail.Location
HAVING (((Sum(detail.Stock))<0))
ORDER BY detail.Location;
thanks
 
Hi,

you want to just sum the negative values? Try adding a WHERE clause: drag
the Stock field in the grid, change the proposed GROUP BY into WHERE, add
the criteria: < 0


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top