Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Visual Basic help (Access 2000)

    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 [products].[Productid], [products].[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]=[products].[Productid]) ON [orders].[orderid]=[order details].[OrderID]
    WHERE ((([orders].[orderdate])>#1/1/2002#))
    GROUP BY [products].[Productid], [products].[grade], [orders].[customerid]
    HAVING (((orders.customerid)=118))
    ORDER BY [products].[grade];


    Second query called OutParis
    SELECT [products].[Productid], [products].[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]=[products].[Productid]) ON [orders].[orderid]=[order details].[OrderID]
    WHERE ((([customers].[afid])=1) And (([customers].[Customerid])<>118))
    GROUP BY [products].[Productid], [products].[grade]
    ORDER BY [products].[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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visual Basic help (Access 2000)

    I assume that the 118 is the ID for one of the cities (customers?). You can replace this with a parameter. This can be an interactive parameter (to be filled in manually each time the queries are run) or the value of a control on a form (or report). Perhaps, you don't need VBA if you use a parameter.

    An interactive parameter looks like [Enter CustomerID]
    A reference to a control on a form looks like [Forms]![frmMyForm]![txtCustomerID] where frmMyForm is the name of the form and txtCustomerID the name of the control.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visual Basic help (Access 2000)

    Thank you for your reply.Yes, i will use a parameter later on, but my problem is substracting from 2 queries.For say customer Nr 118, i have 2 queries
    which are differennt.I want to substract the liters from the 2 queries.I can do it with three queries,but i woulld like to use VBA in order to make things more flexible,use parameters,etc.Can you help me further?If you look at my thrid query,you will see it is based on the previous 2 queries.i want to aoid the use of three queries,if possble.Actually the thrid query only substracts the quantity from the 1 st query and the 2nd query.So,to make my question more clear please forget the customer,and take it to be a constant figure. If i solve the case for this customer,as you have already noted i can solve it for ther other customners too.

    Best regards

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Visual Basic help (Access 2000)

    Why do you want to avoid using 3 queries? If 3 queries work, why fix it?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •