[RESOLVED] Again Please tell me how to tell vb6 this?

[samer22]

Hello
Using Ado, please tell me how to tell vb6 that if all the values in a given column of access table are the same then msgbox "....." but if at least there is one different value in the same column then msgbox "....."

I spent nearly a whole night trying and trying but without success.
Example
PID
1
1
1
1
1
msgbox "the values are the same in "PID"

PID
1
1
1
2
1
3
msgbox "the values are not the same in "PID"
Is this possible?

Thanks

[ColinE66]

Well, one easy way is to execute this query

SELECT PID FROM Table GROUP BY PID
or
SELECT DISTINCT PID FROM table

then count the rows returned. 1 is good. More than 1 not so good.

[Carlos Rocha]

Select distinct PID from ... > 1 ?

[ColinE66]

Of course, you should prevent 'bad' data from getting into your tables in the first place rather than fire-fighting afterwards but sometimes it's unavoidable..

[samer22]

Thank you all for the help
But I don't know if what you are suggesting meets my need or not.
The values in PID column are not necessarely constant.
I mean they can be:
PID
2
2
2
2
3
2
4
the query I need is to check if the values in PID are similar or not.
I tested this query but I don't know wether I'm on the right path or not.
RS.Open "Select distinct PID from T1 where PID > " & 1 , DB, adOpenStatic, adLockOptimistic
If RS.RecordCount > 1 Then
MsgBox "not same"
Else
MsgBox "same"
End If
But it always returns (same)
thanks

[SamOscarBrown]

just use: RS.Open "Select distinct PID from T1", DB, adOpenStatic, adLockOptimistic

[samer22]

SELECT PID FROM Table GROUP BY PID

I think it is working with this query
Thank you a lot

[SamOscarBrown]

Yes, that would work as well.

[Carlos Rocha]

SELECT PID FROM Table GROUP BY PID

I think it is working with this query
Thank you a lot

I don't think so. Please check again

This should work:

RS.Open "Select distinct PID from T1", DB, adOpenStatic, adLockOptimistic 
If RS.RecordCount > 1 Then
MsgBox "not same"
Else
MsgBox "same"
End If

[SamOscarBrown]

Carlos....BOTH "SELECT PID FROM T1 GROUP BY PID" and "SELECT DISTINCT PID FROM T1" WILL work. Why do you say the former will not????

[masoudk1990]

SELECT PID FROM Table GROUP BY PID

A little performance
SELECT 1 FROM Table GROUP BY PID

[gibra]

A little performance
SELECT 1 FROM Table GROUP BY PID

What?

[masoudk1990]

What?

There is a slight overhead when you use field name instead of 1

[Carlos Rocha]

Carlos....BOTH "SELECT PID FROM T1 GROUP BY PID" and "SELECT DISTINCT PID FROM T1" WILL work. Why do you say the former will not????

Sam, I don't think I'm mistaken. Using the last sample data wouldn't the "group by" clause force the return 2,3,4?
Testing with SQLite it works like this

[Carlos Rocha]

There is a slight overhead when you use field name instead of 1

How do you know PID is the first field?

[masoudk1990]

Sam, I don't think I'm mistaken. Using the last sample data wouldn't the "group by" clause force the return 2,3,4?
Testing with SQLite it works like this

Yes it returns 2,3,4. Which means after executing the query RecordCount will become equal to 3. And it means there is 3 distinct ID and its totally correct.

[masoudk1990]

How do you know PID is the first field?

1 doest mean first column. Try this:

CREATE TABLE [dbo].[table1](
	[a] [int] NOT NULL,
	[b] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 3)
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 3)
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 7)

select 1 from table1 group by b

[Carlos Rocha]

Yes it returns 2,3,4. Which means after executing the query RecordCount will become equal to 3. And it means there is 3 distinct ID and its totally correct.

Oh, sorry. My bad

[Carlos Rocha]

1 doest mean first column. Try this:

CREATE TABLE [dbo].[table1](
    [a] [int] NOT NULL,
    [b] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 3)
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 3)
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 7)

select 1 from table1 group by b

masoudk1990, you are right again, sorry

[FunkyDexter]

Assuming this is SQL Server you can improve it further with a count distinct:-

Select Count(Distinct PID)
From myTable

That will return you a single cell table with the count of individual PIDs in it. I'm not sure if that will work with other DBs and I'm not sure whether it's in the Ansii standard or not so if you're using some other DB my best advice is just to try it out and see what happens.

[gibra]

1 doest mean first column. Try this:

CREATE TABLE [dbo].[table1](
    [a] [int] NOT NULL,
    [b] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 3)
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 3)
INSERT [dbo].[table1] ([a], [b]) VALUES (0, 7)

