Case When statement in ADP

[Guest]

Greetings all, and thank you once again for taking the time to read my
problem. I have a table 'tbljob_info' that contains the fields 'job_id',
'SFUEng', 'MDUEng', 'dist_feeder', and 'month_for_splicing'. SFU and MDU
stand for "Single Family Units", and "Multiple Dwelling Units" respectively,
and are the number of residences on an engineering work order to be spliced
by cable splicers. 'dist_feeder' pertains to whether a job is a distribution
job, or a feeder job. As a scheduler I am trying to use a view to generate a
report showing the total number of SFUs and MDUs that are scheduled for any
month by setting the criteria of the 'month_for_splicing' field to 'January',
or what ever the current month is. My problem is this; distribution jobs
entail much less splicing work, and are typically completed within the month,
whereas feeder jobs may cover an entire town, and have thousands of MDUs on
them, and take months to complete. When I generate my report it looks like I
expect to complete all of the feeder jobs when in reality I do not. I want
to be able to control the output of the 'MDUEng' field based on whether or
not the job is 'F', feeder, or 'D', distribution. A friend told me to enter
IIF[dist_feeder]=F,200,[MDUEng]). The 200 pertains to an arbitrary
quantity of MDUs I want to complete when the job is a feeder job. If it is a
distribution I want the real # of MDUs to show in the report. Does this make
sense?I have found, however that IIF statements do not work with SQL Server,
and that I need to use a Case When statement. I do not know how to do this,
nor can I find anything here. Can anyone help me? I am using and ADP with
SQL 2000. Thank you.

 

[[MVP] S.Clark]

Read this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

Ask any question about it.

 

[Guest]

Thank you so much for responding. I read that, and it makes sense, but I do
not know how to apply it. Where on the view grid would I enter in the case
function, in the criteria or the column name?

Read this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

Ask any question about it.
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Greetings all, and thank you once again for taking the time to read my
problem. I have a table 'tbljob_info' that contains the fields 'job_id',
'SFUEng', 'MDUEng', 'dist_feeder', and 'month_for_splicing'. SFU and MDU
stand for "Single Family Units", and "Multiple Dwelling Units"
respectively,
and are the number of residences on an engineering work order to be
spliced
by cable splicers. 'dist_feeder' pertains to whether a job is a
distribution
job, or a feeder job. As a scheduler I am trying to use a view to
generate a
report showing the total number of SFUs and MDUs that are scheduled for
any
month by setting the criteria of the 'month_for_splicing' field to
'January',
or what ever the current month is. My problem is this; distribution jobs
entail much less splicing work, and are typically completed within the
month,
whereas feeder jobs may cover an entire town, and have thousands of MDUs
on
them, and take months to complete. When I generate my report it looks
like I
expect to complete all of the feeder jobs when in reality I do not. I
want
to be able to control the output of the 'MDUEng' field based on whether or
not the job is 'F', feeder, or 'D', distribution. A friend told me to
enter
IIF[dist_feeder]=F,200,[MDUEng]). The 200 pertains to an arbitrary
quantity of MDUs I want to complete when the job is a feeder job. If it
is a
distribution I want the real # of MDUs to show in the report. Does this
make
sense?I have found, however that IIF statements do not work with SQL
Server,
and that I need to use a Case When statement. I do not know how to do
this,
nor can I find anything here. Can anyone help me? I am using and ADP
with
SQL 2000. Thank you.

 

[Sylvain Lafontaine]

For your particular case, then it's in the column name because you want to
modify the output of one column and not change the filtering of the selected
records.

However, I don't remember if you can do this with the view grid. Usually,
you must go into the text mode and directly write your sql statement when
you want to use an advanced feature such as using an UNION. Specifically
for the case statement, I don't remember exactly.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thank you so much for responding. I read that, and it makes sense, but I
do
not know how to apply it. Where on the view grid would I enter in the
case
function, in the criteria or the column name?

Read this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

Ask any question about it.
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Greetings all, and thank you once again for taking the time to read my
problem. I have a table 'tbljob_info' that contains the fields
'job_id',
'SFUEng', 'MDUEng', 'dist_feeder', and 'month_for_splicing'. SFU and
MDU
stand for "Single Family Units", and "Multiple Dwelling Units"
respectively,
and are the number of residences on an engineering work order to be
spliced
by cable splicers. 'dist_feeder' pertains to whether a job is a
distribution
job, or a feeder job. As a scheduler I am trying to use a view to
generate a
report showing the total number of SFUs and MDUs that are scheduled for
any
month by setting the criteria of the 'month_for_splicing' field to
'January',
or what ever the current month is. My problem is this; distribution
jobs
entail much less splicing work, and are typically completed within the
month,
whereas feeder jobs may cover an entire town, and have thousands of
MDUs
on
them, and take months to complete. When I generate my report it looks
like I
expect to complete all of the feeder jobs when in reality I do not. I
want
to be able to control the output of the 'MDUEng' field based on whether
or
not the job is 'F', feeder, or 'D', distribution. A friend told me to
enter
IIF[dist_feeder]=F,200,[MDUEng]). The 200 pertains to an arbitrary
quantity of MDUs I want to complete when the job is a feeder job. If
it
is a
distribution I want the real # of MDUs to show in the report. Does
this
make
sense?I have found, however that IIF statements do not work with SQL
Server,
and that I need to use a Case When statement. I do not know how to do
this,
nor can I find anything here. Can anyone help me? I am using and ADP
with
SQL 2000. Thank you.

 

