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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|