Visual Basic :: Multiple SELECT DISTINCT

Need Help With Select Distinct
I have two tables tbltermin,users.
users has fields id,name,vorname
tbltermin has userid,wiederholungid

The following code works

Code:
adouser.RecordSource = "SELECT * FROM tbltermin INNER JOIN users ON tbltermin.userid = users.id WHERE wiederholungid = " & wiederholungid

grouplist.AddItem adouser.Recordset("Name") & adouser.Recordset("Vorname")
Now I want to change the first line to
SELECT DISTINCT userid FROM tbltermin etc

But then it gives me an error by the second line, it can't find name and vorname

Any suggestions? I am using adodc

Select Distinct And Then Sum
I have a table of components [id],[Range Name],[Component Name],[Type]

Type is a long which I use as a single bit pattern, ie values are only 1,2,4,8,16 etc

What I'm trying to do..
Determine for each [Range Name] the Boolean OR of all the Types in that range. So if components in "Range 1" were Type=1 and Type=4 the result of my query would be "Range 1",5

I figure I could do it in 2 queries
FirstQuery
SELECT DISTINCT [Range Name],[Type]
FROM Components

Gets me 1 record for each different combination of [Range Name] and [Type]

Then the second query acting on the results of the first...
SELECT FirstQuery.Range, Sum(FirstQuery.Type) AS SumOfType
FROM FirstQuery
GROUP BY FirstQuery.Range;

Since I will only have 1 record for each type (per range) I can simply use the SUM function to effectively OR the [Type] values together.

My question is this...
Can this be done with a single query instead?

Any suggestions apprieciated...

Select Distinct
I need to select the distinct Category values that appear in a table to use them to drive a loop processing the table contents. I need the categories to be sorted by first the Order field, and then the Category field. However, I can't figure out how to do this. If I pull the distinct values from the table itself:
sqlstr2 = "SELECT DISTINCT FAQsKB.category FROM FAQsKB WHERE FAQsKB." & group & " = True"
Set cats = dbs.OpenRecordset(sqlstr2, dbOpenDynaset)
I get each category listed once. However, they appear in alphabetical order of category.
If I pull the distinct values from a query that sorts the data the way I want it to be sorted, each category appears multiple times.
I've also tried sorting the table on the appropriate fields, but this has no effect on the output.
It's not possible to add an "Order by" statement to the Select distinct statement, unless it's to order by the category field. Which doesn't help. )
Anyone have an idea how I can get a list of just the distinct categories yet force them to be sorted as I want?
Thanks!

Select Distinct
I'm stuck.

I have a table with names and addresses. I need a list of names and addresses, but only distinct names, so each person gets one letter at one of their addresses.

http://www.accuflight.com
http://www.lettersdirect.co.uk
http://www.ourhouseinportugal.co.uk/

Select Distinct ?
Hello,

I have a table which contains a few fields 2 of which being "Reference" and "StoreNo". There are a few duplicate records in my table, these will have the same "Reference". I'm worried about using SELECT DISTINCT because 1 store may have many records. How would i write this query safely?

Code:
sSQL = "SELECT DISTINCT Reference, Status, Date, ClosedDate, Contractor, ServiceCode " & _
    "FROM Export.txt WHERE Date > " & lDate & _
    " AND Contractor = '" & ContractorCode & "'" & _
    " AND Status <> 'ONSALE'"

thankyou.
Bob

Select Distinct
I want to select a orderNo and a orderDate from a database,

But I want the results distinct by the orderNo ONLY, how do I do that??

scottie.uk

Sort 'SELECT DISTINCT'
How can I sort a sql by its ID:

SELECT DISTINCT Address FROM Human

As SELECT DISTINCT Address FROM Human ORDER BY ID
Or SELECT DISTINCT Address, ID FROM Human ORDER BY ID
are all not possible.

Select Distinct With A Twist
I have a table where I have put in a number of records where a lot of then are identical except for an ID field... what I want to be able to is select all distinct records with the lowest ID field where field1 are identical

the fist part is simple enough.

Select distinct table.field1, table.field2, table.ID........
from table
where ***** ID = (select max (ID) as lowest FROM table) AND how do I get the that to match selcting rows where field1 match too in the same statement

