Nested If in Query

  • Thread starter Thread starter EZ
  • Start date Start date
E

EZ

I'm querying two tables. Basically I need to lookup values in tabl2 and match
them in ranges(columns) in tbl1, if all conditions are met, I want to show a
"Y", otherwise a "N". I have this formula working in Excel, and I know Access
acts a little different than Excel, but I need help on how to modify this
formula to work in Access. Thanks.

ProdBDC:=IF(AND(ISNUMBER(MATCH([tbl2].[BDC_CD],[tbl1].[BDC_CD]0)),ISNUMBER(MATCH([tbl2].[Dest_Loc],[tbl1].[Loc],0)))),"Y","N")
 
Try this --
ProdBDC: IIF(([tbl2].[BDC_CD] = [tbl1].[BDC_CD]) AND ([tbl2].[Dest_Loc] =
[tbl1].[Loc]),"Y","N")
 
Thanks Karl. Your formula worked. But here's my other question:
I need to add 4 new columns with "Y"/"N" data. The first is based on 2
conditions, and if the result is "Y", then by default all other 3 will be "Y"
as well. If the answer is "N", then each field of the remaining 3 will
require 3 conditions in order to be "Y". I can adjust the formula to do that.
But currently my two tables join show only data that matches both tables. I
opened the join properties and the other 2 options will not do what I needed
to do. I need to show all data from both tables for the 4 fields out of 5
fields that i'm using to relate the two tables together. Because with my
current query result, the answers are always going to be "Y" since it's based
on equal joins data.

I hope this is clear. Could it be another approach to this problem?

Thanks.
--
when u change the way u look @ things, the things u look at change.


KARL DEWEY said:
Try this --
ProdBDC: IIF(([tbl2].[BDC_CD] = [tbl1].[BDC_CD]) AND ([tbl2].[Dest_Loc] =
[tbl1].[Loc]),"Y","N")


EZ said:
I'm querying two tables. Basically I need to lookup values in tabl2 and match
them in ranges(columns) in tbl1, if all conditions are met, I want to show a
"Y", otherwise a "N". I have this formula working in Excel, and I know Access
acts a little different than Excel, but I need help on how to modify this
formula to work in Access. Thanks.

ProdBDC:=IF(AND(ISNUMBER(MATCH([tbl2].[BDC_CD],[tbl1].[BDC_CD]0)),ISNUMBER(MATCH([tbl2].[Dest_Loc],[tbl1].[Loc],0)))),"Y","N")
Click to expand...
Click to expand...
 
It sounds as if you are building a spreadsheet instead of using Access as a
relational database.
What is your overall process you are trying to accomplish. Maybe someone
can suggest a better alternative than using the spreadsheet.

EZ said:
Thanks Karl. Your formula worked. But here's my other question:
I need to add 4 new columns with "Y"/"N" data. The first is based on 2
conditions, and if the result is "Y", then by default all other 3 will be "Y"
as well. If the answer is "N", then each field of the remaining 3 will
require 3 conditions in order to be "Y". I can adjust the formula to do that.
But currently my two tables join show only data that matches both tables. I
opened the join properties and the other 2 options will not do what I needed
to do. I need to show all data from both tables for the 4 fields out of 5
fields that i'm using to relate the two tables together. Because with my
current query result, the answers are always going to be "Y" since it's based
on equal joins data.

I hope this is clear. Could it be another approach to this problem?

Thanks.
--
when u change the way u look @ things, the things u look at change.


KARL DEWEY said:
Try this --
ProdBDC: IIF(([tbl2].[BDC_CD] = [tbl1].[BDC_CD]) AND ([tbl2].[Dest_Loc] =
[tbl1].[Loc]),"Y","N")


EZ said:
I'm querying two tables. Basically I need to lookup values in tabl2 and match
them in ranges(columns) in tbl1, if all conditions are met, I want to show a
"Y", otherwise a "N". I have this formula working in Excel, and I know Access
acts a little different than Excel, but I need help on how to modify this
formula to work in Access. Thanks.

ProdBDC:=IF(AND(ISNUMBER(MATCH([tbl2].[BDC_CD],[tbl1].[BDC_CD]0)),ISNUMBER(MATCH([tbl2].[Dest_Loc],[tbl1].[Loc],0)))),"Y","N")
Click to expand...
Click to expand...
Click to expand...
 
In one table I have product shipping information (who receives what/qty). On
the other table I have information about who produces what product.
If location X receives a product from Loc Z (product
class/flavor/size/frequency, etc) I need to use location X code and the
product attributes to lookup the 2nd table and see if actually location X
produces the whole product pkg (combination of class/flavor/size), if yes,
then by default loc X also produces that flavor/size under that class. If
"N", then I need to see if they actually produces the flavor, and if they
produces the size within that prod class... you get the idea. So I will have
a query with who receives what, qty, frequency, and do they produce the prod
4 categories: "Y" or "N". Then I can use that for productivity analyses... if
X import product that it's capable of producing... why can't they produce it?
obviously I have to look at X's production capacity and see if they do have
the capacity and resources to produce that particular product. I hope you
understood what i'm after.

Anyone with some direction on this would be much appreciated. I think I have
completed about 70% of the project so far.

Thanks.


--
when u change the way u look @ things, the things u look at change.


KARL DEWEY said:
It sounds as if you are building a spreadsheet instead of using Access as a
relational database.
What is your overall process you are trying to accomplish. Maybe someone
can suggest a better alternative than using the spreadsheet.

EZ said:
Thanks Karl. Your formula worked. But here's my other question:
I need to add 4 new columns with "Y"/"N" data. The first is based on 2
conditions, and if the result is "Y", then by default all other 3 will be "Y"
as well. If the answer is "N", then each field of the remaining 3 will
require 3 conditions in order to be "Y". I can adjust the formula to do that.
But currently my two tables join show only data that matches both tables. I
opened the join properties and the other 2 options will not do what I needed
to do. I need to show all data from both tables for the 4 fields out of 5
fields that i'm using to relate the two tables together. Because with my
current query result, the answers are always going to be "Y" since it's based
on equal joins data.

I hope this is clear. Could it be another approach to this problem?

Thanks.
--
when u change the way u look @ things, the things u look at change.


KARL DEWEY said:
Try this --
ProdBDC: IIF(([tbl2].[BDC_CD] = [tbl1].[BDC_CD]) AND ([tbl2].[Dest_Loc] =
[tbl1].[Loc]),"Y","N")


:

I'm querying two tables. Basically I need to lookup values in tabl2 and match
them in ranges(columns) in tbl1, if all conditions are met, I want to show a
"Y", otherwise a "N". I have this formula working in Excel, and I know Access
acts a little different than Excel, but I need help on how to modify this
formula to work in Access. Thanks.

ProdBDC:=IF(AND(ISNUMBER(MATCH([tbl2].[BDC_CD],[tbl1].[BDC_CD]0)),ISNUMBER(MATCH([tbl2].[Dest_Loc],[tbl1].[Loc],0)))),"Y","N")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top