Solved Duplicate rows in result of a select query (1 Viewer)

AnilBagga

Member
Local time
Tomorrow, 01:32
Joined
Apr 9, 2020
Messages
223
I have a query wherein I need to fetch a field "Specifications" from a table "tblItemMaster" . The table is leftJoined to another table - tblPendingOrder and the key field is ItemCode in both tables. ItemCode is not a PK in either of the tables

The issue is that ItemCode has duplicates in the tblItemMaster (as we use the itemmaster also as a BOM too). Therefore if there are 2 records in tblitemmaster for an Itemcode, the query adds one extra row in the result! This is understandable

Is there a workaround that this extra row is NOT added? The "Specifications" are same in both rows for the same ItemCode, and the information of any of these records is fine in the query result!
 

plog

Banishment Pending
Local time
Today, 15:02
Joined
May 11, 2011
Messages
11,611
Can you demonstrate your issue with data? Please provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect the query to produce when you feed it the data from A.

Again, 2 sets of data.
 

AnilBagga

Member
Local time
Tomorrow, 01:32
Joined
Apr 9, 2020
Messages
223
I have compiled a simple DB to show the problem as enclosed. The qry results can be seen in the DB - see screenshot below. In SONo1 ItemCode 12345 appears only once but since it appears 2 times in item master, the query result has added an extra row and both rrecord OD's of ItemMaster can be seen in the last column - 1 and 5

Same issue can be seen for ItemCode12347/SONo1 and SONo2 and ItemCode12348 in SONo 2

1609363211990.png
 

Attachments

  • Test1.zip
    21.8 KB · Views: 408

plog

Banishment Pending
Local time
Today, 15:02
Joined
May 11, 2011
Messages
11,611
That's A. Now show me B. What do you expect your query to return? Don't explain it, show me with data.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:02
Joined
Feb 19, 2013
Messages
16,553
agree with Plog - your requirement is confusing so provide the outcome required from the data you provided. if you just want a single field, why join to another table?

And to be clear - you are familiar with SELECT DISTINCT and/or the use of aggregate queries?
 

AnilBagga

Member
Local time
Tomorrow, 01:32
Joined
Apr 9, 2020
Messages
223
The purpose of the query is to fetch the specifications from ItemMaster Table for each record in pending orders table. While doing this search, it should return the specifications of the first record of the item master table and not ADD an extra row as is happening now

This is just a test table. The actual query has multiple such tables in question

I am not familiar with aggregate queries

What is expected is indicated in the screenshot below of the output of the query

One solution is Dlookup. Is there an option in the query?
 

Attachments

  • qryTest.pdf
    31.1 KB · Views: 488

CJ_London

Super Moderator
Staff member
Local time
Today, 20:02
Joined
Feb 19, 2013
Messages
16,553
use an aggregate query and select min ID

copy/paste this into the sql window
Code:
SELECT tblpendingorder.ID, tblpendingorder.ItemCode, tblpendingorder.SONo, tblItemMaster.Specifications, Min(tblItemMaster.ID) AS MinOfID
FROM tblpendingorder LEFT JOIN tblItemMaster ON tblpendingorder.ItemCode = tblItemMaster.ItemCode
GROUP BY tblpendingorder.ID, tblpendingorder.ItemCode, tblpendingorder.SONo, tblItemMaster.Specifications;

and you will get this result

IDItemCodeSONoSpecificationsMinOfID
1​
123451Test1
1​
2​
123461Test2
2​
3​
123471Test3
3​
4​
123482Test4
4​
5​
123492Test5
7​
6​
123502Test6
6​
7​
123452Test1
1​
8​
123472Test3
3​
 

AnilBagga

Member
Local time
Tomorrow, 01:32
Joined
Apr 9, 2020
Messages
223
use an aggregate query and select min ID

copy/paste this into the sql window
Code:
SELECT tblpendingorder.ID, tblpendingorder.ItemCode, tblpendingorder.SONo, tblItemMaster.Specifications, Min(tblItemMaster.ID) AS MinOfID
FROM tblpendingorder LEFT JOIN tblItemMaster ON tblpendingorder.ItemCode = tblItemMaster.ItemCode
GROUP BY tblpendingorder.ID, tblpendingorder.ItemCode, tblpendingorder.SONo, tblItemMaster.Specifications;

and you will get this result

IDItemCodeSONoSpecificationsMinOfID
1​
123451Test1
1​
2​
123461Test2
2​
3​
123471Test3
3​
4​
123482Test4
4​
5​
123492Test5
7​
6​
123502Test6
6​
7​
123452Test1
1​
8​
123472Test3
3​
Thanks
 

Users who are viewing this thread

Top Bottom