Select Distinct Problem
I'm trying to select a number of fields from a SQL 2000 recordset only when the first field is unique..

So I would want to return

Value1 Value14
Value2 Value32
value3 Value34

from the following data

Field1 Field2
Value1 value14
Value1 value12
Value2 value32
Value2 value23
Value3 value34

I've seen the select distinct on function mentioned for other versions of SQL, is there anything I can do in SQL to retun the above results???

SELECT DISTINCT Statement
I am looking to use the SELECT DISTINCT SQL statment to select a particular field where the first seven characters are DISTINCT.

For example if i have
VVV07G3111
VVV07G3112
VVV07G3113
VVV08G3110
VVV08G3109
VVV08W7001
VVV08W7002
VVV07W7145
VVV07W7146

It will return this:
VVV07G3
VVV08G3
VVV07W7
VVV08W7

I am using Access for this instance.

SELECT Distinct Problem
Since SQL doesn't allow case-sensitive Distinct searches, how can I select data like this:

Code:
Select Distinct name From clients
Database contains:
Brian
brian
Brian
bRian

How can I make the results be (3 distinct names instead of 1)?

SELECT DISTINCT Troubles
Hi my wonderful vbforum friends, been a while since i've had to vb it up but here i am and struggling with an access query: -

Code:
SELECT client.client_id, client.first_name, client.last_name, action_client.action_id
FROM action_client RIGHT JOIN client ON action_client.client_id = client.client_id;
returns:

Code:
client_id first_name last_name action_id
1 bill jones 4
1 bill jones 3
1 bill jones 6
2 sam smith 3
3 bob simms 4
I want to return each of the unique client_id's with no duplicates regardless of whether there is an action_id or not, what am i doing wrong?

Help muchness appreciated

Cheers!

*P.S. yeah i did spend ages lining up that table to avoid gettin on with work! hmmm moti... motiv... motivatiowhat?

Select Distinct Not Working
i tried to run this statement but i got some errors...can someone show me how to do this correctly? i want to filter my employee records showing only the records with equivalent transactions on a given date...

VB Code:
d = Date strGroupSQL = "SELECT * FROM tblContact " strDetailsSQL = "SELECT tblContact.ID, tblContact.Name, tblTransaction.Total, tblTransaction.TransID, tblTransaction.Paid, tblTransaction.Total, " strDetailsSQL = strDetailsSQL & "tblTransaction.Total - tblTransaction.Paid AS Balance ,tblTransaction.DueDate, tblTransaction.Settled " strDetailsSQL = strDetailsSQL & "FROM tblContact Where tblContact.ID IN (Select Distinct tblTransaction.ID from tblTransaction WHERE tblTransaction.Settled LIKE 0 AND tblTransaction.DueDate like " & d & ")" strShapeSQL = "SHAPE {" & strGroupSQL & "} " strShapeSQL = strShapeSQL & "APPEND ({" & strDetailsSQL & "} AS GrpDetails " strShapeSQL = strShapeSQL & "RELATE ID to ID) "

SQL - Select Distinct Question
I have a database with a field called CustomerSKU. This field contains values such as:

aa123
ab1234
AA123
AB1234

If I do a "Select Distinct [CustomerSKU] from table xyz"

I get a recordset that contains only AA123 & AB1234. The Select Distinct "sees" the aa123 & the AA123 as the same value.

How can I get SQL to "see" both the aa123 & the AA123 as "distinct"? I have about 10,000 entries in my table and I'd really like to be able to use lower case letters and have them be "distinct".

Problem In SELECT DISTINCT
The SELECT DISTINCT does not work if the field type is Memo, anybody out there who could tell me how to do about it? Please????

Select DISTINCT Records Question
Code:
JobID Description TypeID Hours Date1
TP04067L Power 801 3 20050609
TP03087M Power 801 8 20050610
BL05109 Power 801 2 20050613
BL04211 Power 801 9 20050613
BL04090 Power 801 4 20050614
TP05009C Power 801 8 20050615
TP05009C Power 801 10 20050616
BL03658 Power 801 4 20050616
TP04067N Power 801 4 20050617
BL03658 Power 801 2 20050617
TP03087N Power 801 8 20050617
Given the data set above, is there a way to select only the distinct JobID's by week? Example: TP05009C has 2 entries for the week beginning 6/13/2005. I only need 1 record (the earlier of the 2, if possible).

