February 28th, 2007, 10:15 AM
| | |  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. Quote: |
>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 |
February 28th, 2007, 12:05 PM
| | | Re: Conditional SELECT (IF or CASE)
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: Quote:
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
| |
February 28th, 2007, 12:15 PM
| | | Re: Conditional SELECT (IF or CASE)
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: Quote:
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:
> Quote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
| > Quote:
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
| > Quote:
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
| > Quote:
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
| > Quote:
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
| > Quote:
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
| > Quote: |
As ever, any advice gratefully received (0;
| > | |
February 28th, 2007, 04:55 PM
| | | Re: Conditional SELECT (IF or CASE)
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: Quote:
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:
>
>
> Quote:
then what happens when your company expands and adds another
department? Not good...
| > Quote:
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.
| >> Quote: |
On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
| > Quote: Quote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
| | > Quote: Quote:
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
| | > Quote: Quote:
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
| | > Quote: Quote:
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
| | > Quote: Quote:
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
| | > Quote: Quote:
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
| | > Quote: Quote: |
As ever, any advice gratefully received (0;
| | > Quote: Quote: |
Simon Harris- Hide quoted text -
| | >
- Show quoted text -
| |
February 28th, 2007, 05:35 PM
| | | Re: Conditional SELECT (IF or CASE)
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: Quote:
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:
>
>
> Quote:
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.
| >> Quote: |
On Feb 28, 6:57 am, "Jason Lepack" <jlep...@gmail.comwrote:
| > Quote: Quote:
then what happens when your company expands and adds another
department? Not good...
| | > Quote: Quote:
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.
| | >> Quote: Quote: |
On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
| | > Quote: Quote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
| | > Quote: Quote:
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
| | > Quote: Quote:
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
| | > Quote: Quote:
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
| | > Quote: Quote:
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
| | > Quote: Quote:
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
| | > Quote: Quote: |
As ever, any advice gratefully received (0;
| | > Quote: Quote: |
Simon Harris- Hide quoted text -
| | > Quote: |
- Show quoted text -- Hide quoted text -
| >
- Show quoted text -
| |
February 28th, 2007, 09:55 PM
| | | Re: Conditional SELECT (IF or CASE)
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: Quote:
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:
>
>
> Quote:
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
| >>> Quote: |
On 28 Feb, 12:00, "Jason Lepack" <jlep...@gmail.comwrote:
| > Quote: Quote:
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.
| | >> Quote: Quote: |
On Feb 28, 6:57 am, "Jason Lepack" <jlep...@gmail.comwrote:
| | > Quote: Quote:
then what happens when your company expands and adds another
department? Not good...
| | > Quote: Quote:
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.
| | >> Quote: Quote: |
On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
| | > Quote: Quote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
| | > Quote: Quote:
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
| | > Quote: Quote:
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
| | > Quote: Quote:
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
| | > Quote: Quote:
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
| | > Quote: Quote:
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
| | > Quote: Quote: |
As ever, any advice gratefully received (0;
| | > Quote: Quote: |
Simon Harris- Hide quoted text -
| | > Quote: Quote: |
- Show quoted text -- Hide quoted text -
| | > Quote: |
- Show quoted text -- Hide quoted text -
| >
- Show quoted text -
| |
March 12th, 2007, 09:45 PM
| | | Re: Conditional SELECT (IF or CASE)
So did it work?
Cheers,
Jason Lepack
On Feb 28, 5:48 pm, simon.har...@kingshc.nhs.uk wrote: Quote:
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:
>
>
> Quote:
The nz function checks to see if a field is null. If it is then it
substitutes the other value.
| > Quote: |
nz([yourfield],value if yourfield is null)
| >> Quote: |
[cost]*nz([surcharge],1) AS RealCost
| >> Quote: |
On Feb 28, 11:40 am, simon.har...@kingshc.nhs.uk wrote:
| > Quote: Quote:
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
| | >>> Quote: Quote: |
On 28 Feb, 12:00, "Jason Lepack" <jlep...@gmail.comwrote:
| | > Quote: Quote:
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.
| | >> Quote: Quote: |
On Feb 28, 6:57 am, "Jason Lepack" <jlep...@gmail.comwrote:
| | > Quote: Quote:
then what happens when your company expands and adds another
department? Not good...
| | > Quote: Quote:
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.
| | >> Quote: Quote: |
On Feb 28, 5:06 am, simon.har...@kingshc.nhs.uk wrote:
| | > Quote: Quote:
I have a query in an Access2000 db (created using the QBE as it
happens!) that the resulting SQL looks like this;
| | > Quote: Quote:
SELECT
CLIENTS.[Client#],
Logfile.[Dept Code],
Logfile.[Test Code],
SASTESTS.Cost,
SASTESTS.[Cost]*CLIENTS.[Surcharge] AS RealCost
| | > Quote: Quote:
FROM CLIENTS
INNER JOIN (Logfile INNER JOIN SASTESTS ON Logfile.[Test Code] =
SASTESTS.[Test Code])
ON CLIENTS.[Client#] = Logfile.[Client#];
| | > Quote: Quote:
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
| | > Quote: Quote:
like the following 'pseudoSQL' but I can't seem to get the syntax
right.
| | > Quote: Quote:
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
| | > Quote: Quote: |
As ever, any advice gratefully received (0;
| | > Quote: Quote: |
Simon Harris- Hide quoted text -
| | > Quote: Quote: |
- Show quoted text -- Hide quoted text -
| | > Quote: Quote: |
- Show quoted text -- Hide quoted text -
| | > Quote: |
- Show quoted text -- Hide quoted text -
| >
- Show quoted text -
| |
Posting Rules
| You may not post new threads You may not post replies You may not post attachments You may not edit your posts
HTML code is Off | | |
| | | What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over network members.
|
