can someone help me with my query?

[Ecron]

Hello. I have been struggling with this problem for quite some time, and it's getting quite frustrating. My knowledge of access is limited and i have been using this forum to educate me in the best of ways possible! But, now i have a question that i really need help on when it comes to the creation of a query.

---------------------
Tables:
tblHayProductionSub
-ProductionSubID
-BalesSold

tblHaySales
-SalesID

tblHayLINKProductionSubIDANDSalesID (junction table)
-ProductionSubID
-SalesID
-NumberOfBales

-------------------------------
I need a query to put on a subform located on the sale's form that restricts the user from adding multiple productionsubs to the one salesid (by creating new records in the junction table). I was able to accomplish so far with the following code.
---------------

SELECT *

FROM tblHayProductionSub

WHERE tblHayProductionSub.ProductionSubID
Not In
(SELECT ProductionSubID FROM tblHayLINKProductionSubIDANDSalesID
WHERE SalesID = [Forms]![frmFarmingHaySales]![SalesID])

-----------------------------------
That is entirely succesful.

I now need to NOT display records where the total 'NumberOfBales' (in the juncftion table) > 'Bales Sold' (located on the production sub table).

Also, i want to display a text box, per record on the continuous subform to display the 'bales available' which would be Sum(NumberOfBales) - Bales Sold, both sorted by their same productionID.

Any help would be greatly appreciated!

Thank you.