• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Query to obtain rows that have similar values in different columns

I have a table like below

Date            Name   Qty    PurchaseID    SaleID
04/14/14     Item1   4        3546
04/14/14     Item1   2        123                35461
04/14/14     Item1   1        234                35462
04/14/14     Item1   1        456                35463

the above table has both purchase orders and sale orders. The SaleID is created by the purchaseID + a new number. So the first 4 digits of a SaleID will be the corresponding purchase ID. I need to make sure that Qty on the purchase ID records is same as the total of the Qty in all the corresponding SaleID.

So the above table totals up correct. But if the last row was missing then the sales would total to Qty = 3 (or) if the last row Qty was 2, the Sales would total to Qty = 5, both of which are not equal to 4.

I would like to find all such rows where Purchase Qty <> total of Sale Qty for a given order.
Please help me build a query. I am running this query in MS Access. Thanks!
0
Angel02
Asked:
Angel02
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
Patrick MatthewsCommented:
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.
0
 
Angel02Author Commented:
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?
0
 
omgangIT ManagerCommented:
Here's an example using a subquery.  The subquery gets the left 4 characters of the SaleID so we can join between PurchaseID and SaleID.

Is this what you're looking for?
OM Gang


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;
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
omgangIT ManagerCommented:
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
0
 
Patrick MatthewsCommented:
If you wanted the query to return both the pruchase order record and the associated sales order records:

SELECT p.*
FROM tblOrders p RIGHT JOIN
(SELECT t1.PurchaseID, Avg(t1.Qty) AS PurchaseQty, Sum(t2.Qty) AS SaleQty
FROM tblOrders t1 INNER JOIN tblOrders AS t2 ON t2.SaleID Like t1.PurchaseID & "*"
GROUP BY t1.PurchaseID
HAVING Avg(t1.Qty) <> Sum(t2.Qty)) x ON p.PurchaseID = x.PurchaseID
UNION
SELECT o.*
FROM tblOrders o RIGHT JOIN
(SELECT t1.PurchaseID, Avg(t1.Qty) AS PurchaseQty, Sum(t2.Qty) AS SaleQty
FROM tblOrders t1 INNER JOIN tblOrders AS t2 ON t2.SaleID Like t1.PurchaseID & "*"
GROUP BY t1.PurchaseID
HAVING Avg(t1.Qty) <> Sum(t2.Qty)) x ON o.SaleID Like x.PurchaseID & "*"

Open in new window

0
 
Angel02Author Commented:
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
0
 
omgangIT ManagerCommented:
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
0
 
skullnobrainsCommented:
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.
0
 
Angel02Author Commented:
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?
0
 
omgangIT ManagerCommented:
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
0
 
omgangIT ManagerCommented:
All I did was add the SaleId to the subquery and then included it in the main query.
OM Gang
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now