If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subquery
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 |
#2
|
|||
|
|||
Subquery
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 hoachen wrote: 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 |
#3
|
|||
|
|||
Subquery
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; -- Build a little, test a little. "hoachen" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|