URGENT!! Using IIF in query

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
 
C

Chris2

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.
 
K

Karen

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...
 
C

Chris2

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.
 
C

Chris2

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.
 
C

Chris2

Karen.

You are welcome. :)

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


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query too slow 1
recursive relationship query question 8
Struggling with query 22
Slow Query 3
Report based on two similar queries 1
Calculation In Query 1
Using a list box in a query 5
Calculation in Query 5

Top