tia,
flynn

Select Distinct Year And Month
Hi
I have an SQl server 2000 table containing a field PDate,the format of the field is:
d/m/yyyy.

I want to select the distinct years present in the database and the distinct months related to each year.

For example if the database contains:
7/8/2006
12/25/2005
10/19/2006
2/14/2006
6/24/2005

so i have 2 distincts years:2005 and 2006
2005 has the following months: 12(December) and 6(June)
2006 has the following months: 7(July),10(october) and 2(February)

I want to load the results into 2 comboboxes,cboyear and cbomonth.

How can I do it?
thanks in advance

SQL Select Distinct Records (UN-Resolved)
I am trying to combine records from 2 access tables that only have one common field called PermitNum. they are both in the same Access mdb file. Except for the common field, all field names are unique

Table 1 has 10 records
table 2 has 5 records

No matter what I try (even an Access query) I get 50 records. All I am looking for is a recordset with 15 records.

The purpose of the query is to print a report in date order for all charges and payments for a specific customer.

Select Distinct Rows In Excel
Hi All,

how to find distinct row in excel

I have A:1 to A:158 cells and they have values like
aa
bb
bb
cc
cd
aa
sp

I want to get only distinct values. How can I do that?
Please help me.

Thanks
Chitra

Sqlce Select Distinct Problem
Hello,

Can anyone tell me what could be the problem that this code:

"SELECT DISTINCT Line FROM Location"

is not working?

i'm using evb 3.0, sqlce 2.0 (at least this reference i'm including in project: Microsoft CE SQL Server Control 2.0) and an emulator from embedded visual tools.

best regards,
Silviu

Select Distinct Not Working In Shape Command
i tried to run this statement but i got some errors...can someone show me how to do this correctly? i want to filter my employee records showing only the records with equivalent transactions on a given date...

[vbcode]
d = Date
strGroupSQL = "SELECT * FROM tblContact "

strDetailsSQL = "SELECT tblContact.ID, tblContact.Name, tblTransaction.Total, tblTransaction.TransID, tblTransaction.Paid, tblTransaction.Total, "
strDetailsSQL = strDetailsSQL & "tblTransaction.Total - tblTransaction.Paid AS Balance ,tblTransaction.DueDate, tblTransaction.Settled "
strDetailsSQL = strDetailsSQL & "FROM tblContact Where tblContact.ID IN (Select Distinct tblTransaction.ID from tblTransaction WHERE tblTransaction.Settled LIKE 0 AND tblTransaction.DueDate like " & d & ")"

strShapeSQL = "SHAPE {" & strGroupSQL & "} "
strShapeSQL = strShapeSQL & "APPEND ({" & strDetailsSQL & "} AS GrpDetails "
strShapeSQL = strShapeSQL & "RELATE ID to ID) "

[/vbcode]

Select Distinct Records -- Database Menu
CR = Version XI Enterprise Edition

Designing a report, I have 3 tables that are used and linked.
InventoryHistory
PartStatus
tempAltHistory
All 3 tables have a Primary key called dbPartNumber. InventoryHistory is linked to PartStatus; InventoryHistory is linked to TempAltHistory.

From a VB6 application, a part number is being passed to the report as a formula for selection.

The correct record is being selected. That is not the problem. The problem is: I am getting multiple occurrences of the same record being printed on the report. When I went to the Database menu, "Select Distinct Records" is not enabled therefore I can not check it. I don't understand why it's not enabled?

PartStatus and TempAltHistory database fields are ONLY used in the Page Header Section.

InventoryHistory database fields are ONLY being used in the Detail Section.

I have checked the tables and there are not duplicates in the table.
Here's the SQL statements that were generated by CR:

Code:
SELECT `InventoryHistory`.`dbTransDate`, `InventoryHistory`.`dbQtyAdjRecv`,
`InventoryHistory`.`dbStockQty`,
`InventoryHistory`.`dbReason`, `InventoryHistory`.`dbUnitPrice`,
`InventoryHistory`.`dbTime`, `InventoryHistory`.`dbLocation`,
`InventoryHistory`.`dbEnterDate`, `InventoryHistory`.`dbPartNumber`, `InventoryHistory`.`dbUserName`
FROM `InventoryHistory` `InventoryHistory`
ORDER BY `InventoryHistory`.`dbTransDate` DESC, `InventoryHistory`.`dbTime` DESC

