Results 1 to 3 of 3

Thread: Query problem

  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Unanswered: Query problem

    Hi thanks in advance,
    I have a query on transaction table with the following fields

    InvId-----numeric unique Identifier
    Item------Text description of item
    SalesQty-Numeric number sold
    PurchQty-Numeric Number Puchased
    SalePrice-transaction sale price
    PurchPrice-transaction Purchase Price


    I have a query that calculates inventory Inventoryum Of[Purchases] - Sum Of [Sales]

    this works fine But I want to show the Last cost of the Item and the last sale

    I have tried using the last but this but this returns the last record when I do this for the value one cancels out the other not sure how to proceed from here. Any help would be great thanks Bob

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not to certain what you are trying to do, but I suspect its some form of accountancy valuation rule, probably FIFO

    first off you need to know what the 'last' record is.
    unless you always buy and sell on the same transaction then you need to identify what is the last price of each item. you should be able to do that using sub queries

    so thats going to look something like:-
    Code:
    SELECT Robert_Bob.ProductID,sum( Robert_Bob.salesQty) as TotSales, sum(Robert_Bob.PurchaseQty) as TotPurchase, (select top 1 SalesPrice
      from robert_bob as SP
      where sp.productid = robert_bob.productID and sp.salesprice>0
      order by id DESC) AS SPrice, (select top 1 PurchasePrice
      from robert_bob as PP
      where Pp.productid = robert_bob.productID and Pp.purchaseprice>0
      order by id DESC) AS PPrice
    FROM Robert_Bob
    group by productId;
    you would be better off having another table called, say Products, and use the PK of that table in your transaction table. mind you you'd be better off separating the sales and purchasing transaction but thats another story
    Last edited by healdem; 12-13-13 at 06:01.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2012
    Posts
    56

    Solved

    Hi
    Thanks Healdem for the direction looked at the link and was able to figure it out.

    Thanks Again

    Bob

Posting Permissions

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