Solved

Query to obtain rows that have similar values in different columns

Posted on 2014-04-22
11
243 Views
Last Modified: 2014-05-02
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
Comment
Question by:Angel02
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40015676
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
 

Author Comment

by:Angel02
ID: 40015689
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
 
LVL 28

Accepted Solution

by:
omgang earned 400 total points
ID: 40015733
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
 
LVL 28

Expert Comment

by:omgang
ID: 40015747
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 40015908
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Angel02
ID: 40020432
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
 
LVL 28

Expert Comment

by:omgang
ID: 40020833
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
 
LVL 26

Expert Comment

by:skullnobrains
ID: 40028977
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
 

Author Comment

by:Angel02
ID: 40030788
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
 
LVL 28

Expert Comment

by:omgang
ID: 40032560
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
 
LVL 28

Expert Comment

by:omgang
ID: 40032572
All I did was add the SaleId to the subquery and then included it in the main query.
OM Gang
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now