SELECT `PartStatus`.`dbPartDescription`, `PartStatus`.`dbIsThisNonInv`, `PartStatus`.`dbPartNumber`
FROM `PartStatus` `PartStatus`

SELECT `TempAltHistory`.`dbOnHand`, `TempAltHistory`.`dbOnOrder`, `TempAltHistory`.`dbPartNumber`

SET XACT_ABORT ON Causes Edit Loss On Select Distinct
I have to admit this is one of the weirdest problems I have ever run into.

I using VB6 connecting through ADO (2.1 - 2.6 - tried several of them) to a MS SQL Server 2000 database.

When I do a SET XACT_ABORT ON, create an ADO transaction, edit data in the transaction and then open a recordset using a select distinct clause, my edits are lost.

If I do just a normal select, the edits still remain in the transaction. Now if I do a SET XACT_ABORT OFF, the edits remain even if I do a select distinct which leads me to believe it is somethign with the SET XACT_ABORT ON.

I do the XACT_ABORT setting because I need to handle nested transactions (triggers are involved with some of the updates I am doing).

Below is an example code that causes the problem:

Code:
Private Sub Form_Load()
Dim x As ADODB.Connection
Dim y As ADODB.Recordset

Set x = New ADODB.Connection
Set y = New ADODB.Recordset

x.ConnectionString = _
"Provider=sqloledb;Data Source=vsx1d60x;Initial Catalog=land_surface_develop;integrated security=SSPI"

x.Open

'set xact abort on to handle nested transactions
x.Execute "SET XACT_ABORT ON"

'Create my ADO transaction
x.BeginTrans

'Update a value in a table. Tee original value was '037'
x.Execute "UPDATE site set nghd_id = '048' where site_id = 1646987"

'To prove that the update has went through show the value. Value will be '048'
y.Open "select nghd_id from site where site_id = 1646987", x, adOpenForwardOnly, adLockOptimistic
MsgBox y("nghd_id")
y.Close

'Here is where the problem occurs. I can do a select distinct from any table I choose. If the
'recordset is opened against the same connection as the transaction, the edits are lost.
y.Open "select distinct park_id from park"
y.Close

'Select the data we updated. The value displayed will be '037' which is the original value before the
'edit had taken place. If you take out the distinct in the above statement, this will display
''048' as value which is the expected outcome. In addition, if you don't do the SET XACT_ABORT ON
'this will say '048' whether you do a select distinct or not.
y.Open "select nghd_id from site where site_id = 1646987", x, adOpenForwardOnly, adLockOptimistic
MsgBox y("nghd_id")
y.Close

x.RollbackTrans
x.Execute "SET XACT_ABORT OFF"

x.Close

Set y = Nothing
Set x = Nothing

End Sub

Has anyone come across this before and maybe have an explanation for it?

Any help is greatly appreciated. Thanks

Select Distinct Not Working In SHAPE Command
i tried to run this statement but i got some errors...can someone show me how to do this correctly? i want to filter my employee records showing only the records with equivalent transactions on a given date...

[vbcode]
d = Date
   strGroupSQL = "SELECT * FROM tblContact "

   strDetailsSQL = "SELECT tblContact.ID, tblContact.Name, tblTransaction.Total, tblTransaction.TransID, tblTransaction.Paid, tblTransaction.Total, "
   strDetailsSQL = strDetailsSQL & "tblTransaction.Total - tblTransaction.Paid AS Balance ,tblTransaction.DueDate, tblTransaction.Settled "
   strDetailsSQL = strDetailsSQL & "FROM tblContact Where tblContact.ID IN (Select Distinct tblTransaction.ID from tblTransaction WHERE tblTransaction.Settled LIKE 0 AND tblTransaction.DueDate like " & d & ")"

   strShapeSQL = "SHAPE {" & strGroupSQL & "} "
   strShapeSQL = strShapeSQL & "APPEND ({" & strDetailsSQL & "} AS GrpDetails "
   strShapeSQL = strShapeSQL & "RELATE ID to ID) "

