SQL instead of a query ?


Is it possible to write an expression in SQL instead of writing a thrird query? I have 2 queries, called inflow and outflow
and in a thrid query i substract the sum of the cartons.It works perfect in this way, but i need to make this more flexible,in order
to use more variables. Therefore i tried to write the queries in an SQL form, and after that i have placed the result in the OnOpen event of a report
like that:

Me.RecordSource = Inflow.Sum([order details].cartons) - Outflow. Sum([order details].cartons
However i receive the message "Not a valid expression"

I am afraid what i am doing contradict to the basic rules in Acces and i ask for help.I will repeat the 2 queries i have as follows:

Inflow

SELECT products.grade, products.size, Sum([order details].cartons) AS SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity
FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid =

[order details].OrderID) ON products.Productid = [order details].ProductID
WHERE (((orders.customerid)=118) AND ((orders.orderdate)>#12/1/2002#))
GROUP BY products.grade, products.size;


Outflow :
SELECT products.grade, products.size, Sum([order details].cartons) AS SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity
FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid =

[order details].OrderID) ON products.Productid = [order details].ProductID
WHERE (((orders.orderdate)>#1/1/2002#) AND ((orders.customerid)<>118) AND ((customers.afid)=2))
GROUP BY products.grade, products.size;

AFter that i tried the following in the OnOpen event of a report:

Dim Inflow As String
Dim OutflowAs String
Inflow = " SELECT products.grade, products.size, Sum([order details].cartons) AS SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity
FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid =

[order details].OrderID) ON products.Productid = [order details].ProductID
WHERE (((orders.customerid)=118) AND ((orders.orderdate)>#12/1/2002#))
GROUP BY products.grade, products.size "

Outflow = " SELECT products.grade, products.size, Sum([order details].cartons) AS SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity
FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid =

[order details].OrderID) ON products.Productid = [order details].ProductID
WHERE (((orders.customerid)=118) AND ((orders.orderdate)>#12/1/2002#))
GROUP BY products.grade, products.size "



Me.RecordSource = Inflow.Sum([order details].cartons) - Outflow. Sum([order details].cartons

Comments

  • :
    : Is it possible to write an expression in SQL instead of writing a thrird query? I have 2 queries, called inflow and outflow
    : and in a thrid query i substract the sum of the cartons.It works perfect in this way, but i need to make this more flexible,in order
    : to use more variables. Therefore i tried to write the queries in an SQL form, and after that i have placed the result in the OnOpen event of a report
    : like that:
    :
    : Me.RecordSource = Inflow.Sum([order details].cartons) - Outflow. Sum([order details].cartons
    : However i receive the message "Not a valid expression"
    :
    : I am afraid what i am doing contradict to the basic rules in Acces and i ask for help.I will repeat the 2 queries i have as follows:
    :
    : Inflow
    :
    : SELECT products.grade, products.size, Sum([order details].cartons) AS SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity
    : FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid =
    :
    : [order details].OrderID) ON products.Productid = [order details].ProductID
    : WHERE (((orders.customerid)=118) AND ((orders.orderdate)>#12/1/2002#))
    : GROUP BY products.grade, products.size;
    :
    :
    : Outflow :
    : SELECT products.grade, products.size, Sum([order details].cartons) AS SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity
    : FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid =
    :
    : [order details].OrderID) ON products.Productid = [order details].ProductID
    : WHERE (((orders.orderdate)>#1/1/2002#) AND ((orders.customerid)<>118) AND ((customers.afid)=2))
    : GROUP BY products.grade, products.size;
    :
    : AFter that i tried the following in the OnOpen event of a report:
    :
    : Dim Inflow As String
    : Dim OutflowAs String
    : Inflow = " SELECT products.grade, products.size, Sum([order details].cartons) AS SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity
    : FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid =
    :
    : [order details].OrderID) ON products.Productid = [order details].ProductID
    : WHERE (((orders.customerid)=118) AND ((orders.orderdate)>#12/1/2002#))
    : GROUP BY products.grade, products.size "
    :
    : Outflow = " SELECT products.grade, products.size, Sum([order details].cartons) AS SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity
    : FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid =
    :
    : [order details].OrderID) ON products.Productid = [order details].ProductID
    : WHERE (((orders.customerid)=118) AND ((orders.orderdate)>#12/1/2002#))
    : GROUP BY products.grade, products.size "
    :
    :
    :
    : Me.RecordSource = Inflow.Sum([order details].cartons) - Outflow. Sum([order details].cartons
    :

    hi,
    set db =currentdb
    set rec1=db.openrecordset("inflow")
    set rec2=db.openrecordset("outflow")

    Me.RecordSource = rec1![SumOfcartons] - rec2![SumOfcartons]


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion