URGENT!! Using IIF in query

  • Thread starter Thread starter karen
  • Start date Start date
K

karen

I need help with using IIF in my query. I need to make one
calculation based on one criteria and another calculation
based on another. For example:

ProjectID
ProjectType

ProjectType can be either 1 or 2. If it's 1, I need to
divide the Amount by half and if Amount is 2 then the
number is not divided. A copy of the SQL follows in case
this is as clear as mud.

SELECT [tbl Allocation].AllocationDesignationID, [tbl
Allocation].AllocationID, [tbl Allocation].ProjectID, [tbl
Projects].ProjectType, [tbl Allocation].EmployeeID, [qry
NetRevenue].[Net Revenue], [tbl Allocation].Percentage,
[Net Revenue]*[Percentage] AS Amount
FROM [tbl Projects] INNER JOIN (([tbl Allocation
Designation] INNER JOIN [tbl Allocation] ON [tbl
Allocation Designation].AllocationDesignationID = [tbl
Allocation].AllocationDesignationID) INNER JOIN [qry
NetRevenue] ON [tbl Allocation].ProjectID = [qry
NetRevenue].ProjectID) ON ([qry NetRevenue].ProjectID =
[tbl Projects].ProjectID) AND ([tbl Projects].ProjectID =
[tbl Allocation].ProjectID);

Thanks!
karen
 
karen said:
I need help with using IIF in my query. I need to make one
calculation based on one criteria and another calculation
based on another. For example:

ProjectID
ProjectType

ProjectType can be either 1 or 2. If it's 1, I need to
divide the Amount by half and if Amount is 2 then the
number is not divided. A copy of the SQL follows in case
this is as clear as mud.

SELECT [tbl Allocation].AllocationDesignationID, [tbl
Allocation].AllocationID, [tbl Allocation].ProjectID, [tbl
Projects].ProjectType, [tbl Allocation].EmployeeID, [qry
NetRevenue].[Net Revenue], [tbl Allocation].Percentage,
[Net Revenue]*[Percentage] AS Amount
FROM [tbl Projects] INNER JOIN (([tbl Allocation
Designation] INNER JOIN [tbl Allocation] ON [tbl
Allocation Designation].AllocationDesignationID = [tbl
Allocation].AllocationDesignationID) INNER JOIN [qry
NetRevenue] ON [tbl Allocation].ProjectID = [qry
NetRevenue].ProjectID) ON ([qry NetRevenue].ProjectID =
[tbl Projects].ProjectID) AND ([tbl Projects].ProjectID =
[tbl Allocation].ProjectID);

Thanks!
karen
Click to expand...

SQL realigned for readability only:

SELECT [tbl Allocation].AllocationDesignationID
,[tbl Allocation].AllocationID
,[tbl Allocation].ProjectID
,[tbl Projects].ProjectType
,[tbl Allocation].EmployeeID
,[qry NetRevenue].[Net Revenue]
,[tbl Allocation].Percentage
,[Net Revenue] * [Percentage] AS Amount
FROM [tbl Projects]
INNER JOIN
(([tbl Allocation Designation]
INNER JOIN
[tbl Allocation]
ON [tbl Allocation Designation].AllocationDesignationID =
[tbl Allocation].AllocationDesignationID)
INNER JOIN
[qry NetRevenue]
ON [tbl Allocation].ProjectID = [qry NetRevenue].ProjectID)
ON ([qry NetRevenue].ProjectID = [tbl Projects].ProjectID)
AND ([tbl Projects].ProjectID = [tbl Allocation].ProjectID);


Try:

SELECT [tbl Allocation].AllocationDesignationID
,[tbl Allocation].AllocationID
,[tbl Allocation].ProjectID
,SWITCH(1, [tbl Projects].ProjectType / 2,
2, [tbl Projects].ProjectType)
,[tbl Allocation].EmployeeID
,[qry NetRevenue].[Net Revenue]
,[tbl Allocation].Percentage
,[Net Revenue] * [Percentage] AS Amount
FROM [tbl Projects]
INNER JOIN
(([tbl Allocation Designation]
INNER JOIN
[tbl Allocation]
ON [tbl Allocation Designation].AllocationDesignationID =
[tbl Allocation].AllocationDesignationID)
INNER JOIN
[qry NetRevenue]
ON [tbl Allocation].ProjectID = [qry NetRevenue].ProjectID)
ON ([qry NetRevenue].ProjectID = [tbl Projects].ProjectID)
AND ([tbl Projects].ProjectID = [tbl Allocation].ProjectID);


