Use of "IF" in Access ADP stored procedure - Help

[Mickey1234]

Hi,

How would I be able to use CASE statement if it applies to different
table. For example: I have 3 tables, billing, Job_2 and
JOB_COST_Query1_Sub

I want apply this CASE statement and extract some data from the third
table: cost. How would that be possibly be done ? Sounds complicated,
isn't it? Please help .....

Example:
-----------
SELECT dbo.billing.Job_No
FROM dbo.billing INNER JOIN
dbo.Job_2 ON dbo.billing.Job_No = dbo.Job_2.Job_No INNER JOIN
dbo.JOB_COST_Query1_Sub() JOB_COST_Query1_Sub ON dbo.Job_2.Job_No =
JOB_COST_Query1_Sub.Job_No
WHERE (dbo.billing.Job_No = @JobNumber)

Have tried and modified the following query, but it didn't work, why?
SELECT dbo.billing.Job_No, dbo.JOB_COST_Query_Sub, CASE WHEN
[CountAFE]>1 Then 'Multiple' Else [FirstOfAFE No],
FROM dbo.billing INNER JOIN
dbo.Job_2 ON dbo.billing.Job_No = dbo.Job_2.Job_No INNER JOIN
dbo.JOB_COST_Query1_Sub() JOB_COST_Query1_Sub ON dbo.Job_2.Job_No =
JOB_COST_Query1_Sub.Job_No
WHERE (dbo.billing.Job_No = @JobNumber)



Thanks.

 

[Michel Walsh]

Hi,


CASE can only be used where a single parameter (value) is expected, it is
not a "Select Case/ Switch" or like a "If/Then/Else" flow control.


SELECT u.whatever
FROM ( SELECT 1 As TableNb, f1, f2, f2 FROM Table1
UNION ALL
SELECT 2, g1, g2, g3 FROM table2
UNION ALL
SELECT 3, h1, h2, h3 FROM table3) As u
WHERE u.TableNb= [whichTableYouWant]


is an ugly work around.



Hoping it may help,
Vanderghast, Access MVP

 

[Tom Ellison]

Dear Mickey:

I'm having some trouble seeing what you are attempting.

But here are some things I can tell:

Your use of a CASE statement is missing the END keyword.

Are you wanting the JOIN to JOB_COST_Query1_Sub to include only one
row no matter how many are described by the JOIN? Perhaps you should
make this a sub query that returns only one row for each value Job_No.
You could write this as a separate query until you have just what you
want to JOIN with, then include this subquery in the JOIN.

It appears you only need two columns: Job_No on which to join, and
AFE. Could it be:

SELECT Job_No, CASE WHEN JobCt = 1 THEN AFE ELSE 'Multiple' END AS AFE
FROM (
SELECT Job_No, MIN(AFE) AS AFE, COUNT(*) AS JobCt
FROM Job_Cost_Query1_Sub
GROUP BY Job_No
) X

If the above gives you the AFE for each Job_No as you want it, either
make this a View and join to that view, or join to this as a subquery.

Sorry if I've missed the point altogether. I may have made a lot of
assumptions in trying to understand your needs.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts