Query Help

[tmcrouse]

I have three separate tables. 1 that is Jun10 and houses the subID, policy, policyname and their indicator choice. Then I have table 2 that is Jul10RAWCES and it houses subID, policy, policyname and their indicator choice. Then my final is decreaseJul10 and it has policy and policyname.



I have the tables joined and from Jun10 want the policy, policyname and indicator choice of only yy, xy or ny and then pull only the indicator choice from Jul10RAWCES that is not yy, xy or ny. The primarykey between the date tables is the subID and I have that set to is not null and then the key between jun10 and decreaseJul10 is the policy because I only want to pull certain policies.

No matter what I try I can't get the right data back for the Jul10RAWCES table. My goal is to show only those records that were a yy, xy or ny in jun10 and what they are in the jul10rawces that does not = yy xy or ny. So if the subID was a yy in jun10 in order for my other data to be correct they need to be a nn in jul or xx or something that doesn't end in a y.

here is an example of the code i have tried:

select jun10.pol, jun10.polname, jun10.ind, jul10rawces.ind from (jun10 inner join jul10rawces on jun10.empid=julrawces.subid) inner join decreasejul10 on jun10.pol=decreasejul10.policy where (((jun10,ind="yy") or (((jun10.ind="xy")) or (((jun10.ind="ny")) and ((jul10rawces.subid)is not null) and ((jul10rawces.ind) not in ("yy","xy","ny")));


Now I am typing this manually looking at my work computer and typing in this forum so I might have missed a (). The code doesn't error. It runs the problem is it doesn't return what I ask for.

[weekend00]

What did you expect and what did you get?

the query looks fine, just be sure you have enough policy in decreasejul10 to match the policy in jun10 to avoid from missing some records.

[tmcrouse]

What I got back was valid except it returns

pol policyname indjun10 indjul10rawces
xxxx pitney bowes yy yy

I want it to look at the jun10 ind choices of yy, xy and ny which it is but for the indjul10rawces i want it to return everything but yy, xy and ny. This is because I am looking for anything that was a yy, xy or ny in jun10 table and what it changed to in the jul10 table that isn't equal to the yy xy or ny. i only want to see that a policy went from say yy in jun to nn in jul

[tmcrouse]

maybe under my jun10.ind column needs to be the yy and xy and ny and then the jul10rawces.ind needs to be [jun10].[ind]<>[jul10rawces].[ind]

the rest of the query remains the same. i will try changing it to that and see what comes back. been playing with this for a day now. tried all sorts of different where like not in() and <> and not exist etc etc etc. but because the jun10 and jul10 files are 10.7 rows due to distinct subid's it takes forever to run

[weekend00]

check you ( and ).
I found they are not on right place. you can try below updated one:

select jun10.pol, jun10.polname, jun10.ind, jul10rawces.ind from (jun10 inner join jul10rawces on jun10.empid=julrawces.subid) inner join decreasejul10 on jun10.pol=decreasejul10.policy where ((jun10,ind="yy") or (jun10.ind="xy") or (jun10.ind="ny")) and (jul10rawces.subid is not null) and (jul10rawces.ind not in ("yy","xy","ny"));

[tmcrouse]

The code is manually typed in not copied because my work computer has the code. I just copied it over manually and might have placed () in incorrect places or not enough. but the code does run. no error on code just not giving me what i want. tried the [jun10].[ind]<>[jul10].[ind] and that didn't work.

[weekend00]

but the code does run. no error on code

this does mean you logic is right.

criteria: where (a or b) and c
is totally different from
where a or b and c
althrough both are right in syntax.

[tmcrouse]

Maybe I should just try an unmatched query. if jun10 is yy xy or ny i want the unmatched in the jul10rawces file. i will see if that works.

[tmcrouse]

nope. this is crazy. maybe i should just not do any choices in the jul10rawces.ind section and let it return everything under the sun and make a table of it and manually delete anything that says YY in the jun10.ind field that is a YY in the jul10rawces.ind field meaning it is returning the exact same and that is not what i want. i want it to give me all that are yy, xy and ny in jun10 but i dont want yy xy or ny to be returned from jul10 file. crazy that i can't figure this out

[tmcrouse]

cuz i am spending far too much time on this and i'll bet creating a table and then sorting and deleting the rows i dont want would be faster. just seems dumb to do this type of process monthly. i know there is a query out there that would work for my needs and just need to find someone that knows what i am doing wrong. cuz when i leave this dept which i am hopefully doing soon my mgr is going to have to do my work and doesn't like manual stuff but of course she doesn't know the answer to my problem. she normally comes to me for answers. i never go to her. i come here.

[weekend00]

What I got back was valid except it returns

pol policyname indjun10 indjul10rawces
xxxx pitney bowes yy yy

I want it to look at the jun10 ind choices of yy, xy and ny which it is but for the indjul10rawces i want it to return everything but yy, xy and ny. This is because I am looking for anything that was a yy, xy or ny in jun10 table and what it changed to in the jul10 table that isn't equal to the yy xy or ny. i only want to see that a policy went from say yy in jun to nn in jul

You must not get any record with indjul10rawces="yy" if your where clause is right in logic. since you got this record, the logic must be wrong.

[tmcrouse]

that is what i think is logic is wrong but how to make it right. now i have just inner joined jun10.subid to jul10rawces.subid then inner join jun10.pol to decreasejul10.pol and said i want pol, polnam and ind that is = to yy, xy, ny in the jun10 table and then give me everything under the sun from jul10rawces.ind of course where the subid's match and then the policy matches the list in the decrease table and then I am doing a make table to another database cuz working with these tables is too time consuming since they each contain 10.7 million rows. all but the decreasejul10 file. that has only 9885 policy numbers. then once i get that table which it is almost done doing a make table to another database and it says it is pasting 410k rows. i will then open that database and manually get rid of all the items returned that are the same and keep those that are not and maybe sometime b4 i leave this dept i will find the right query to run so i can save it for her. otherwise she will just have to play the sort and delete game too.

[weekend00]

can you open your query in SQL view, copy and paste it here?

[tmcrouse]

well that didn't work either.

[tmcrouse]

I give up. I will just tell my boss she will have to figure it out or tell the team I can't figure out how to run the query. The end numbers have to equal another report I have and they don't so something isn't right and I can't figure out what and all the forums I have posted to have not been able to help me with the answer either.