Query unable to use not in -

[sunilvedula]

hi all,

i have two tables (table1 and table 2). Both contains the same fields. The field refno is the unique one. i want to find out the similar records with the same refno and having the same "action" (another field) type. Also i want to segregrate records which have the same refno and different "action" types.

i have tried using "in" which gives me all records with the same ref no but is not allowing me to separate the records on the action type which i tried using "Not in"

Also i have tried writing one query which pulls up all the records with the same refno and then write another queyr based on this query to differentiate based on the action type but it does not give me the desired result. it is not able to differntiate the action types when using "Not in".

Both Refno and action are text fields. Please help with suggestions.

 

[khawar]

It works perfect here is a little sample

 

[sunilvedula]

Nice example and one of the best i have seen in recent times. thanq . That explains me the difference when to use and not use. But my problem remains the same. I have action types which are the same in the both tables. But for some refno the action type changes over a period of time. Now i want to identify the records for which the action type has changed and not changed. Can you help me with it

 

[khawar]

can you upload your database and also tell me what you want to do i will solve your problem

 

[sunilvedula]

Please find sample with two tables. Now both the tables contain the same records but with different action types. i want records where teh action type has changed and not changed. the problem is i cannot specify which action type i need since i will be requiring anything that is changed.

 

[khawar]

Please give a little bit more detail and explain the table and tell what you want to do on basis of what

 

[sunilvedula]

Hi,
for example there are two records with the same ref no i.e "1234". The actions type in TblWorkAssoc is "ADOC". For the same record the action type in TblProcOOT is "1cb" . Now i want that record where the action type has changed to be displayed. Also i want records where there is no change in the action type also to be displayed. If i am not clear please let me know

 

[Brianwarnock]

I don't understand all of this In Not In business these are just simple Where clauses

Where t1.ref=t2.ref and t1.action<>t2.action
for the first and i geuss you can work out the second.

Brian

 

[sunilvedula]

Hi But it doesnt work. i did try even before posting this one but it never worked.

"Not in" works to pull records where that data is unique. For eg if a record has type as "ppppp" which is not available in any of the tables then it would give else it would not show.

i want to pull records for 1 and also for 2 with a query. You can also refer to the zip file i sent. Maybe i am missing something here and it might be very easy but i am unable to do it with "Not in".

 

[Brianwarnock]

Hi But it doesnt work. i did try even before posting this one but it never worked.
QUOTE]

Now if that was aimed at my post I wonder what you did

SELECT TblProcOOT.RefNo, TblWorkAssoc.REF, TblProcOOT.Actn, TblWorkAssoc.ACTION
FROM TblProcOOT, TblWorkAssoc
WHERE (((TblProcOOT.RefNo)=TblWorkAssoc.REF) AND ((TblProcOOT.Actn)<>TblWorkAssoc.ACTION));

gives 9 records including the one quoted

If that is not what you want you need to explain why.

Brian

 

[sunilvedula]

Hi Brian,

sorry for troubling you again. I did use the same code which you mentioned. It gives me nothing. Then what i did i changed the action type to one of the record to ABC which is non existent and entirely a new action type. Then it showed me that record. My guess is if the action type is existing for the any of the records in the coloumn action type then it is not giving it. The code written is searching for something that is not existent in the Action type and is unique. This is my guess. Following is the code i have used which also you provided me.

SELECT TblWorkAssoc.REF, TblWorkAssoc.ACTION
FROM TblProcOOT, TblWorkAssoc
WHERE (((TblProcOOT.RefNo)=[TblWorkAssoc].[REF]) AND ((TblProcOOT.Actn)<>[TblWorkAssoc].[ACTION]))
GROUP BY TblWorkAssoc.REF, TblWorkAssoc.ACTION;

 

[Brianwarnock]

It works ok for me, but I don't know why you are grouping.
I attach your test db with 3 queries.
My original
the one you quoted above
ditto without the grouping.

Brian

 

[sunilvedula]

thanks a lot. will check and let you know. It should help

 

[sunilvedula]

Thanks a lot it works. But cant understand why it did not work with mine. Anyhow will check on that to figure out the error. But this works. Thanks a lot and sorry for any inconvenience

 

[Brianwarnock]

It was no problem, sometimes wires get crossed , some times the gremlins of IT strike.
Glad its now working.

Brian