Help combining two queries into one (1 Viewer)

[sargon]

First query = Sum Products:

SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;

Second query :

SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));

I need to combine those two query sql code to make only one query and I dont'hjave any idea how to do it

 

[Brianwarnock]

The obvious question is why?

 

[plog]

I do no think its possible. Mainly because in your second query you are trying to GROUP BY fields that not grouped by in the prior query (i.e. SumOfInputs, SumOfOutputs, etc.).

Of course that's only my hunch, it might be possible. The way to do it is to treat the second query like its an algebraic expression where the Summed fields in it are variables. You would then replace those variables with what they are based on in the first query. For example, in the second query you have:

Nz([SumOfInputs],0)-Nz(Sum(Tab1.Outputs),0)


You would replace [SumOfInputs] and [SumOfOutputs] with how they were calculated in the first query:

NZ(Sum(Tab1.Inputs), 0)-Nz([SumOfOutputs],0)

You would also change your FROM clause to that of the first query. Again, not sure if it will work because your second query groups on some of those Summed fields. But you can use the replace method I mentioned above in the GROUP BY clause and it might work.

 

[sargon]

@Brian, I try to migrate from access 2010 to VB2010. I need to make a vb frontend... and VB is so different. I don't know how to use two queries in vb (query + subquery).