Query to obtain rows that have similar values in different columns

Before getting into the answer, some advice...

This is a really bad design.  If you have any power to do so, I urge you to change it now.

Ideally, the purchase orders and sales orders should be different tables, and the sales orders should use a foreign key to relate back to the purchase orders.

Yes. I totally agree. We are going to get rid of the entire Database very soon as this poor design exists all over.
For now, I have to run this query. Is it possible at all?

Sample data


MyTable
TableID      TransDate      PartName      Qty      PurchaseID      SaleId
1      4/14/2014      Item1      4      3456      
2      4/14/2014      Item1      2      123      34561
3      4/14/2014      Item1      1      234      34561
4      4/14/2014      Item1      1      456      34561
5      4/14/2014      Item1      5      6789      
6      4/14/2014      Item1      2      123      67890
7      4/14/2014      Item1      1      234      67890
8      4/14/2014      Item1      1      345      67890


Query result
PurchaseID      Qty      SalePurch      SumOfQty
3456      4      3456      4
6789      5      6789      4

Thanks a lot. I would need both the queries, the one by omgang and the one by Patrick Matthews. So to compare I get the right data, I run the following queries and both returned 1200 rows.

SELECT MYTABLE.[ID], MYTABLE.Boxes,MYTABLE.Shipbogota,MYTABLE.FLOWER,MYTABLE.[ORDER], MyTableJoin.SaleID, Sum(MyTableJoin.Boxes) AS SumOfQty, MYTABLE.Type
FROM MYTABLE INNER JOIN (SELECT MYTABLE.Boxes, Left([deluser],7) AS SaleID
FROM MYTABLE) AS MyTableJoin ON MYTABLE.[ID] = MyTableJoin.SaleID
GROUP BY MYTABLE.[ID], MYTABLE.boxes, MyTableJoin.SaleID, MYTABLE.shipbogota,MYTABLE.FLOWER, MYTABLE.[ORDER],MyTable.Type
having  MyTable.Boxes <  Sum(MyTableJoin.Boxes)
ORDER by  mytablejoin.SaleID desc

SELECT p.*
FROM  MYTABLE p RIGHT JOIN
(SELECT t1.[ID],Avg (t1.Boxes) AS PurchaseQty, Sum(t2.Boxes) AS SaleQty
FROM  MYTABLE t1 INNER JOIN  MYTABLE AS t2 ON left(t2.deluser,7) =  t1.[ID]
GROUP BY t1.[ID]
HAVING Avg (t1.Boxes) < Sum(t2.Boxes)) x ON p.[ID] = x.[ID]
order by t1.[ID] desc


*************************************************

I now have to add few conditions to the queries like PurchaseType = 'P' and SaleStatus <>'cc'. So I Modified both the queries like attached. Now I get the count different. omgang's query returns 87 rows but Patrick Matthews query is missing few rows and returns 79. Can you please take a look?
(I could merge the two queries to get what I want but just wanted to know what wrong I am doing in the query that returns 79 rows.)
EE-Queries.txt

You've changed the number of fields in each query and so changed how the records are being grouped.

Query 1 (OM Gang)
GROUP BY MYTABLE.[ID], MYTABLE.boxes, MyTableJoin.SaleID, MYTABLE.shipbogota,MYTABLE.FLOWER, MYTABLE.[ORDER],MyTable.Type

Query 2 (Pattrick Matthews)
GROUP BY t1.[ID],t1.type,t2.status

Query 1 has 5 Groupings; Query 2 has only 3.

It makes sense that a query with a larger number of groupings will result in a larger result set.
Consider, if a table contains 1000 records but all records have one of five dates then a grouping on date alone will result in 5 records in the result set.  If all 1000 records also contain one of two userIDs and we add userID as a grouping then we can expect (most likely) to have 10 records in the result set - one record for each userID on each date.  Add another field to GroupBy and the result set gets larger.

All of this may not be any help to you.  What are you needing to accomplish?
I thought that the query I suggested provided the desired result.  By adding a criteria expression it will only display results where the Sum of Qty for SaleID does not equal the PurchaseID Qty.

