Conditional Statement in a Query

[vanguardaccess]

I have a query with the following information: (example)

ID-----Type----Course-----Dept-----RequiredCredits
01-----A-------M12-------Math-----8
01-----A-------E12-------English-----6
01-----A-------S12-------Science-----6
02-----B-------M12-------Math-----8

The RequiredCredits field is coming in from a related table that matches
with Dept (e.g. If the Dept is Math, it pulls an 8). The table looks like the
following (with a third column added, as it is part of my question):

Dept----ReqCredA-----ReqCredB
Math----8-------------7
English--6-------------5
etc.

What I would like to do is have the query pull from either column (ReqCredA
or ReqCredB) based on what I designate in the Type field (A or B, from the
main query). This would change the 4th record in the query above from 8 to 7
RequiredCredits.

Is this possible? If so, how?

Your help is, as always, appreciated.

 

[John W. Vinson]

I have a query with the following information: (example)

ID-----Type----Course-----Dept-----RequiredCredits
01-----A-------M12-------Math-----8
01-----A-------E12-------English-----6
01-----A-------S12-------Science-----6
02-----B-------M12-------Math-----8

The RequiredCredits field is coming in from a related table that matches
with Dept (e.g. If the Dept is Math, it pulls an 8). The table looks like the
following (with a third column added, as it is part of my question):

Dept----ReqCredA-----ReqCredB
Math----8-------------7
English--6-------------5
etc.

What I would like to do is have the query pull from either column (ReqCredA
or ReqCredB) based on what I designate in the Type field (A or B, from the
main query). This would change the 4th record in the query above from 8 to 7
RequiredCredits.

Is this possible? If so, how?

Your help is, as always, appreciated.

I would really suggest restructuring your second table! As it is, you'll need
a highly inefficient IIF or better Switch to select the credit:

ReqCred: Switch([Type] = "A", [table2].[ReqCredA], [Type] = "B",
[Table2].[ReqCredB], True, Null)

And you'll need to restructure your tables, restructure the query, redesign
forms etc. if you ever have a TypeC.

A better design for your second table would have three fields - Dept, Type and
ReqCred. You can join on BOTH the Dept and Type fields to pick up the
appropriate value.

 

[vanguardaccess]

Thanks, I took your advice and joined the third column. It worked well and
solved the problem.
--
--vanguardaccessman

I have a query with the following information: (example)

ID-----Type----Course-----Dept-----RequiredCredits
01-----A-------M12-------Math-----8
01-----A-------E12-------English-----6
01-----A-------S12-------Science-----6
02-----B-------M12-------Math-----8

The RequiredCredits field is coming in from a related table that matches
with Dept (e.g. If the Dept is Math, it pulls an 8). The table looks like the
following (with a third column added, as it is part of my question):

Dept----ReqCredA-----ReqCredB
Math----8-------------7
English--6-------------5
etc.

What I would like to do is have the query pull from either column (ReqCredA
or ReqCredB) based on what I designate in the Type field (A or B, from the
main query). This would change the 4th record in the query above from 8 to 7
RequiredCredits.

Is this possible? If so, how?

Your help is, as always, appreciated.

I would really suggest restructuring your second table! As it is, you'll need
a highly inefficient IIF or better Switch to select the credit:

ReqCred: Switch([Type] = "A", [table2].[ReqCredA], [Type] = "B",
[Table2].[ReqCredB], True, Null)

And you'll need to restructure your tables, restructure the query, redesign
forms etc. if you ever have a TypeC.

A better design for your second table would have three fields - Dept, Type and
ReqCred. You can join on BOTH the Dept and Type fields to pick up the
appropriate value.