select 1 from table1 group by b

This doesn't work!

[Carlos Rocha]

Another option to improve performance would be to limit RS to 2, like in
select 1 from T1 group by PID limit 2

[SamOscarBrown]

Do you honestly think samer22 is concerned with performance? OP is obviously a beginner...I'd think it best just to advise on HOW to return what is needed, rather than getting into the weeds quite so much. If this was a thread on performance, I'd say ya'll are 'right on' with providing these insights, but maybe not for the OP (at least in this juncture of his/hers learning curve.

[gibra]

Do you honestly think samer22 is concerned with performance? OP is obviously a beginner...I'd think it best just to advise on HOW to return what is needed, rather than getting into the weeds quite so much. If this was a thread on performance, I'd say ya'll are 'right on' with providing these insights, but maybe not for the OP (at least in this juncture of his/hers learning curve.

I agree 100%.

[FunkyDexter]

This doesn't work!

Works fine for me

Do you honestly think samer22

I sort of half agree. While I wouldn't want to blind him with science I do think the general principal of doing as much work as possible in the query and transmitting as little data back and forth across the connection is a good one for anyone to take on early and I don't think the examples given here have been difficult to follow.

[ColinE66]

Assuming this is SQL Server you can improve it further with a count distinct:-

Select Count(Distinct PID)
From myTable

That will return you a single cell table with the count of individual PIDs in it. I'm not sure if that will work with other DBs and I'm not sure whether it's in the Ansii standard or not so if you're using some other DB my best advice is just to try it out and see what happens.

That was my original thought but I wasn't sure how it translated to the OP's DBMS so I went with something more universal. There are a few ways to skin this particular cat!

[masoudk1990]

Select Count(Distinct PID)
From myTable

That was my original thought but I wasn't sure how it translated to the OP's DBMS so I went with something more universal. There are a few ways to skin this particular cat!

Yes, I tested it and it didnt work on access 2010. But all other examples worked as well.

[samer22]

Two days without internet
Thank you all very much
I'm so grateful to you
Problem solved with both queries: distinct and group by
Thank you again

[samer22]

Execuse me to open again this thread.
I want to ask if this query is possible.

RS.Open "SELECT TEST FROM GradesTable GROUP BY TEST where PID like '" & Text2.Text & "'", DB, adOpenStatic, adLockOptimistic
If RS.RecordCount > 1 Then

I want via this query to check whether a given PID has values which are not all the same.
example:
PID TEST
1 10
1 10
1 11
2 12
2 12
2 12
The example above shows that PID 2 has the same values however PID 2 has a different value.
I tried with the query above but did succeed.
I'm sorry again

[SamOscarBrown]

Almost...do your "GROUP BY" after your "WHERE" statement.
You didn't run into an error on that query of yours?

you know...you REALLY should ask these DB questions in the Database Development Section.

I'm going to ask an Admin to move'

[samer22]

SamOscarBrown
Please I want to keep my post here because the members here know quite well that I'm just a beginner.
For the query, Itested this query but it doesn't return correct results.

RS.Open "SELECT TEST FROM GradesTable where PID like '" & Text2.Text & "', GROUP BY TEST , DB, adOpenStatic, adLockOptimistic

Thanks

[ColinE66]

Lose the comma and put the GROUP BY within the double quotes.

"SELECT TEST FROM GradesTable where PID = '" & Text2.Text & "' GROUP BY TEST"

you don't need LIKE either, in this case. = will do.

[samer22]

I think I got it
This is the right syntax

RS.Open "SELECT TEST FROM GradesTable where PID like '" & Text2.Text & "', GROUP BY TEST , DB, adOpenStatic,
 adLockOptimistic

Thank you very much all of you

[samer22]

ColinE66
Sorry I have written my reply before I saw yours.
When I put = instead of like I get an error
type de données incompatible...
Thanks

[samer22]

What about if we want to select more than a field in a distinct or group by query.

RS.Open "SELECT TEST, exam FROM GradesTable where PID like '" & Text2.Text & "', GROUP BY TEST , DB, adOpenStatic,
 adLockOptimistic

Is this possible?

[masoudk1990]

What about if we want to select more than a field in a distinct or group by query.

RS.Open "SELECT TEST, exam FROM GradesTable where PID like '" & Text2.Text & "', GROUP BY TEST , DB, adOpenStatic,
 adLockOptimistic

Is this possible?

Yes, but not like this.
When you use GROUP BY in your queries all columns in select section must be inside GROUP BY section too.

RS.Open "SELECT TEST, exam FROM GradesTable where PID like '" & Text2.Text & "', GROUP BY TEST,exam" , DB, adOpenStatic,
 adLockOptimistic

