Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/SQL Question
I am trying to generate some worksheets that utilizes VBA/ADODB to
query the MSSQL db for a given series. With that series information, it should return PART_NOs that has STD = 1 and a unique price at that particular 'START', and keeping the 'TYPE' in consideration... DB examples below: Main DB ID PART_NO SERIES STD 1 A-1 A 1 2 A-2 A 1 3 A-3 A 1 4 D-1 D 1 5 D-2 D 0 Price DB ID PART_ID TYPE START PRICE 50 1 X 1000 50 51 1 X 10000 40 52 1 Y 1000 60 53 1 Y 10000 50 54 2 X 1000 50 55 2 X 10000 40 56 2 Y 1000 60 57 2 Y 10000 50 58 2 X 1000 90 etc. main.ID and Price.PART_ID are paired together. So in an example case, lets say I am querying for SERIES A, with TYPE X. A table should be outputted something like PART_NO A-1 1000 50 A-1 10000 40 A-3 1000 90 Note how it skipped printing A2 because the price is the same as A1. I'm really looking for the SQL code here... I can't get it to filter on distinct price. ..Open "SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE FROM MAIN, PRICING WHERE (MAIN.SERIES LIKE " & givenseries & ") AND (MAIN.STD = '1') AND (PRICING.PRICE != '') AND (PRICING.TYPE = 'X') AND (MAIN.ID = PRICING.PART_ID)", dbConn, adOpenStatic I've been trying to use GROUP BY and HAVING to get what I need but it doesn't seem to fit the bill. I guess I'm not terribly clear on how I can use the SQL DISTINCT command...? Thanks for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/SQL Question
Without looking too deeply into what it is you are trying to do, I can tell
you I had trouble with "Select Distinct" and "Order By". I was able to use "Select Distinct" but was not able to get it to work in conjunction with "Order By". I'm not surprised "Group By" is also giving you trouble. I don't know why. e.g. "Select distinct * from TableName where Whatever = 'something' Order By somefield;" (didn't sort the data) So I chose to just do a "Select Distinct" and do my own internal sort. "mazzarin" wrote: I am trying to generate some worksheets that utilizes VBA/ADODB to query the MSSQL db for a given series. With that series information, it should return PART_NOs that has STD = 1 and a unique price at that particular 'START', and keeping the 'TYPE' in consideration... DB examples below: Main DB ID PART_NO SERIES STD 1 A-1 A 1 2 A-2 A 1 3 A-3 A 1 4 D-1 D 1 5 D-2 D 0 Price DB ID PART_ID TYPE START PRICE 50 1 X 1000 50 51 1 X 10000 40 52 1 Y 1000 60 53 1 Y 10000 50 54 2 X 1000 50 55 2 X 10000 40 56 2 Y 1000 60 57 2 Y 10000 50 58 2 X 1000 90 etc. main.ID and Price.PART_ID are paired together. So in an example case, lets say I am querying for SERIES A, with TYPE X. A table should be outputted something like PART_NO A-1 1000 50 A-1 10000 40 A-3 1000 90 Note how it skipped printing A2 because the price is the same as A1. I'm really looking for the SQL code here... I can't get it to filter on distinct price. ..Open "SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE FROM MAIN, PRICING WHERE (MAIN.SERIES LIKE " & givenseries & ") AND (MAIN.STD = '1') AND (PRICING.PRICE != '') AND (PRICING.TYPE = 'X') AND (MAIN.ID = PRICING.PART_ID)", dbConn, adOpenStatic I've been trying to use GROUP BY and HAVING to get what I need but it doesn't seem to fit the bill. I guess I'm not terribly clear on how I can use the SQL DISTINCT command...? Thanks for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/SQL Question
Yeah, perhaps I should restate my problem...
SQL distinct does what I want when you get down to it, but it completely destroys the organization of the table. The part numbers were entered in a certain manner and I do not believe they can be duplicated through any typical sort. For example A-105A is higher then A-400B, but if you sorted in Excel (for example) it would put 105 below 400 Charlie wrote: Without looking too deeply into what it is you are trying to do, I can tell you I had trouble with "Select Distinct" and "Order By". I was able to use "Select Distinct" but was not able to get it to work in conjunction with "Order By". I'm not surprised "Group By" is also giving you trouble. I don't know why. e.g. "Select distinct * from TableName where Whatever = 'something' Order By somefield;" (didn't sort the data) So I chose to just do a "Select Distinct" and do my own internal sort. "mazzarin" wrote: I am trying to generate some worksheets that utilizes VBA/ADODB to query the MSSQL db for a given series. With that series information, it should return PART_NOs that has STD = 1 and a unique price at that particular 'START', and keeping the 'TYPE' in consideration... DB examples below: Main DB ID PART_NO SERIES STD 1 A-1 A 1 2 A-2 A 1 3 A-3 A 1 4 D-1 D 1 5 D-2 D 0 Price DB ID PART_ID TYPE START PRICE 50 1 X 1000 50 51 1 X 10000 40 52 1 Y 1000 60 53 1 Y 10000 50 54 2 X 1000 50 55 2 X 10000 40 56 2 Y 1000 60 57 2 Y 10000 50 58 2 X 1000 90 etc. main.ID and Price.PART_ID are paired together. So in an example case, lets say I am querying for SERIES A, with TYPE X. A table should be outputted something like PART_NO A-1 1000 50 A-1 10000 40 A-3 1000 90 Note how it skipped printing A2 because the price is the same as A1. I'm really looking for the SQL code here... I can't get it to filter on distinct price. ..Open "SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE FROM MAIN, PRICING WHERE (MAIN.SERIES LIKE " & givenseries & ") AND (MAIN.STD = '1') AND (PRICING.PRICE != '') AND (PRICING.TYPE = 'X') AND (MAIN.ID = PRICING.PART_ID)", dbConn, adOpenStatic I've been trying to use GROUP BY and HAVING to get what I need but it doesn't seem to fit the bill. I guess I'm not terribly clear on how I can use the SQL DISTINCT command...? Thanks for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/SQL Question
Actually never mind, it doesn't do exactly what I want it to do... All
the prices are still duplicated, ideally I should only have at most 3 results being returned (according to the actual data being fed in) I am beyond confused heh I think I might have to do the filtering outside of SQL Any help is appreciated :) mazzarin wrote: Yeah, perhaps I should restate my problem... SQL distinct does what I want when you get down to it, but it completely destroys the organization of the table. The part numbers were entered in a certain manner and I do not believe they can be duplicated through any typical sort. For example A-105A is higher then A-400B, but if you sorted in Excel (for example) it would put 105 below 400 Charlie wrote: Without looking too deeply into what it is you are trying to do, I can tell you I had trouble with "Select Distinct" and "Order By". I was able to use "Select Distinct" but was not able to get it to work in conjunction with "Order By". I'm not surprised "Group By" is also giving you trouble. I don't know why. e.g. "Select distinct * from TableName where Whatever = 'something' Order By somefield;" (didn't sort the data) So I chose to just do a "Select Distinct" and do my own internal sort. "mazzarin" wrote: I am trying to generate some worksheets that utilizes VBA/ADODB to query the MSSQL db for a given series. With that series information, it should return PART_NOs that has STD = 1 and a unique price at that particular 'START', and keeping the 'TYPE' in consideration... DB examples below: Main DB ID PART_NO SERIES STD 1 A-1 A 1 2 A-2 A 1 3 A-3 A 1 4 D-1 D 1 5 D-2 D 0 Price DB ID PART_ID TYPE START PRICE 50 1 X 1000 50 51 1 X 10000 40 52 1 Y 1000 60 53 1 Y 10000 50 54 2 X 1000 50 55 2 X 10000 40 56 2 Y 1000 60 57 2 Y 10000 50 58 2 X 1000 90 etc. main.ID and Price.PART_ID are paired together. So in an example case, lets say I am querying for SERIES A, with TYPE X. A table should be outputted something like PART_NO A-1 1000 50 A-1 10000 40 A-3 1000 90 Note how it skipped printing A2 because the price is the same as A1. I'm really looking for the SQL code here... I can't get it to filter on distinct price. ..Open "SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE FROM MAIN, PRICING WHERE (MAIN.SERIES LIKE " & givenseries & ") AND (MAIN.STD = '1') AND (PRICING.PRICE != '') AND (PRICING.TYPE = 'X') AND (MAIN.ID = PRICING.PART_ID)", dbConn, adOpenStatic I've been trying to use GROUP BY and HAVING to get what I need but it doesn't seem to fit the bill. I guess I'm not terribly clear on how I can use the SQL DISTINCT command...? Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|