Is it possible to construct a function of substracting from 2 queries with the help of Visal Basic, instead of making three queries?
I have to substract the liters from 2 queries and obtain the liters in a third query.The first query has input data and the second query output data.
By substracting them i obtain the balance in the warehouse.
I have done this by means of three queries.However i lack the flexibility,since i have to make a lot of queries for each separate case, i need to make it also for 15 other cities.
My task is to substract the liters from the 2 queries:
First query, called Inparis
SELECT
.[Productid],
.[grade], Sum([order details].[liters]) AS SumOfliters
FROM (orders INNER JOIN customers ON [orders].[customerid]=[customers].[Customerid]) INNER JOIN ([order details] INNER JOIN products ON [order
details].[ProductID]=
.[Productid]) ON [orders].[orderid]=[order details].[OrderID]
WHERE ((([orders].[orderdate])>#1/1/2002#))
GROUP BY
.[Productid],
.[grade], [orders].[customerid]
HAVING (((orders.customerid)=118))
ORDER BY
.[grade];
Second query called OutParis
SELECT
.[Productid],
.[grade], Sum([order details].[liters]) AS [:SumOfliters]
FROM (orders INNER JOIN customers ON [orders].[customerid]=[customers].[Customerid]) INNER JOIN ([order details] INNER JOIN products ON [order
details].[ProductID]=
.[Productid]) ON [orders].[orderid]=[order details].[OrderID]
WHERE ((([customers].[afid])=1) And (([customers].[Customerid])118))
GROUP BY
.[Productid],
.[grade]
ORDER BY
.[grade];
The thrid query:
SELECT [InParis].[Productid], [InParis].[grade], Sum([InParis].[SumOfliters]) AS Input, Sum([OutParis].SumOfliters) AS Output,
Sum([InParis.SumOfliters]-[OutParis.SumOfliters]) AS Balance
FROM InParis INNER JOIN OutParis ON [InParis].[Productid]=[OutParis].[Productid]
GROUP BY [InParis].[Productid], [InParis].[grade];
I need a function something like that
Dim SQLIn As String
Dim SQLOut As String
SQLIn = ……….
SQLOut = ……….
And then: somehow to substract with Run SQL:
Sum([order details].[liters]) AS [:SumOfliters] from the SQLIn – Sum([order details].[liters]) AS [:SumOfliters] from the SQLOut = ??
Any help with this difficult and i am afraid not well described by me problem ?