Or if you dont want to include it in group by section you must use one of aggregate functions for it in select section.

RS.Open "SELECT TEST, COUNT(exam) FROM GradesTable where PID like '" & Text2.Text & "', GROUP BY TEST" , DB, adOpenStatic,
 adLockOptimistic

By the way the LIKE you wrote make no sense and you can replace it with =

To understand what like use for in queries read this:
www.w3schools.com/sql/sql_like.asp

[FunkyDexter]

Please I want to keep my post here because the members here know quite well that I'm just a beginner.

I've seen you say this before. You do know that the database section is part of this forum don't you. Anyone from this section who knows about databases will also visit the database section along with a bunch of other database experts who aren't necessarily VB6 experts and they will be every bit as sympathetic to the fact that you're a beginner. The database section is a far more suitable home for your thread and you'd be likely to get better help there.

If you really want this left in the VB6 section I'm happy to respect that but I do think you're doing yourself a disservice. The answers you're getting in this section are very good but the ones you'd get in the DB section are likely to be even better.

By the way, you really should specify what database you're using. An answer that is right for Access may be wrong for SQLServer or Oracle and vice versa. Without knowing which database your using we may accidentally give you bad advice.

[samer22]

masoudk1990
Thanks a lot for the help.
For this query it quite OK

RS.Open "SELECT TEST, exam FROM GradesTable where PID like '" & Text2.Text & "', GROUP BY TEST,exam" , DB, adOpenStatic,
 adLockOptimistic

But for this one it isn't working.

RS.Open "SELECT TEST, COUNT(exam) FROM GradesTable where PID like '" & Text2.Text & "', GROUP BY TEST" , DB, adOpenStatic,
 adLockOptimistic

I mean I can't retreive data from exam field on listview.

With .ListItems.Add(, , RS("TEST"))
                .SubItems(1) = RS("EXAM")

I can only see data from TEST field.

For (LIKE) and '(=)
When I use = I get error: (type de données incompatible)
thanks a lot

[samer22]

I've seen you say this before. You do know that the database section is part of this forum don't you. Anyone from this section who knows about databases will also visit the database section along with a bunch of other database experts who aren't necessarily VB6 experts and they will be every bit as sympathetic to the fact that you're a beginner. The database section is a far more suitable home for your thread and you'd be likely to get better help there.

If you really want this left in the VB6 section I'm happy to respect that but I do think you're doing yourself a disservice. The answers you're getting in this section are very good but the ones you'd get in the DB section are likely to be even better.

By the way, you really should specify what database you're using. An answer that is right for Access may be wrong for SQLServer or Oracle and vice versa. Without knowing which database your using we may accidentally give you bad advice.

I'm sorry if I have broken the rules of the forum
I like this corner because the members here know me and understand my problems very well.
But please do what you think is appropriate.

[FunkyDexter]

I'm sorry if I have broken the rules of the forum

You haven't broken the rules and I don't intend to come across as telling you off in any way. I'm just concerned that you're probably not getting the best out of the forum.

If you post in the wrong section you're likely to get fewer and lower quality answers than if you post in the right one. (No disrespect meant to any of the contributors to this thread - the answers given here have been very good).

I would hope that users in the database forum respect the fact that you're a beginner just as much as the users in here. If people don't respect that you're a beginner and you feel you're not being given the consideration you deserve then report it to the mods. Our aim is to create a friendly forum where people of all ability levels can be supported. We all start out as beginners so if we cut beginners out we end up with no members at all.

As long as they are polite to others and follow the forum rules, everyone should be made to feel welcome in any part of the forum. I do not like the idea that anyone might be being made to feel unwelcome in any part of this forum and would want to deal with it appropriately.

Anyway, on to your questions:-

For this query it quite OK

But for this one it isn't working

I'm surprised that either works. Both have a comma before the group by which would be a syntax error in most forms of sql. If you remove the comma I would expect both to work fine.

I mean I can't retreive data from exam field on listview.

I think your field name probably isn't "Exam". In the first query you're selecting Exam so the field gets returned as Exam. In the second you're selecting Count(Exam) so some arbitrary field name is being assigned. Try aliasing the second query like this:-

SELECT TEST, COUNT(exam) as Exam...

I think that will probably fix it.

When I use = I get error: (type de données incompatible)

That's a bit of a mystery. If you don't use a wildcard then a like and a = do exactly the same thing. What is the datatype of the PID field in the database and what is the value contained in Text2.Text? It sounds like they're incompatible but why they would then work with a Like statement is a mystery to me.


And finally, what database are you using? It's really important that you let us know because the advice I'm giving is based on the assumption that you're using SQLServer while others are assuming you're using access. So at least one person in this thread is probably giving you bad (or at least, not the best) advice.