[RESOLVED] cannot DISTINCT 1 field And 2 Fields at the same time why?

[salsa31]

hey
i am trying to Distinct and load 1 field to the listview and another one as regular but it is not working why?
what i am missing ?
this is my code

Select DISTINCT PriceName

Dim RsQ As New ADODB.Recordset
        
RsQ.Open "Select DISTINCT PriceName,PriceCost From PriceList order by PriceName", CN
        Do While Not RsQ.EOF
        Set itm = FrmTipulimToGeneralSale.LTreat.ListItems.Add(, , RsQ!PriceName, 1)
            itm.SubItems(1) = FormatCurrency(RsQ!PriceCost)
        RsQ.MoveNext
        Loop
 RsQ.Close

tnx for any help
salsa31

[Arnoutdv]

Do you really have items in the table with the same PriceName?
Can multiple records with the same PriceName have a different PriceCost?
If so, how would you know which PriceCost you want to display?

[salsa31]

Do you really have items in the table with the same PriceName?
Can multiple records with the same PriceName have a different PriceCost?
If so, how would you know which PriceCost you want to display?

same price name yes

how would you know which PriceCost you want to display?

that is why i want to DISTINCT

[Arnoutdv]

Let's fabricate an example of your table:

PriceName   PriceCost
Red         10
Red         10
Red         12
Red         14
Red         14
Blue        20
Blue        20
Blue        23
Blue        23

Which output do you want?

[salsa31]

each pricename has as Diffrent PriceCost

in your E.X there are 5 reds and 4 blue
i need my list to show 1 red and 1 blue
thats why i need to use the distinct

[Arnoutdv]

PriceName   PriceCost
Red         10
Red         12
Red         14
Blue        20
Blue        23

Which output do you want?

Distinct PriceName will give you:
- Red
- Blue

Which PriceCost should be associated with the unique values?

[salsa31]

which output do you want?

Distinct pricename will give you:
- red
- blue

which pricecost should be associated with the unique values?

yes sir

[techgnome]

If you select distinct pricename ... you will get Red & Blue ... but you need more than that, right? OK. Problem is you also have PriceCost in there... how do you know which ONE of those you want? There's nothing to make them unique.

Ideally you should have had two tables... one with PriceName and an ID ... the other with ID, PriceNameID, PriceCost, and possibly some kind of date. that will then let you get back to a specific date and identify the specific price in effect.

LEss than ideal, you have it all in one table... PriceName, PriceCost and a date...

In both cases you would group by price name and get the max date... then join that back to the table on both fields and that would give you all of the PriceCosts for each of the PriceNames.


-tg

[Schmidt]

yes sir

I take this "yes" as:
Yes the PriceCosts shall be "associated" with the (distinct) PriceName.

And since there are different (multiple) PriceCosts you will have to somehow
"gather" or "aggregate" behind the unique PriceNames - why not use the
SQL-construct which was made for these kind of "aggregations".

The Group By Clause is quite similar to Distinct, in that you can define
"the distinct bucket" for which to gather (or aggregate) certain other FieldValues.

"Select PriceName, Sum(PriceCost) From PriceList Group By PriceName"

The above has the distinct "Bucket" (PriceName) colored in magenta, the Aggregate-Function (Sum) in blue.

There's other Aggreate-Functions as e.g.:
- Avg(PriceCost) ... in case you want the Average of the PriceCosts
- Count(PriceCost) ... in case you want only the Count of the PriceCosts in the Bucket.

Olaf

[salsa31]

Got it Tnx amigos

[Arnoutdv]

Which solution did you choose?