469,286 Members | 2,442 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

Conditional SELECT (IF or CASE)

I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;

SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost

FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];

Up until now this has worked fine but now the users want an extra
'sophistication' (ain't it always the way!). They need to have a
different 'surcharge' applied according to the 'dept code' field. Now
as it happens, there are only 5 possible departments, so I was going
to put some extra fields on the client table (surchargeA, surchargeB
etc.) that correspond to each department.
The problem is I don't know how to 'flex' the calculation to use the
'correct' surcharge depending on the value of the [dept code] field.
>From my reading thus far I'm guessing it would have to be something
like the following 'pseudoSQL' but I can't seem to get the syntax
right.

IF [dept code = 530]
[cost]*[surchargeA] AS RealCost
ELSE [dept code = 550]
[cost]*[surchargeB] AS RealCost
ELSE [dept code = 560]
[cost]*[surchargeC] AS RealCost
ELSEIF
[cost]*1.00 AS RealCost
ENDIF

As ever, any advice gratefully received (0;

Simon Harris

Feb 28 '07 #1
6 14796
then what happens when your company expands and adds another
department? Not good...

Instead add a table called "Surcharges". In it your fields will be a
department id and a surcharge value. You could also add in an
effective date so that when the surcharge by department changes you
can still preserve historical data.

Cheers,
Jason Lepack

On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;

SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost

FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];

Up until now this has worked fine but now the users want an extra
'sophistication' (ain't it always the way!). They need to have a
different 'surcharge' applied according to the 'dept code' field. Now
as it happens, there are only 5 possible departments, so I was going
to put some extra fields on the client table (surchargeA, surchargeB
etc.) that correspond to each department.
The problem is I don't know how to 'flex' the calculation to use the
'correct' surcharge depending on the value of the [dept code] field.>From my reading thus far I'm guessing it would have to be something

like the following 'pseudoSQL' but I can't seem to get the syntax
right.

IF [dept code = 530]
[cost]*[surchargeA] AS RealCost
ELSE [dept code = 550]
[cost]*[surchargeB] AS RealCost
ELSE [dept code = 560]
[cost]*[surchargeC] AS RealCost
ELSEIF
[cost]*1.00 AS RealCost
ENDIF

As ever, any advice gratefully received (0;

Simon Harris

Feb 28 '07 #2
Would like to revise my statement. In your surcharge table you will
have a client#, dept_code, surcharge value. Then you join the table
to the query based on client# and dept_code.

Cheers,
Jason Lepack

On Feb 28, 6:57 am, "Jason Lepack" <jlep...@gmail.comwrote:
then what happens when your company expands and adds another
department? Not good...

Instead add a table called "Surcharges". In it your fields will be a
department id and a surcharge value. You could also add in an
effective date so that when the surcharge by department changes you
can still preserve historical data.

Cheers,
Jason Lepack

On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
Up until now this has worked fine but now the users want an extra
'sophistication' (ain't it always the way!). They need to have a
different 'surcharge' applied according to the 'dept code' field. Now
as it happens, there are only 5 possible departments, so I was going
to put some extra fields on the client table (surchargeA, surchargeB
etc.) that correspond to each department.
The problem is I don't know how to 'flex' the calculation to use the
'correct' surcharge depending on the value of the [dept code] field.>From my reading thus far I'm guessing it would have to be something
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
IF [dept code = 530]
[cost]*[surchargeA] AS RealCost
ELSE [dept code = 550]
[cost]*[surchargeB] AS RealCost
ELSE [dept code = 560]
[cost]*[surchargeC] AS RealCost
ELSEIF
[cost]*1.00 AS RealCost
ENDIF
As ever, any advice gratefully received (0;
Simon Harris

Feb 28 '07 #3
I like the sound of this. In fact, something along these lines *had*
passed through my mind (honest!). This might be quite useful in terms
of reducing administrative overhead because there will probably be
relatively few client/dept combinations that will have a 'surcharge'
of anything other than 1.00
How would I phrase the SQL in the query to use '[cost]*[surcharge] AS
RealCost' if there IS a surcharge for that combination otherwise just
use '[cost] AS RealCost'?
In other words, if I do an OUTERJOIN to the surcharges table, how do I
get it to handle the NULL value for most situations? I guess I'll
still need something like;
IF [surcharge IS NULL]
cost AS RealCost
ELSE
[cost]*[surcharge] AS RealCost
ENDIF

Is that syntax right?

Simon

On 28 Feb, 12:00, "Jason Lepack" <jlep...@gmail.comwrote:
Would like to revise my statement. In your surcharge table you will
have a client#, dept_code, surcharge value. Then you join the table
to the query based on client# and dept_code.

Cheers,
Jason Lepack

On Feb 28, 6:57 am, "Jason Lepack" <jlep...@gmail.comwrote:
then what happens when your company expands and adds another
department? Not good...
Instead add a table called "Surcharges". In it your fields will be a
department id and a surcharge value. You could also add in an
effective date so that when the surcharge by department changes you
can still preserve historical data.
Cheers,
Jason Lepack
On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
Up until now this has worked fine but now the users want an extra
'sophistication' (ain't it always the way!). They need to have a
different 'surcharge' applied according to the 'dept code' field. Now
as it happens, there are only 5 possible departments, so I was going
to put some extra fields on the client table (surchargeA, surchargeB
etc.) that correspond to each department.
The problem is I don't know how to 'flex' the calculation to use the
'correct' surcharge depending on the value of the [dept code] field.>From my reading thus far I'm guessing it would have to be something
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
IF [dept code = 530]
[cost]*[surchargeA] AS RealCost
ELSE [dept code = 550]
[cost]*[surchargeB] AS RealCost
ELSE [dept code = 560]
[cost]*[surchargeC] AS RealCost
ELSEIF
[cost]*1.00 AS RealCost
ENDIF
As ever, any advice gratefully received (0;
Simon Harris- Hide quoted text -

- Show quoted text -

Feb 28 '07 #4
The nz function checks to see if a field is null. If it is then it
substitutes the other value.

nz([yourfield],value if yourfield is null)

in your case:

[cost]*nz([surcharge],1) AS RealCost

Cheers,
Jason Lepack

On Feb 28, 11:40 am, simon.har...@kingshc.nhs.uk wrote:
I like the sound of this. In fact, something along these lines *had*
passed through my mind (honest!). This might be quite useful in terms
of reducing administrative overhead because there will probably be
relatively few client/dept combinations that will have a 'surcharge'
of anything other than 1.00
How would I phrase the SQL in the query to use '[cost]*[surcharge] AS
RealCost' if there IS a surcharge for that combination otherwise just
use '[cost] AS RealCost'?
In other words, if I do an OUTERJOIN to the surcharges table, how do I
get it to handle the NULL value for most situations? I guess I'll
still need something like;
IF [surcharge IS NULL]
cost AS RealCost
ELSE
[cost]*[surcharge] AS RealCost
ENDIF

Is that syntax right?

Simon

On 28 Feb, 12:00, "Jason Lepack" <jlep...@gmail.comwrote:
Would like to revise my statement. In your surcharge table you will
have a client#, dept_code, surcharge value. Then you join the table
to the query based on client# and dept_code.
Cheers,
Jason Lepack
On Feb 28, 6:57 am, "Jason Lepack" <jlep...@gmail.comwrote:
then what happens when your company expands and adds another
department? Not good...
Instead add a table called "Surcharges". In it your fields will be a
department id and a surcharge value. You could also add in an
effective date so that when the surcharge by department changes you
can still preserve historical data.
Cheers,
Jason Lepack
On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
Up until now this has worked fine but now the users want an extra
'sophistication' (ain't it always the way!). They need to have a
different 'surcharge' applied according to the 'dept code' field. Now
as it happens, there are only 5 possible departments, so I was going
to put some extra fields on the client table (surchargeA, surchargeB
etc.) that correspond to each department.
The problem is I don't know how to 'flex' the calculation to use the
'correct' surcharge depending on the value of the [dept code] field.>From my reading thus far I'm guessing it would have to be something
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
IF [dept code = 530]
[cost]*[surchargeA] AS RealCost
ELSE [dept code = 550]
[cost]*[surchargeB] AS RealCost
ELSE [dept code = 560]
[cost]*[surchargeC] AS RealCost
ELSEIF
[cost]*1.00 AS RealCost
ENDIF
As ever, any advice gratefully received (0;
Simon Harris- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Feb 28 '07 #5
You know what? That is *so* elegant I'm almost tempted to dial into
work and try it now (0; Or else, I might just slap myself and cash in
my kudos points tommorrow, instead!
Either way, thanks for the help!

Simon

On 28 Feb, 17:21, "Jason Lepack" <jlep...@gmail.comwrote:
The nz function checks to see if a field is null. If it is then it
substitutes the other value.

nz([yourfield],value if yourfield is null)

in your case:

[cost]*nz([surcharge],1) AS RealCost

Cheers,
Jason Lepack

On Feb 28, 11:40 am, simon.har...@kingshc.nhs.uk wrote:
I like the sound of this. In fact, something along these lines *had*
passed through my mind (honest!). This might be quite useful in terms
of reducing administrative overhead because there will probably be
relatively few client/dept combinations that will have a 'surcharge'
of anything other than 1.00
How would I phrase the SQL in the query to use '[cost]*[surcharge] AS
RealCost' if there IS a surcharge for that combination otherwise just
use '[cost] AS RealCost'?
In other words, if I do an OUTERJOIN to the surcharges table, how do I
get it to handle the NULL value for most situations? I guess I'll
still need something like;
IF [surcharge IS NULL]
cost AS RealCost
ELSE
[cost]*[surcharge] AS RealCost
ENDIF
Is that syntax right?
Simon
On 28 Feb, 12:00, "Jason Lepack" <jlep...@gmail.comwrote:
Would like to revise my statement. In your surcharge table you will
have a client#, dept_code, surcharge value. Then you join the table
to the query based on client# and dept_code.
Cheers,
Jason Lepack
On Feb 28, 6:57 am, "Jason Lepack" <jlep...@gmail.comwrote:
then what happens when your company expands and adds another
department? Not good...
Instead add a table called "Surcharges". In it your fields will be a
department id and a surcharge value. You could also add in an
effective date so that when the surcharge by department changes you
can still preserve historical data.
Cheers,
Jason Lepack
On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
Up until now this has worked fine but now the users want an extra
'sophistication' (ain't it always the way!). They need to have a
different 'surcharge' applied according to the 'dept code' field. Now
as it happens, there are only 5 possible departments, so I was going
to put some extra fields on the client table (surchargeA, surchargeB
etc.) that correspond to each department.
The problem is I don't know how to 'flex' the calculation to use the
'correct' surcharge depending on the value of the [dept code] field.>From my reading thus far I'm guessing it would have to be something
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
IF [dept code = 530]
[cost]*[surchargeA] AS RealCost
ELSE [dept code = 550]
[cost]*[surchargeB] AS RealCost
ELSE [dept code = 560]
[cost]*[surchargeC] AS RealCost
ELSEIF
[cost]*1.00 AS RealCost
ENDIF
As ever, any advice gratefully received (0;
Simon Harris- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Feb 28 '07 #6
So did it work?

Cheers,
Jason Lepack

On Feb 28, 5:48 pm, simon.har...@kingshc.nhs.uk wrote:
You know what? That is *so* elegant I'm almost tempted to dial into
work and try it now (0; Or else, I might just slap myself and cash in
my kudos points tommorrow, instead!
Either way, thanks for the help!

Simon

On 28 Feb, 17:21, "Jason Lepack" <jlep...@gmail.comwrote:
The nz function checks to see if a field is null. If it is then it
substitutes the other value.
nz([yourfield],value if yourfield is null)
in your case:
[cost]*nz([surcharge],1) AS RealCost
Cheers,
Jason Lepack
On Feb 28, 11:40 am, simon.har...@kingshc.nhs.uk wrote:
I like the sound of this. In fact, something along these lines *had*
passed through my mind (honest!). This might be quite useful in terms
of reducing administrative overhead because there will probably be
relatively few client/dept combinations that will have a 'surcharge'
of anything other than 1.00
How would I phrase the SQL in the query to use '[cost]*[surcharge] AS
RealCost' if there IS a surcharge for that combination otherwise just
use '[cost] AS RealCost'?
In other words, if I do an OUTERJOIN to the surcharges table, how do I
get it to handle the NULL value for most situations? I guess I'll
still need something like;
IF [surcharge IS NULL]
cost AS RealCost
ELSE
[cost]*[surcharge] AS RealCost
ENDIF
Is that syntax right?
Simon
On 28 Feb, 12:00, "Jason Lepack" <jlep...@gmail.comwrote:
Would like to revise my statement. In your surcharge table you will
have a client#, dept_code, surcharge value. Then you join the table
to the query based on client# and dept_code.
Cheers,
Jason Lepack
On Feb 28, 6:57 am, "Jason Lepack" <jlep...@gmail.comwrote:
then what happens when your company expands and adds another
department? Not good...
Instead add a table called "Surcharges". In it your fields will be a
department id and a surcharge value. You could also add in an
effective date so that when the surcharge by department changes you
can still preserve historical data.
Cheers,
Jason Lepack
On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
Up until now this has worked fine but now the users want an extra
'sophistication' (ain't it always the way!). They need to have a
different 'surcharge' applied according to the 'dept code' field. Now
as it happens, there are only 5 possible departments, so I was going
to put some extra fields on the client table (surchargeA, surchargeB
etc.) that correspond to each department.
The problem is I don't know how to 'flex' the calculation to use the
'correct' surcharge depending on the value of the [dept code] field.>From my reading thus far I'm guessing it would have to be something
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
IF [dept code = 530]
[cost]*[surchargeA] AS RealCost
ELSE [dept code = 550]
[cost]*[surchargeB] AS RealCost
ELSE [dept code = 560]
[cost]*[surchargeC] AS RealCost
ELSEIF
[cost]*1.00 AS RealCost
ENDIF
As ever, any advice gratefully received (0;
Simon Harris- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Mar 12 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Newbie | last post: by
9 posts views Thread by Kevin | last post: by
7 posts views Thread by Lauren Quantrell | last post: by
8 posts views Thread by | last post: by
1 post views Thread by microsoft.public.dotnet.languages.vb | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.