Qry using results of a previos query

[theevilsam]

I have a query that produces the following results



Now I need to add some of the values together.I have considered two ways to do this, one is probably gonna get some flack, but I think it may be the easyest option.



1) create an SQL Query that will insert the sum of field (SalesbyHalfPintBottleorCan) where ProductID=1 or ProductID=2 which I came up with the following.

INSERT INTO TblStockFinal (StockLevels)
SELECT (SUM(QrySales1.SalesByHalfPintBottleorCan))
FROM QrySales1
WHERE QrySales1.ProductID=1 OR QrySales1ProductID=2;

I would have numerous SQL's like this that would all be run from a button on an ADMIN form to fill out the table and then create a report form it. the data in the table would be deleted when the report is closed.
I know your not supposed to store calculated data in a table, but I see this as more of a place holder table as opposed to a storage table.

I think that is the easyest option as it can be broken down into multiple parts that are easy to handle.

Option two is to create the report straight off the query.

Either way i still need a query and I am hitting some problems. I was cirtain that you could reference a query result in another query, but I cant seem to get it right, the above query gives a parameter box for you to enter the value for SalesByHalfPintBottleorCan but I want it to sum the values in the column of SalesByHalfPintBottleorCan in the results of the query QrySales1.

I have tried multiple incarnations of the query, with more and less brackets and loads of other little variations and this is just what I gave up on...

Any help would be greatly appreciated

[Epona]

You can reference a query within a query using the QD view but you have to save the query after doing step one so you can use the result in the builder for step two. hope that makes sense

[theevilsam]

How would I use an Iff statement.

I can use a select statement to select what data I need and this works

SELECT Sum(QrySales1.[SalesByHalfPint/BottleorCan]) AS [SumOfSalesByHalfPint/BottleorCan]
FROM QrySales1
WHERE (((QrySales1.ProductID)=1 Or (QrySales1.ProductID)=2));

but I cant seem to get it to insert at all. I want this to be inserted into the table, along with the word "Carling" in a field called "StockDesc" but I thought I'd try it with just the query first.

Any help would be great.

[Epona]

Sorry, my first thought was an iif statement - but i didnt initially understand your question, i thought it was a "if then" case.

What about selecting the id first then summing the total sales?

[alansidman]

Are you able to split out the brand name from the product size so you have two fields? If you can do this then you can run an aggregate query grouping on the brand name and summing on the quantities.

Alan

[theevilsam]

Yeah, went back and reshaped my tables so that sizes were all under the same ID and then was able to do this with ease. lot of work in the short term, but by god there will be a lot less in the long run..

That, I suppose, is why normalisation and good table structure are important in databases...

Thanks for help everyone