Help in query

[mustish1 via AccessMonster.com]

Hi:
Can any one please help me in creating query. Criteria is that:

If LstVldAccount="OtherAccount 1" then match tech_id.corp with Val(Left(
[LastValidTech].[OtherAcct1],5
If LstVldAccount="OtherAccount 2" then match tech_id.corp with Val(Left(
[LastValidTech].[OtherAcct2],5
If LstVldAccount="OtherAccount 3 then match tech_id.corp with Val(Left(
[LastValidTech].[OtherAcct3],5

If LstVldAccount is empty and tbl_ValidDisputes.LstVldTech also dont match
with tech_id.tech then it still show values from tbl_ValidDisputes

I try to write it first but it gives syntax error:

SELECT tbl_ValidDisputes.TicketNum, tbl_ValidDisputes.OtherAcct1,
tbl_ValidDisputes.OtherAcct2,tbl_ValidDisputes.LstVldAccount,
tbl_ValidDisputes.OtherAcct3, tech_id.TECH, tech_id.TECHCONT, tech_id.CORP
FROM tbl_ValidDisputes LEFT JOIN tech_id ON IIF(LstVldAccount="OtherAccount
1", Val(Left([LastValidTech].[OtherAcct1],5)), IIF(LstVldAccount =
"OtherAccount 2", Val(Left([LastValidTech].[OtherAcct2],5)), IIF
(LstVldAccount = "OtherAccount 3", Val(Left([LastValidTech].[OtherAcct3],5)),
0)))=tech_id.CORP and tbl_ValidDisputes.LstVldTech=tech_id.tech

Thanks in advance.

 

[mustish1 via AccessMonster.com]

Sorry this is an actual query where the syntax error is:

SELECT tbl_ValidDisputes.TicketNum, tbl_ValidDisputes.OtherAcct1,
tbl_ValidDisputes.OtherAcct2, tbl_ValidDisputes.OtherAcct3,
tbl_ValidDisputes.LstVldAccount, tech_id.TECH, tech_id.TECHCONT, tech_id.CORP
FROM tbl_ValidDisputes LEFT JOIN tech_id ON IIF (tbl_ValidDisputes.
LstVldAccount="OtherAccount 1", Val(Left([tbl_ValidDisputes].[OtherAcct1],5),
IIF(tbl_ValidDisputes.LstVldAccount = "OtherAccount 2", Val(Left(
[tbl_ValidDisputes].[OtherAcct2],5), IIF(tbl_ValidDisputes.LstVldAccount =
"OtherAccount 3", Val(Left([tbl_ValidDisputes].[OtherAcct3],5), 0))) =tech_id.
CORP and tbl_ValidDisputes.LstVldTech=tech_id.tech

 

[John Vinson]

Hi:
Can any one please help me in creating query. Criteria is that:

If LstVldAccount="OtherAccount 1" then match tech_id.corp with Val(Left(
[LastValidTech].[OtherAcct1],5
If LstVldAccount="OtherAccount 2" then match tech_id.corp with Val(Left(
[LastValidTech].[OtherAcct2],5
If LstVldAccount="OtherAccount 3 then match tech_id.corp with Val(Left(
[LastValidTech].[OtherAcct3],5

You're having difficulty becaus your table structure is incorrectly
normalized. If you have fields named OtherAcct1, OtherAcct2, and
OtherAcct3, then you are storing a one (tech? don't know what entity
this table represents) to many Other Accounts fields in each record.
*This is incorrect design*.

If you have a many to many relationship, in which each tech is
connected to multiple accounts, and (possibly) each account may be
related to multiple techs, you should have another table with the
tech_ID and the Account.

Aoso, the fact that you need to split off the leftmost five bytes of
the OtherAcctN fields indicates that your field is not atomic. Storing
multiple pieces of information in one field is a bad idea, for exactly
the reason you're experiencing.

If LstVldAccount is empty and tbl_ValidDisputes.LstVldTech also dont match
with tech_id.tech then it still show values from tbl_ValidDisputes

Your query doesn't mention tbl_ValidDisputes and we have no idea how
that table relates to anything else in your database. You can see your
database - we cannot!

I try to write it first but it gives syntax error:

SELECT tbl_ValidDisputes.TicketNum, tbl_ValidDisputes.OtherAcct1,
tbl_ValidDisputes.OtherAcct2,tbl_ValidDisputes.LstVldAccount,
tbl_ValidDisputes.OtherAcct3, tech_id.TECH, tech_id.TECHCONT, tech_id.CORP
FROM tbl_ValidDisputes LEFT JOIN tech_id ON IIF(LstVldAccount="OtherAccount
1", Val(Left([LastValidTech].[OtherAcct1],5)), IIF(LstVldAccount =
"OtherAccount 2", Val(Left([LastValidTech].[OtherAcct2],5)), IIF
(LstVldAccount = "OtherAccount 3", Val(Left([LastValidTech].[OtherAcct3],5)),
0)))=tech_id.CORP and tbl_ValidDisputes.LstVldTech=tech_id.tech

You cannot use an IIF statement in a JOIN clause. With your current
structure, I think you'll need to either build the entire SQL string
in VBA code, or use a UNION query to normalize the data. Your best bet
is to stop, restructure your tables so that each field contains only
one item of information and none of your tables have repeating fields,
and start over!

John W. Vinson[MVP]