[Guest]

Thank you Sylvain for the help. Is there somewhere other then the Microsoft
support that would help with what I need to do? I am looking for help not
meant for people who know what they are doing, because I do not.

For your particular case, then it's in the column name because you want to
modify the output of one column and not change the filtering of the selected
records.

However, I don't remember if you can do this with the view grid. Usually,
you must go into the text mode and directly write your sql statement when
you want to use an advanced feature such as using an UNION. Specifically
for the case statement, I don't remember exactly.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thank you so much for responding. I read that, and it makes sense, but I
do
not know how to apply it. Where on the view grid would I enter in the
case
function, in the criteria or the column name?

Read this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

Ask any question about it.
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Greetings all, and thank you once again for taking the time to read my
problem. I have a table 'tbljob_info' that contains the fields
'job_id',
'SFUEng', 'MDUEng', 'dist_feeder', and 'month_for_splicing'. SFU and
MDU
stand for "Single Family Units", and "Multiple Dwelling Units"
respectively,
and are the number of residences on an engineering work order to be
spliced
by cable splicers. 'dist_feeder' pertains to whether a job is a
distribution
job, or a feeder job. As a scheduler I am trying to use a view to
generate a
report showing the total number of SFUs and MDUs that are scheduled for
any
month by setting the criteria of the 'month_for_splicing' field to
'January',
or what ever the current month is. My problem is this; distribution
jobs
entail much less splicing work, and are typically completed within the
month,
whereas feeder jobs may cover an entire town, and have thousands of
MDUs
on
them, and take months to complete. When I generate my report it looks
like I
expect to complete all of the feeder jobs when in reality I do not. I
want
to be able to control the output of the 'MDUEng' field based on whether
or
not the job is 'F', feeder, or 'D', distribution. A friend told me to
enter
IIF[dist_feeder]=F,200,[MDUEng]). The 200 pertains to an arbitrary
quantity of MDUs I want to complete when the job is a feeder job. If
it
is a
distribution I want the real # of MDUs to show in the report. Does
this
make
sense?I have found, however that IIF statements do not work with SQL
Server,
and that I need to use a Case When statement. I do not know how to do
this,
nor can I find anything here. Can anyone help me? I am using and ADP
with
SQL 2000. Thank you.

 

[Sylvain Lafontaine]

Well, one possibility would be to hire someone.

These newsgroups are not here to replace the usual need of hiring (and
paying) someone when it's needed.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thank you Sylvain for the help. Is there somewhere other then the
Microsoft
support that would help with what I need to do? I am looking for help not
meant for people who know what they are doing, because I do not.

For your particular case, then it's in the column name because you want
to
modify the output of one column and not change the filtering of the
selected
records.

However, I don't remember if you can do this with the view grid.
Usually,
you must go into the text mode and directly write your sql statement when
you want to use an advanced feature such as using an UNION. Specifically
for the case statement, I don't remember exactly.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thank you so much for responding. I read that, and it makes sense, but
I
do
not know how to apply it. Where on the view grid would I enter in the
case
function, in the criteria or the column name?

:

Read this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

Ask any question about it.
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Greetings all, and thank you once again for taking the time to read
my
problem. I have a table 'tbljob_info' that contains the fields
'job_id',
'SFUEng', 'MDUEng', 'dist_feeder', and 'month_for_splicing'. SFU
and
MDU
stand for "Single Family Units", and "Multiple Dwelling Units"
respectively,
and are the number of residences on an engineering work order to be
spliced
by cable splicers. 'dist_feeder' pertains to whether a job is a
distribution
job, or a feeder job. As a scheduler I am trying to use a view to
generate a
report showing the total number of SFUs and MDUs that are scheduled
for
any
month by setting the criteria of the 'month_for_splicing' field to
'January',
or what ever the current month is. My problem is this; distribution
jobs
entail much less splicing work, and are typically completed within
the
month,
whereas feeder jobs may cover an entire town, and have thousands of
MDUs
on
them, and take months to complete. When I generate my report it
looks
like I
expect to complete all of the feeder jobs when in reality I do not.
I
want
to be able to control the output of the 'MDUEng' field based on
whether
or
not the job is 'F', feeder, or 'D', distribution. A friend told me
to
enter
IIF[dist_feeder]=F,200,[MDUEng]). The 200 pertains to an
arbitrary
quantity of MDUs I want to complete when the job is a feeder job.
If
it
is a
distribution I want the real # of MDUs to show in the report. Does
this
make
sense?I have found, however that IIF statements do not work with SQL
Server,
and that I need to use a Case When statement. I do not know how to
do
this,
nor can I find anything here. Can anyone help me? I am using and
ADP
with
SQL 2000. Thank you.