VBA/SQL Question

[mazzarin]

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.

[Charlie]

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.
>
>

[mazzarin]

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.
> >
> >

[mazzarin]

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.
> > >
> > >