How to code this sql in VB?

  • Thread starter Thread starter MikeA
  • Start date Start date
M

MikeA

Hello all,
I have a table with data like this:

ClientID(PK) Agent Clinic Status
123456789 01 01 1
123456789 01 02 2
223456789 01 01 1
223456789 02 01 1
000000001 02 02 1
000000001 02 01 1
000000001 02 02 1
How can I pick out only 1 record which clientID have
status=1 like ex. above will pick out only 2 records are:
ClientID= 223456789 and 000000001
Thanks you for any advice,
MikeA
 
Hello all,
I have a table with data like this:

ClientID(PK) Agent Clinic Status
123456789 01 01 1
123456789 01 02 2
223456789 01 01 1
223456789 02 01 1
000000001 02 02 1
000000001 02 01 1
000000001 02 02 1
How can I pick out only 1 record which clientID have
status=1 like ex. above will pick out only 2 records are:
ClientID= 223456789 and 000000001
Thanks you for any advice,
MikeA
Click to expand...

Do you want to do it in VB, or in a Query?

The Query to select an ARBITRARY one of the Status 1 records for each
client would be

SELECT ClientID, First([Agent]) As FirstOfAgent, First([Clinic]) As
FirstOfClinic FROM yourtable
WHERE Status = 1
GROUP BY ClientID;

Note that "first" is a bit misleading: it's the first record in disk
storage order, and that order is arbitrary and uncontrollable.
Essentially this retrieves a random record.
 
Thanks John
I will teting your recommendation , also I just have
differnt way to do it like:
I select all from tableA where status=1 into tableTempthen
I select from tableA where status=2 and clientID not in
tableA not in tableTemp. It si working now.
Thank for taking your time.
Best Regards & Happy Holiday.
MikeA
-----Original Message-----
Hello all,
I have a table with data like this:

ClientID(PK) Agent Clinic Status
123456789 01 01 1
123456789 01 02 2
223456789 01 01 1
223456789 02 01 1
000000001 02 02 1
000000001 02 01 1
000000001 02 02 1
How can I pick out only 1 record which clientID have
status=1 like ex. above will pick out only 2 records are:
ClientID= 223456789 and 000000001
Thanks you for any advice,
MikeA
Click to expand...

Do you want to do it in VB, or in a Query?

The Query to select an ARBITRARY one of the Status 1 records for each
client would be

SELECT ClientID, First([Agent]) As FirstOfAgent, First ([Clinic]) As
FirstOfClinic FROM yourtable
WHERE Status = 1
GROUP BY ClientID;

Note that "first" is a bit misleading: it's the first record in disk
storage order, and that order is arbitrary and uncontrollable.
Essentially this retrieves a random record.



.
Click to expand...
 
Thanks John
I will teting your recommendation , also I just have
differnt way to do it like:
I select all from tableA where status=1 into tableTempthen
I select from tableA where status=2 and clientID not in
tableA not in tableTemp. It si working now.
Click to expand...

Well, I use temp tables and MakeTable queries only when there is NO
other way - they are very inefficient, and (as in this case, unless
I'm missing something) very rarely needed.
 
Back
Top