Problem with query

[shultzcarla]

We have made a inventory database in access 2010 (our first ever database!!) and are having trouble with a query that calculates our percentage sold from each bin. It seems that it works if you have unique bin number fields in each table however if you were to add to the same bin twice in the harvest table or take from the same bin twice in the sales table it seems to double the total of everything including opening inventory. All we want it to do is add the opening inventory to harvested subtract the sold and give a total by commodity. Here is the sql of the percentage sold query SELECT OpeningBalT.Commodity, Sum(OpeningBalT.Amount) AS SumOfAmount, Sum(HarvestT.AmountBu) AS SumOfAmountBu, Sum(SalesTransferT.AmountBu) AS SumOfAmountBUSold, Nz([SumOfAmount],0)+Nz([SumOfAmountBU],0)-Nz([SumOfAmountBUSold],0) AS TotalonHand, Nz([SumofAmountBuSold],0)/Nz([TotalonHand],0) AS PercentageSold FROM (OpeningBalT LEFT JOIN HarvestT ON OpeningBalT.BinNum = HarvestT.BinNum) LEFT JOIN SalesTransferT ON OpeningBalT.BinNum = SalesTransferT.BinNum GROUP BY OpeningBalT.Commodity; Thanks so much for your help

[Robeen]

If you add to - or subtract from - a BinNum . . . do you then have two entries for that BinNum in the Table?

If you could post a screenshot of your data / table design, it would be easier to see what's going on.

[shultzcarla]

Thanks for your reply...Yes when I add to or subtract from a BinNum that is when I get two entries created.
I will attach a screen shot of the design view of the query. Hope this helps...please let me know if you need anything else. I appreciate your help so much!

[Robeen]

Does your query give you more than one row of data for those instances where there has been more than one addition or subtraction?
Try creating a query each for your Harvest & SalesTransfer tables like this:
1. QryHarvestT:

Select BinNum, Sum(AmountBU) As AmountAdded
From HarvestT
Group By BinNum;

2. QrySalesTransferT:

Select BinNum, Sum(AmountBU) As AmountSold
From SalesTransferT
Group By BinNum;

Then - instead of using the HarvestT & SalesTransferT tables in your final query - use the OpeningBalT table - and the two queries shown above.
The two queries are making sure that duplicate/multiple BunNums are summed into one row and their Amounts summed.

See if that works & let us know.

[shultzcarla]

Thanks so much for you suggestion....sorry for the delayed response I wasn't able to work on it right away!
I tried the two separate queries got them to work!! But when I used the two queries instead of the tables in my final query it didn't show all the records.

Here are my two codes for the final query:
OLD
SELECT OpeningBalT.Commodity, Sum(OpeningBalT.Amount) AS SumOfAmount, Sum(HarvestT.AmountBu) AS SumOfAmountBu, Sum(SalesTransferT.AmountBu) AS SumOfAmountBUSold, Nz([SumOfAmount],0)+Nz([SumOfAmountBU],0)-Nz([SumOfAmountBUSold],0) AS TotalonHand, Nz([SumofAmountBuSold],0)/Nz([TotalonHand],0) AS PercentageSold
FROM (OpeningBalT LEFT JOIN HarvestT ON OpeningBalT.BinNum = HarvestT.BinNum) LEFT JOIN SalesTransferT ON OpeningBalT.BinNum = SalesTransferT.BinNum
GROUP BY OpeningBalT.Commodity;

NEW
SELECT OpeningBalT.Commodity, Sum(OpeningBalT.Amount) AS SumOfAmount, Sum(HarvestQ.SumOfAmountBU) AS SumOfAmountBUHarvested, Sum(SalesQ.SumOfAmountBU) AS SumOfAmountBUSold, Nz([SumofAmountBUSold],0)/Nz([TotalonHand],0) AS PercentageSold, Nz([SumOfAmount],0)+Nz([SumOfAmountBUHarvested],0)-Nz([SumOfAmountBUSold],0) AS TotalonHand
FROM (OpeningBalT INNER JOIN HarvestQ ON OpeningBalT.BinNum = HarvestQ.BinNum) INNER JOIN SalesQ ON OpeningBalT.BinNum = SalesQ.BinNum
GROUP BY OpeningBalT.Commodity;

Let me know if you need anymore information. Thanks again for your help!

[shultzcarla]

Here is a screen shot of the final query

[Robeen]

One thing I noticed right away is that in the first query you posted - your joins are LEFT joins.
In the new query you created, you have INNER joins.
If you look at the two screenshots you posted, you will notice that the first one has arrows goiing from the left table to the other two tables in the query.
The second one does not have these arrows.
Right-click on each of the lines in your new query, open 'Join Properties' and select option 2 in each case.
You will notice the arrows appear at the end of the connecting join lines when you do this.
Also - you will then notice that in the SQL of your query, there will be 'LEFT JOIN' instead of 'INNER JOIN'.
OR - instead - in the SQL you can change the word INNER . . . to LEFT in the two instances in the query. That should have the same effect.
This will give you ALL the records from your 'OpeningBalT' table - and matching values from the two queries you created.
Hope this works for you. Let us know.

[shultzcarla]

Thanks so much that worked!

[Robeen]

Happy to help!