Sincerely,

Chris O.
 
Thanks Chris but now I only get either 0.5 or 1 in the
Total.

-----Original Message-----

I need help with using IIF in my query. I need to make one
calculation based on one criteria and another calculation
based on another. For example:

ProjectID
ProjectType

ProjectType can be either 1 or 2. If it's 1, I need to
divide the Amount by half and if Amount is 2 then the
number is not divided. A copy of the SQL follows in case
this is as clear as mud.

SELECT [tbl Allocation].AllocationDesignationID, [tbl
Allocation].AllocationID, [tbl Allocation].ProjectID, [tbl
Projects].ProjectType, [tbl Allocation].EmployeeID, [qry
NetRevenue].[Net Revenue], [tbl Allocation].Percentage,
[Net Revenue]*[Percentage] AS Amount
FROM [tbl Projects] INNER JOIN (([tbl Allocation
Designation] INNER JOIN [tbl Allocation] ON [tbl
Allocation Designation].AllocationDesignationID = [tbl
Allocation].AllocationDesignationID) INNER JOIN [qry
NetRevenue] ON [tbl Allocation].ProjectID = [qry
NetRevenue].ProjectID) ON ([qry NetRevenue].ProjectID =
[tbl Projects].ProjectID) AND ([tbl Projects].ProjectID =
[tbl Allocation].ProjectID);

Thanks!
karen
Click to expand...

SQL realigned for readability only:

SELECT [tbl Allocation].AllocationDesignationID
,[tbl Allocation].AllocationID
,[tbl Allocation].ProjectID
,[tbl Projects].ProjectType
,[tbl Allocation].EmployeeID
,[qry NetRevenue].[Net Revenue]
,[tbl Allocation].Percentage
,[Net Revenue] * [Percentage] AS Amount
FROM [tbl Projects]
INNER JOIN
(([tbl Allocation Designation]
INNER JOIN
[tbl Allocation]
ON [tbl Allocation
Click to expand...
Designation].AllocationDesignationID =
[tbl Allocation].AllocationDesignationID)
INNER JOIN
[qry NetRevenue]
ON [tbl Allocation].ProjectID = [qry NetRevenue].ProjectID)
ON ([qry NetRevenue].ProjectID = [tbl Projects].ProjectID)
AND ([tbl Projects].ProjectID = [tbl Allocation].ProjectID);


Try:

SELECT [tbl Allocation].AllocationDesignationID
,[tbl Allocation].AllocationID
,[tbl Allocation].ProjectID
,SWITCH(1, [tbl Projects].ProjectType / 2,
2, [tbl Projects].ProjectType)
,[tbl Allocation].EmployeeID
,[qry NetRevenue].[Net Revenue]
,[tbl Allocation].Percentage
,[Net Revenue] * [Percentage] AS Amount
FROM [tbl Projects]
INNER JOIN
(([tbl Allocation Designation]
INNER JOIN
[tbl Allocation]
ON [tbl Allocation
Click to expand...
Designation].AllocationDesignationID =
[tbl Allocation].AllocationDesignationID)
INNER JOIN
[qry NetRevenue]
ON [tbl Allocation].ProjectID = [qry NetRevenue].ProjectID)
ON ([qry NetRevenue].ProjectID = [tbl Projects].ProjectID)
AND ([tbl Projects].ProjectID = [tbl Allocation].ProjectID);


Sincerely,

Chris O.


.
Click to expand...
 
Karen said:
Thanks Chris but now I only get either 0.5 or 1 in the
Total.

-----Original Message-----

I need help with using IIF in my query. I need to make one
calculation based on one criteria and another calculation
based on another. For example:

ProjectID
ProjectType

ProjectType can be either 1 or 2. If it's 1, I need to
divide the Amount by half and if Amount is 2 then the
number is not divided. A copy of the SQL follows in case
this is as clear as mud.

SELECT [tbl Allocation].AllocationDesignationID, [tbl
Allocation].AllocationID, [tbl Allocation].ProjectID, [tbl
Projects].ProjectType, [tbl Allocation].EmployeeID, [qry
NetRevenue].[Net Revenue], [tbl Allocation].Percentage,
[Net Revenue]*[Percentage] AS Amount
FROM [tbl Projects] INNER JOIN (([tbl Allocation
Designation] INNER JOIN [tbl Allocation] ON [tbl
Allocation Designation].AllocationDesignationID = [tbl
Allocation].AllocationDesignationID) INNER JOIN [qry
NetRevenue] ON [tbl Allocation].ProjectID = [qry
NetRevenue].ProjectID) ON ([qry NetRevenue].ProjectID =
[tbl Projects].ProjectID) AND ([tbl Projects].ProjectID =
[tbl Allocation].ProjectID);

