Results 1 to 3 of 3
  1. #1
    end is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2016
    Location
    Suwalki, Poland
    Posts
    2

    The problem with the function sum()

    I apologize in advance for my English.
    I do IT project database. I created a sample database wine warehouse. But when I try to calculate the profit and condition of the goods, the program displays wrong results.


    You can buy goods on one ID several times, sell too.


    I'm trying to do it with three tables: Buy, Sell, Commodity.
    Buy: Id_Buy | Id_commodity | Amount | Price
    Sell: Id_Sell | Id_Commodity | Amount | Price
    Commodity: Id_Commodity | Name


    In another table I'm trying to do:
    Name | Sum of one ID : Amount.Buy | Sum of one ID: Amount.Sell | The number of boxes which stay in stock (Amonut.Buy - Amount.Sell) | Profit (Price.Sell - Price.Buy)



    Profit I think it's allright but I have no idea how program is summing amount.buy and amount.sell because it looks like when it's trying to sum of one ID amount.buy it's check how many times it was sold and then It multiplies it to the amount bought (yes, it's hard to understand when someone has no idea how to say it in english)


    My code from the fourth table:
    Code:
    SELECT Commodity.Name , Sum(Buy.Amount) AS Amount.Buy, Sum(Sell.Amount) AS Amount.Sell, (Sum(Buy.Amount)-Sum(Sell.Amount)) AS The number of boxes.., (Sum(Sell.Price)-Sum(Buy.Price)) AS Profit 
    FROM (Buy INNER JOIN Commodity ON Buy.ID_Commodity = Commodity.ID_Commodity) INNER JOIN Sell ON Buy.ID_commodity = Sell.Id_commodity 
    WHERE (((Commodity.Id_commodity) = Buy.Id_Commodity AND (Commodity.Id_commodity) = Sell.Id_commodity))
    GROUP BY Commodity.Name;
    I don't know why program add "INNER JOIN" to this code also.
    If anything is unclear, please ask in the comments.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    doesn't sound like you have your tables constructed correctly. You should have two tables - a commodity table as you have and a transactions table like your buy or sell table but with an extra column to indicate buy or sell

    then it becomes a simple matter of summing the data.

    With the way you have it constructed at the moment if you had 1 buy transaction and 3 sell transactions, your buy transaction would appear three times (one for each sell)

  3. #3
    end is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2016
    Location
    Suwalki, Poland
    Posts
    2
    Quote Originally Posted by Ajax View Post
    doesn't sound like you have your tables constructed correctly. You should have two tables - a commodity table as you have and a transactions table like your buy or sell table but with an extra column to indicate buy or sell

    then it becomes a simple matter of summing the data.

    With the way you have it constructed at the moment if you had 1 buy transaction and 3 sell transactions, your buy transaction would appear three times (one for each sell)
    Thank you!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with IIF function
    By donnysl in forum Queries
    Replies: 3
    Last Post: 08-12-2013, 10:11 AM
  2. Problem with function?
    By devxweb in forum Access
    Replies: 2
    Last Post: 01-07-2013, 08:43 AM
  3. Problem with DAvg function
    By averagejoe in forum Access
    Replies: 1
    Last Post: 10-05-2012, 05:43 AM
  4. Problem with IIF function
    By Hulk in forum Forms
    Replies: 3
    Last Post: 03-20-2011, 12:59 PM
  5. VBA Function problem
    By smikkelsen in forum Programming
    Replies: 5
    Last Post: 07-16-2010, 07:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums