Multiple Conditions

[mr.donaldson]

I have 1 table that I duplicated to make 3 tables total. I did this b/c I am trying to create separate columns from the same field and table. The field is Workorder. Each workorder list the workorder number followed by a dash and then code. I am wanting to put all of the workorders with the same code in it's own column. I have 5 codes that i am searching for. The first column list the workorder and a code (123456789-AD). The second column (123456789-BC). I'm good to this point but my problem occurs next. The third column i am trying to put 3 types of workorder and it's code in the same column. As follows, (123456789-CD, 123456789-TC, and 123456789-PTC. However, when I do this it takes the results from the 3rd column and applies it to the 3rd column but also the 1st and 2nd column. I tried a UNION query and unless I am doing it incorrectly it does not work. Any suggestions would be greatly appreciated.

 

[CJ_London]

I suggest you investigate using a crosstab query which may solve your problem - you haven't really provided enough information to provide a full solution.

Presuming what defines what is to appear in each column is the alpha code at the end, when defining the crosstab column, you need to base the columns definition on this - the formula would be something like:

ColHeader:iif (right(workorder,2) in ("AD","BC"), right(workorder,2),"XX")

This will give you column headings of AD, BC and XX.

But without more information I can't advise what your row and values need to be

 

[mr.donaldson]

I have a table called workorder_data with a field called workorder. A user enters their workorder number followed by a respective code (123456789-AD, 123456789-BC, 123456789-CD, 123456789-TC, and 123456789-PTC). I want to pull all the AD and BC workorders into their own separate columns. So I have 2 columns now. On the third column, I want to pull all CD, TC, and PTC. I can get the first 2 columns fine but when I try to pull the 3rd it takes the data and overrides what is in the 1st and 2nd column. So now the columns have the same data. I am using the AND OR operator because all the workorders are tying back to the same field. So I have where....((workorder Like) "%AD"), OR ((workorder_1 Like) "%BC"), OR ((workorder_2 Like) "%CD"), OR ((workorder Like) "%TC"), OR ((workorder Like) "%PTC") AND Area = "23";

Hope that helps. Thanks for 1st suggestion maybe this will help further.

 

[CJ_London]

If you look at my code, you'll see there are effectively 3 'states'

AD
BC
Everything else

So develop you code on this principle

Also notice you are using % rather than * - so just checking you are using ANSI92 SQL, if not you'll need to use * - and if you are using ANSI92 then you should be using Alike rather than Like

2am here, so signing off now