[/vbcode]

Sql Select Distinct - Doesnt Return Unique Values
Does anybody have any idea why the foollowing SQL statement does not return unique values for the surname field?

Code:sqlDataString = "Select DISTINCT CustID,Surname,Postcode from customer order by Surname"

(custId is the primary key)

mark

How To Give Sql Command "select Distinct ....." In VB6.0 With Access
Hi,

I have two text boxes,

ex:- text1.text = "23-06-04"
text2.text = "26-06-04"

i want give the sql command like

"select distinct column where date between --text1.text and ---text2.text--"

how to send the values of text boxes with above sql command.

"Select Distinct MyField From MyTable " Error !
I got a question , when I use distinct with my sql statement .

There always comes with a exception .

Why ? Can't i use " Distinct " ??

ps. I use .cdb file

"Select Distinct" From An Access Database
Hi All,

I wish to look in a recordset, select one of each vehicle name, put it in a list box, and then go to each of those vehicles and select data from the record with the greatest value for runtime and add it to the list box with the vehicle name. I have started with some code using the select distinct command which I have used in the past for one varable but it will not work with multiple varables. can anyone help with this problem. I will attach the database and the code.

Thanks,

Ben

Code: Public Sub VehicleList()

Dim Counter, Vehicle, Priority, RunTime, JobNo
Dim rs As ADODB.Recordset
Dim strvehicle As String

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "Driver.mdb;Mode=ReadWrite;Persist Security Info=False"
cn.Open

    Set rs = New ADODB.Recordset
    rs.Open "select distinct(Vehicle) from Vehicles", cn

        Counter = 0
        Do Until rs.EOF
            Counter = Counter + 1
            Vehicle = rs!Vehicle
            'Priority = rs!Priority
            'RunTime = rs!RunTime
            'JobNo = rs!JobNo
            strvehicle = Vehicle & " " & Priority & " " & JobNo & " " & RunTime
            Me.LstVehicles.AddItem (strvehicle)
            rs.MoveNext
        Loop

    rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing

End Sub

Edited by - Ben Lee on 5/5/2004 11:15:46 AM

Simulate Select "DISTINCT" In ADOCE
Hi!
I got this problem,this query brings me what I need, but it bring it twice, i can solve this problem in Access with the statement "DISTINTC" but isnt supported in ADOCE, Can anybody tell me How can I do it in ADOCE.
Any suggets will be much appreciated.

SELECT
Clientes.Nombre, Deudas.*
FROM
Vendedor_Cliente
INNER JOIN
(Clientes INNER JOIN Deudas ON Clientes.Id_Cliente = Deudas.Id_Cliente)
ON
Vendedor_Cliente.ID_Cliente = Clientes.ID_Cliente

Carlos Amaya.-Venezuela

SQL "SELECT DISTINCT" Need Help Asap!
I understand that i can use

Select DISTINCT field1
from table1

and it works fine. however it will only return that one field. I need something similar to

SELECT DISTINCT(field1) *
from table1

so it will pull all distince rows from field1 but also return the rest of my fields.

tia!

Rob

Prevent Multiple Select In Multiple Combos
Hi,

I have a control array of 13 combo boxes cmb6Pos(0) to cmb6Pos(12) that are all loaded with the same list of alphanumeric codes. How can I prevent a code from being selected in a combo box if it has already been selected in another?

Any pointers or code would be great, thanks,

Mat

Select Multiple
Just need to know how to select multiple files in a commondialog box by dragging the mouse or holding the ctrl button (like win media player). -Thanks

Multiple Select
I need the user to be able to select multiple directories.
I can't seem to use DriveListBox or Common Dialog.
Does anybody know another way or a neat control that lets me do it?
Otherwise I think I'll have to write my own using a ListBox...

Multiple Select
I want to select multiple files from Common Dialog ...
How to do it ...?

Multiple Select?????
Hi
How to Select multiple Files using CommonDialog Control?
Plz tell me
Thanks in Advance
Karthik

Multiple Select In MSHF
Hi guys,