Thanks!
karen
Click to expand...

SQL realigned for readability only:

SELECT [tbl Allocation].AllocationDesignationID
,[tbl Allocation].AllocationID
,[tbl Allocation].ProjectID
,[tbl Projects].ProjectType
,[tbl Allocation].EmployeeID
,[qry NetRevenue].[Net Revenue]
,[tbl Allocation].Percentage
,[Net Revenue] * [Percentage] AS Amount
FROM [tbl Projects]
INNER JOIN
(([tbl Allocation Designation]
INNER JOIN
[tbl Allocation]
ON [tbl Allocation
Click to expand...
Designation].AllocationDesignationID =
[tbl Allocation].AllocationDesignationID)
INNER JOIN
[qry NetRevenue]
ON [tbl Allocation].ProjectID = [qry NetRevenue].ProjectID)
ON ([qry NetRevenue].ProjectID = [tbl Projects].ProjectID)
AND ([tbl Projects].ProjectID = [tbl Allocation].ProjectID);


Try:

SELECT [tbl Allocation].AllocationDesignationID
,[tbl Allocation].AllocationID
,[tbl Allocation].ProjectID
,SWITCH(1, [tbl Projects].ProjectType / 2,
2, [tbl Projects].ProjectType)
,[tbl Allocation].EmployeeID
,[qry NetRevenue].[Net Revenue]
,[tbl Allocation].Percentage
,[Net Revenue] * [Percentage] AS Amount
FROM [tbl Projects]
INNER JOIN
(([tbl Allocation Designation]
INNER JOIN
[tbl Allocation]
ON [tbl Allocation
Click to expand...
Designation].AllocationDesignationID =
[tbl Allocation].AllocationDesignationID)
INNER JOIN
[qry NetRevenue]
ON [tbl Allocation].ProjectID = [qry NetRevenue].ProjectID)
ON ([qry NetRevenue].ProjectID = [tbl Projects].ProjectID)
AND ([tbl Projects].ProjectID = [tbl Allocation].ProjectID);


Sincerely,

Chris O.
Click to expand...
Click to expand...

Ok, I see what I've done wrong.

"Amount" is a derived column found further down the Query's SELECT clause.
I read the original specification as if "Amount" were the amount in
ProjectType (actualy, my error was a little more serios than that, as I
didn't write the conditional tests correctly . . .). My apologies.

Note: After a more careful reading, I can't decide whether Amount should be
divided by 2 or .5, but I wrote in 2. If you need it divided by .5, just
change it.


SELECT [tbl Allocation].AllocationDesignationID
,[tbl Allocation].AllocationID
,[tbl Allocation].ProjectID
,SWITCH([tbl Projects].ProjectType = 1, [Net Revenue] * [Percentage] /
2,
[tbl Projects].ProjectType = 2, [Net Revenue] * [Percentage])
AS Amount
,[tbl Allocation].EmployeeID
,[qry NetRevenue].[Net Revenue]
,[tbl Allocation].Percentage
FROM [tbl Projects]
INNER JOIN
(([tbl Allocation Designation]
INNER JOIN
[tbl Allocation]
ON [tbl Allocation Designation].AllocationDesignationID =
[tbl Allocation].AllocationDesignationID)
INNER JOIN
[qry NetRevenue]
ON [tbl Allocation].ProjectID = [qry NetRevenue].ProjectID)
ON ([qry NetRevenue].ProjectID = [tbl Projects].ProjectID)
AND ([tbl Projects].ProjectID = [tbl Allocation].ProjectID);

The above saves without errors, but obviously I wasn't able to test it.


Sincerely,

Chris O.
 
As a further note, when I changed the SWITCH function, I wrote ProjectType
out of the Query.
Just add that column back in. Also, if necessary, move the entire SWITCH
function several positions down in order to get Amount back to its original
position.


Sincerely,

Chris O.
 
Karen.

You are welcome. :)

I'm glad I was able to correct my original oversight.


Sincerely,

Chris O.
 
Back
Top