Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do amalgamate 3 select queries and then get unique entries from the result
Hi All
Strange request I know, but could somebody give me pointers on how I can put 3 queries into 1 'thing' and then get only the unique entries from this 'thing'. To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queries themselves aren't that complex and all return the same 2 fieldsets of stock code and stock desc. Because these separate queries might bring back the same stock code/description I need to amalgamate the data and then query again to bring out only distinct stock values, eg: Query 1 brings back: stock code stock desc IVP Invoice Paper STP Statement Paper KGC Keyboard Cover etc... etc... Query 2 brings back: stock code stock desc IVP Invoice Paper BOB Back Pack KGC Keyboard Cover etc... etc... Query 3 brings back: stock code stock desc KGC Keyboard Cover 3.5"D 3.5" Disks etc... etc... I need to produce 1 resultset that shows: stock code stock desc IVP Invoice Paper BOB Back Pack 3.5"D 3.5" Disks KGC Keyboard Cover STP Statement Paper etc... etc... (all unique entries) I'm currently just bringing back the 3 query results in Excel, but I'd like to be able to do the above. In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do? Thanks Robbie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do amalgamate 3 select queries and then get unique entries from the result
You'll first have to concatenate the 3 Query results (with one header row)
Then you can use DataFilterAdvanced Filter to eliminate the dupes PS: if you used SQL like: SELECT ... FROM ... (Query 1) UNION SELECT... FROM ... (Query 2) UNION SELECT ... FROM ... (Query 3) you would already get one set of results instead of 3 HTH -- AP "Astra" a écrit dans le message de ... Hi All Strange request I know, but could somebody give me pointers on how I can put 3 queries into 1 'thing' and then get only the unique entries from this 'thing'. To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queries themselves aren't that complex and all return the same 2 fieldsets of stock code and stock desc. Because these separate queries might bring back the same stock code/description I need to amalgamate the data and then query again to bring out only distinct stock values, eg: Query 1 brings back: stock code stock desc IVP Invoice Paper STP Statement Paper KGC Keyboard Cover etc... etc... Query 2 brings back: stock code stock desc IVP Invoice Paper BOB Back Pack KGC Keyboard Cover etc... etc... Query 3 brings back: stock code stock desc KGC Keyboard Cover 3.5"D 3.5" Disks etc... etc... I need to produce 1 resultset that shows: stock code stock desc IVP Invoice Paper BOB Back Pack 3.5"D 3.5" Disks KGC Keyboard Cover STP Statement Paper etc... etc... (all unique entries) I'm currently just bringing back the 3 query results in Excel, but I'd like to be able to do the above. In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do? Thanks Robbie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do amalgamate 3 select queries and then get unique entries from the result
Couldn't you just amalgamate the 3 queries into one, thereby not getting any
duplicates? Saving that, you could use a dictionary object on each set of data, such as Dim dic As Object Dim dicItems Dim i As Long Dim sTemp As String Set dic = CreateObject("Scripting.Dictionary") On Error Resume Next dic.Add "IVP", "Invoice Paper" dic.Add "BOB", "Back Pack" dic.Add "KGC", "Keyboard Cover" dic.Add "KGC", "Keyboard Cover" dic.Add "3.5D", "3.5 Disks" On Error GoTo 0 dicItems = dic.Items For i = 0 To dic.Count - 1 sTemp = sTemp & dicItems(i) & vbNewLine Next MsgBox sTemp Set dic = Nothing -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Astra" wrote in message ... Hi All Strange request I know, but could somebody give me pointers on how I can put 3 queries into 1 'thing' and then get only the unique entries from this 'thing'. To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queries themselves aren't that complex and all return the same 2 fieldsets of stock code and stock desc. Because these separate queries might bring back the same stock code/description I need to amalgamate the data and then query again to bring out only distinct stock values, eg: Query 1 brings back: stock code stock desc IVP Invoice Paper STP Statement Paper KGC Keyboard Cover etc... etc... Query 2 brings back: stock code stock desc IVP Invoice Paper BOB Back Pack KGC Keyboard Cover etc... etc... Query 3 brings back: stock code stock desc KGC Keyboard Cover 3.5"D 3.5" Disks etc... etc... I need to produce 1 resultset that shows: stock code stock desc IVP Invoice Paper BOB Back Pack 3.5"D 3.5" Disks KGC Keyboard Cover STP Statement Paper etc... etc... (all unique entries) I'm currently just bringing back the 3 query results in Excel, but I'd like to be able to do the above. In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do? Thanks Robbie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Queries that will not select data | Excel Discussion (Misc queries) | |||
Unique Entries | Excel Discussion (Misc queries) | |||
Unique Entries | Excel Discussion (Misc queries) | |||
Unique Entries | Excel Worksheet Functions | |||
Unique Entries | Excel Worksheet Functions |