SELECT MyTable.PurchaseID, MyTable.Qty, MyTableJoin.SalePurch, Sum(MyTableJoin.Qty) AS SumOfQty
FROM MyTable INNER JOIN (SELECT MyTable.TableID, MyTable.Qty, Left([SaleId],4) AS SalePurch FROM MyTable)  AS MyTableJoin ON MyTable.PurchaseID = MyTableJoin.SalePurch
GROUP BY MyTable.PurchaseID, MyTable.Qty, MyTableJoin.SalePurch
HAVING (((Sum(MyTableJoin.Qty))<>[MyTable].[Qty]));

OM Gang

select purchase.qty, purchase.purchaseID, sum(sale.qty) as sum
from yourtable as purchase
inner join
yourtable as sale
on sale.saleID like purchase.purchaseID & '%'
where
purchase.saleID = ''
group by purchase.purchaseID
having purchase.qty <> sum

not sure about the exact syntax because i'm not used to access, but the logic is correct sql-wise. not sure about the way to check for the emptiness of saleID either. maybe you need "is null" or a different comparison. you should first try the query without the "having" clause.

Thank you omgang. I needed what you had done in your query. Just so I get all the columns from the table, I used a tip from Patrick Matthews query and created the attached query.

I now get all the rows where the sum of boxes in the SaleOrders is greater or equal to boxes in the coreesponding PurchaseOrder.
I need to make a new change in the query. When I sum up the boxes in the SaleOrders, I am currently making sure first 7 characters of SaleID are same as the purchase order. Now I have to sum up only those SaleOrders that have the same last 1 character.

MyTable
TableID      TransDate      PartName      Qty      PurchaseID      SaleId
1                   4/14/2014      Item1         4          3456      
2                   4/14/2014      Item1         2          123                  34561
3                   4/14/2014      Item1         3          234                  34561
4                   4/14/2014      Item1         1          456                  34562
5                   4/14/2014      Item1         5          6789      
6                   4/14/2014      Item1         2          102                   67890
7                   4/14/2014      Item1         1          324                   67890
8                   4/14/2014      Item1         3          345                   67891
9                   4/14/2014      Item1         1          567                   67891


Query result
MyTable
TableID      TransDate      PartName      Qty      PurchaseID      SaleId
2                   4/14/2014      Item1         2          123                  34561
3                   4/14/2014      Item1         3          234                  34561

So for order 6789, I should sum up the boxes differently for different right(SaleID,1).
(2+1) < 5 and (3+1)<5. So Order 6789 should not be a part of the result set.

Can you please help?

SELECT MyTable.PurchaseID, MyTable.Qty, MyTableJoin.SalePurch, Sum(MyTableJoin.Qty) AS SumOfQty, MyTableJoin.SaleId
FROM MyTable INNER JOIN (SELECT MyTable.TableID, MyTable.Qty, Left([SaleId],4) AS SalePurch, MyTable.SaleId FROM MyTable)  AS MyTableJoin ON MyTable.PurchaseID = MyTableJoin.SalePurch
GROUP BY MyTable.PurchaseID, MyTable.Qty, MyTableJoin.SalePurch, MyTableJoin.SaleId
HAVING (((Sum(MyTableJoin.Qty))<[MyTable].[Qty]));


Result
PurchaseID      Qty      SalePurch      SumOfQty      SaleId
6789      5      6789      4      67890
6789      5      6789      4      67891


Table Data
TableID      TransDate      PartName      Qty      PurchaseID      SaleId
1      4/14/2014      Item1      4      3456      
2      4/14/2014      Item1      2      123      34561
3      4/14/2014      Item1      1      234      34561
4      4/14/2014      Item1      1      456      34561
5      4/14/2014      Item1      5      6789      
6      4/14/2014      Item1      2      123      67890
7      4/14/2014      Item1      1      234      67890
8      4/14/2014      Item1      1      345      67890
9      4/14/2014      Item1      2      456      67891
10      4/14/2014      Item1      2      567      67891

OM Gang

All I did was add the SaleId to the subquery and then included it in the main query.
OM Gang