Union in Nested If Statements

[Pyrrhus272BC]

All,

My question related around nested IF statements and a Union. I am trying to figure out how the last component of the formula should force the query to do a union on data (like a lookup) if it runs through all the previous tests.

In design mode it looks like this:

Prod Detail (Pre): IIf([*2011 Corp (P1 & PGS)]![L9 Cost Cent Desc]="TOTAL CORE FI DCM US CONDUITS" And [*2011 Corp (P1 & PGS)]![L1 Orig Prod Desc]="Origination Income","Conduits",IIf([*2011 Corp (P1 & PGS)]!Product1="Portfolio" And [*2011 Corp (P1 & PGS)]![Source System Code]="TLP","TLP",IIf([*2011 Corp (P1 & PGS)]![L8 Cost Cent Desc]="Total Core Mortgage Trading" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="DCM Origination","DCM",IIf([*2011 Corp (P1 & PGS)]!Product1="Markets" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="Eq Cash","Markets Other",IIf([*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="DCM Origination Cross Sell","Cross Sell",IIf([*2011 Corp (P1 & PGS)]![L2 Orig Prod Desc]="Deduplication Cross Sell","Solution Sales",IIf([*2011 Corp (P1 & PGS)]!Product1="Markets" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="Syndicate","Markets Other", Insert Union Here )))))))

OR in SQL Mode:

SELECT [*2011 Corp (P1 & PGS)].*, IIf([*2011 Corp (P1 & PGS)]![L9 Cost Cent Desc]="TOTAL CORE FI DCM US CONDUITS" And [*2011 Corp (P1 & PGS)]![L1 Orig Prod Desc]="Origination Income","Conduits",IIf([*2011 Corp (P1 & PGS)]!Product1="Portfolio" And [*2011 Corp (P1 & PGS)]![Source System Code]="TLP","TLP",IIf([*2011 Corp (P1 & PGS)]![L8 Cost Cent Desc]="Total Core Mortgage Trading" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="DCM Origination","DCM",IIf([*2011 Corp (P1 & PGS)]!Product1="Markets" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="Eq Cash","Markets Other",IIf([*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="DCM Origination Cross Sell","Cross Sell",IIf([*2011 Corp (P1 & PGS)]![L2 Orig Prod Desc]="Deduplication Cross Sell","Solution Sales",IIf([*2011 Corp (P1 & PGS)]!Product1="Markets" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="Syndicate","Markets Other"))))))) AS [Prod Detail (Pre)]
FROM [*2011 Corp (P1 & PGS)];

My skill in writing SQL is moderate, so I needed the advice on how to best create this. As I said, ultimately, this Union should happen after running through all the other tests and is the last component (similar to a vlookup).


Appreciate all input.

 

[revans]

I suspect you have your nomenclature mixed up, or perhaps I am. A UNION query combines two (or more) queries and runs them as if they were one. They look sort of like this

SELECT Field1, Field2, Field3
FROM Table1
UNION
SELECT Field1, Field2, Field3
FROM Table2

Since you say you are looking for a function like a vlookup in Excel I think you really want a DLookup. Without specifics of what you're looking up I can't help you much with that except to say the help files are good and a quick search of these forums for "Dlookup" should help out.

hth,

Rich

 

[Pyrrhus272BC]

The thing is, I am looking to put the union function AFTER all the other arguments are put through. I know Dlookups, but as I understand they are more to specific lookups. The reason I had mentioned Vlookups, was because of the fact that I had been looking for any one of multiple results in a column that need to pick up a corresponding result in another table.
Again, only after it has run through all the other arguments.

I suspect you have your nomenclature mixed up, or perhaps I am. A UNION query combines two (or more) queries and runs them as if they were one. They look sort of like this

SELECT Field1, Field2, Field3
FROM Table1
UNION
SELECT Field1, Field2, Field3
FROM Table2

Since you say you are looking for a function like a vlookup in Excel I think you really want a DLookup. Without specifics of what you're looking up I can't help you much with that except to say the help files are good and a quick search of these forums for "Dlookup" should help out.

hth,

Rich

 

[james_lankford]

a union combines 2 or more queries
that is all it does
the queries have to have the same number of columns and the corresponding columns in all the queries have to be exactly the same type

a union makes no sense with one query

I had been looking for any one of multiple results in a column that need to pick up a corresponding result in another table

so you want to join two tables ?

that is not what a union is

 

[Pyrrhus272BC]

Ok, I was thinking of a left or right join, sorry. As I had mentioned what I basically want it to do in the end of the nested if's is to look up an item in one table that matches the same value in another table, and present another column. For example

Table A -------------------------------Table B
Column A ------------------------------Column A ------Column B
Shoes ----------------------------------------Vegetables -------Apples
Trees -----------------------------------------Cars--------------Toyota
Feet-------------------------------------------Ice Cream--------Haagen Daz
Ice Cream------------------------------------ Animals --------- Walrus

I want it in this case to look up any value in Table A and find the corresponding item in Table B. Then, return what would be in this case a match, and item in Column B of Table B

So Ice Cream, is the only item that matched off, and the corresponding item would be Haagen Daz. I do not want to limit it to a specific search for Ice Cream, but ANY item that matches off.

a union combines 2 or more queries
that is all it does
the queries have to have the same number of columns and the corresponding columns in all the queries have to be exactly the same type

a union makes no sense with one query


so you want to join two tables ?

that is not what a union is

 

[mahju]

You can use inner join to do this

If table 1 is item and table 2 is item-2 (has relevant information to search) then use

SELECT [Item-2].Desc
     FROM Item INNER JOIN [Item-2] ON Item.Item = [Item-2].Item;

or make a inner join of two tables in design view of query

Regards