Subquery

[hoachen]

I am trying to compare two fields data if there are match.

Table 1:
item#
123

Table 2:
Item# ItemName Edition creator
123 Name1 05 Smith
456 EkName2 05 Smith
111 Name1 11th 10 Larry
656 EkName4 latest John

The result should return below, because the Edition and the creator match
even though the ItemName and the item# does not match.
123 Name1 05 Smith
456 EkName2 05 Smith

I tried SELECT ItemName, Edition (select temp.ItemName, Edition, from
[ItemTable] AS temp Where ItemName like "ek*") from [ItemTable] Order Edition

 

[John Spencer]

Two query method:
First query saved as TheSavedQuery
SELECT Edition, Creator
FROM ItemTable INNER JOIN [Table1]
ON ItemTable.[Item#] = [Table1].[Item#]

Second query uses the ItemTable table and the saved query.
SELECT ItemTable.*
FROM ItemTable INNER JOIN TheSavedQuery
ON ItemTable.Edition = TheSavedQuery.Edition
AND ItemTable.Creator = TheSavedQuery.Creator



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Try these two queries --
qryPullMatch --
SELECT [Item#], ItemName, Edition, Creator, (select temp.[Item#]
FROM [ItemTable] AS temp Where temp.Edition = [ItemTable].Edition AND
[ItemTable].Creator = temp.Creator) AS temp1
FROM [ItemTable]
ORDER BY Edition, Creator;

SELECT [Item#], ItemName, Edition, Creator
FROM [ItemTable]
ORDER BY Edition, Creator
UNION ALL SELECT [Item#], ItemName, Edition, Creator
FROM [ItemTable] INNER JOIN qryPullMatch ON [ItemTable].[Item#] =
qryPullMatch.temp1
ORDER BY Edition, Creator;