Is it possible to select two or more 'non-adjacent' rows in MSHF.
( f.e. the way it can be done in the ListView (keeping CTRL pressed) ?

Thanks in advance,

Shurik12.

Multiple Select Case
Is there anyway of using a multiple select case statement?

I have two variables called BlueU a GreenU. I want to check a range of cells for their colour and value.

If they are Blue and contain the value U, then add one to Blue U and the same for GreenU if it is Green and has a value of U.

If it's anything else, don't add one to anything.

How To Select Multiple Checkboxes?
Hi VB Pros

I have a large grid of checkboxes, if i were to selected a group of checkboxes, how can i check all those checkboxes that are being selected?

Thanks a lot

Multiple Select In ComboBox [VB6]
Given a Form [Form1] with a ComboBox [cbText].
Currently the ComboBox is a dropdown that allows the user to select one of the choices, what I need is to extend this behavior so that the user can (if required) select more then one of the choices at a time.

Something similar to holding down Ctrl and selecting multiple choices from the list.
Can this be done?

Also what effect will this have on the display?
Meaning, currently the cbText starts blank, I click the dropdown arrow and pick a choice, this choice is then placed in the cbText. What happens if I have multiple Selections? What is placed in the cbText combobox (that we see)?

Multiple Select With Code?
How do I select multiple items from a listbox with code, using the listbox.text and the listbox.listindex properties?

Multiple Select On A Datagrid?
searching the forums and google.com gave me squat.

basically one of my fields is email, my boss wants to be able to select multiple rows (in the ctrl-click fashion) and email those mutiple selected rows.

How Do You Multiple Select In Commondialog?
I can do it easily in a file list box, so is there any reason I can't do it in a commondialog.showopen box?

Select Multiple Case
I was wondering if there was a conditional statement which like performed multiple blocks of code rather than just one.

i.e.

Code:
select case num
case 0
<do code>
case 1
<do other code>
End select
The above would only execute one piece of code.. i want one to execute multiple pieces of code.

ie.

Code:
select case num
case 0
<do code>
case 0,1
<do other code>
End select
i was wondering if num = 0 if it could execute both blocks of code. I do know this particular example doesnt work. but wondering if there was a piece of code which did work.

Other than really long separate If statments, as this would make my code look really long and inefficient.

Multiple Select In Listview
I'm trying to figure out how to tell what records are selected in a listview when the user clicks a button. Lets say the user uses the shift key to select the first 5 records. How can I tell what records are selected when he clicks the button? If I look at the selectedItem it only gives me the last one selected. Thanks for the help.

Select From Multiple Tables
Hi,

I have three tables:

phone_types: idtype, description
phone_object: id_object, description
phone_number: familyid, idtype, idobject, phone_number

When I choose a family: I want to be able to display
type(description) | object(description) | phone_number

but now on a flexgrid i'm only able to display :
idtype | idobject | phone_number

- is using a flexgrid a good idea?
- how do I change my select?

Select From Multiple Tables
I have two tables in an access database that have a 1 to 1 relationship. I want to pull out all fields from the two related records. Something like this:
"SELECT * FROM PersonalData,Charges WHERE PersonalData.ClientID = Charges.ClientID"
Except this always gives me the last record created which I dont want. I want a specific record. I have the ID number of the record I want in a variable called IDnum. How do I get all fields from both tables for that specific ID number (IDnum)?

"SELECT * FROM PersonalData,Charges WHERE PersonalData.ClientID = " & "' IDnum"' & " = " & Charges.ClientID" (A horrible attempt at guessing) or something like that?

Anybody?

Multiple Select In Datagrid [VB6]
Given a Form [Form1] with a Datagrid [dgRes]
I need to implement a Multiple Selection mechanism (does one already exist?) for the Datagrid so that users can select (highlight I assume) multiple records then, for example, click a button that will do something with all the selected records.

If this was done 1 Record at a time it is easy (I use the CLICK event of the Datagrid to load that rows information), so the only problem is how do I select Multiple Rows at a time? (I try clicking on one row then holding shift or ctrl and clicking on another but this does not work).

Any clues?

Multiple SELECT DISTINCT

Is there anyway to do a multiple Select Distinct and only get distinct from the each field you pick? ie: Code:SELECT DISTINCT field1, field2, field3, field4 FROM table

Is there anyway to do a multiple Select Distinct and only get distinct from the each field you pick? ie:

Code:
SELECT DISTINCT field1, field2, field3, field4 FROM table