Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    decreased liters in pivot query (Access 2000)

    I want to put as a criteria in the query only those customers which liters are less than the previous quarter.May you help me ? The query is :
    TRANSFORM Sum([order details].liters) AS SumOfLiters
    SELECT customers.Customerid
    FROM (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    GROUP BY customers.Customerid
    PIVOT Format([InvoiceDate],"q");

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

    Re: decreased liters in pivot query (Access 2000)

    > only those customers which liters are less than the previous quarter

    Do you want to include a customer if there is a decrease in at least one quarter, or in all quarters?

    Do you also want to compare the first quarter of this year to the fourth quarter of last year?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: decreased liters in pivot query (Access 2000)

    Thank you very much Hans ! Much appreciated. I want to include a customer if there is a decrease compared with the previous quarter.For example, if we have 100 liters in the third quarter and 90 liters in the fourth quarter.
    Next year, the query should beging in the second quarter comparing with the first quarter,then when we are in the third quarter, compare third quarter with second quarter.
    We want to compare always with the previous quarter and will not compare with the quantities made in the first quarter.
    Regards

    Freelance

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

    Re: decreased liters in pivot query (Access 2000)

    OK, you must create a new query based on your crosstab query. Let's say that your crosstab query is named qryCross.
    The SQL for the new query looks like this:
    <code>
    SELECT *
    FROM [qryCross]
    WHERE [2]<[1] OR [3]<[2] OR [4]<[3]
    </code>
    (When you switch to design view and save the query, Access will change the SQL slightly, but the idea will remain the same)

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: decreased liters in pivot query (Access 2000)

    Excellent ! Super ! I cannot believe my eyes, but it works so fine ! Thank you thank you !

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: decreased liters in pivot query (Access 2000)

    Dear Hans

    I would like to improve my query a bit. First the query does not take into account the cases where there are no liters at all. For example, if there are 20 liters in the third quarter, and no liters in the fourth quarter, the query does not show it as a decrease.Also, there are cases when there are no liters in the third quarter, and no liters in the fourth quarter, but there are some in the previous quarters, can i take them also into account ?

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

    Re: decreased liters in pivot query (Access 2000)

    Try this in the crosstab query:

    TRANSFORM Nz(Sum([order details].liters),0) AS SumOfLiters
    ...

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: decreased liters in pivot query (Access 2000)

    Thank you ,i undestand now, thats great !

Posting Permissions

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