Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query to obtain rows that have similar values in different columns

Posted on 2014-04-22
11
Medium Priority
?
258 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 93

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 1600 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 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
 

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 27

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

926 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