How to update this...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there! Using A02 on XP. Not a programmer but learning.

I have some data that I'll be exporting to a comma delimited (.csv) file and
would like to update a field first.

I get back a file we've sent to clients to update their employee data. They
edit existing, add new and mark some as terminated, reporting hours worked
and wages earned.

I have a field [Hours] that may be edited in two different ways. The
directions include the following grid to use for reporting hours worked:

0 = 0 - 500
1 = 501 - 999
2 = 1000 and up

They may fill in the 0, 1, 2's or use the 500, 999 and 1000. Some may
actually key in the exact hours worked such as 1257.75 or 864.0 or 522. I
need to send the data to my system using the 0, 1 & 2's.

I know I can do it with three update queries but wonder if I can do this
with one step? Should I use a Switch or Case? Not sure.

Thanks in advance for any help or advice!!!
 
Hi, Bonnie.

First, your reporting hours grid doesn't accommodate the numbers between 500
and 501, nor the numbers between 999 and 1,000, so your update query needs to
do so. Second, make a copy of your table as a backup in case something goes
wrong with the query.

If I understand your question correctly, you want to replace the Hours field
with a 0, 1, or 2, depending upon the value currently in the Hours field. If
so, then the following update query would assign the correct values:

UPDATE tblEmpHours
SET Hours = IIF((Hours <= 2), Hours, SWITCH((Hours <= 500), 0, (Hours <
1000), 1, (Hours >= 1000), 2));

.. . . where tblEmpHours is the name of the table. If your code of 0, 1, or
2 has already been assigned by the users, then this value remains the same.
If instead the number of hours is in the Hours field (500, 522, et cetera),
then the appropriate code will be assigned. You may want to use different
cutoffs, but in the above query, if the hours are 500 or less, then a 0 is
assigned. If the hours are 1,000 or more, then a 2 is assigned. If the
hours are any other value, then a 1 is assigned.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Hi Gunny,

Thanks for the infor. Sorry, yes I had gaps. I'll clean that up.

I'm excited that I was close thinking it might be a switch thing. I'll work
on it tomorrow.

Thanks again for taking time to help folks!
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

First, your reporting hours grid doesn't accommodate the numbers between 500
and 501, nor the numbers between 999 and 1,000, so your update query needs to
do so. Second, make a copy of your table as a backup in case something goes
wrong with the query.

If I understand your question correctly, you want to replace the Hours field
with a 0, 1, or 2, depending upon the value currently in the Hours field. If
so, then the following update query would assign the correct values:

UPDATE tblEmpHours
SET Hours = IIF((Hours <= 2), Hours, SWITCH((Hours <= 500), 0, (Hours <
1000), 1, (Hours >= 1000), 2));

. . . where tblEmpHours is the name of the table. If your code of 0, 1, or
2 has already been assigned by the users, then this value remains the same.
If instead the number of hours is in the Hours field (500, 522, et cetera),
then the appropriate code will be assigned. You may want to use different
cutoffs, but in the above query, if the hours are 500 or less, then a 0 is
assigned. If the hours are 1,000 or more, then a 2 is assigned. If the
hours are any other value, then a 1 is assigned.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi there! Using A02 on XP. Not a programmer but learning.

I have some data that I'll be exporting to a comma delimited (.csv) file and
would like to update a field first.

I get back a file we've sent to clients to update their employee data. They
edit existing, add new and mark some as terminated, reporting hours worked
and wages earned.

I have a field [Hours] that may be edited in two different ways. The
directions include the following grid to use for reporting hours worked:

0 = 0 - 500
1 = 501 - 999
2 = 1000 and up

They may fill in the 0, 1, 2's or use the 500, 999 and 1000. Some may
actually key in the exact hours worked such as 1257.75 or 864.0 or 522. I
need to send the data to my system using the 0, 1 & 2's.

I know I can do it with three update queries but wonder if I can do this
with one step? Should I use a Switch or Case? Not sure.

Thanks in advance for any help or advice!!!
Click to expand...
Click to expand...
 
Hi, Bonnie.

You're welcome. Yes. The Switch( ) method will get you most of the way
there. However, since the value in the Hours field could fall into one of
three categories (your special code, actual hours, or top of hour range),
you found yourself in need of extra coding to assign the value you need.
It's less work and fewer chances of errors if each field in a record is used
for only one attribute.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that questions answered the quickest are often from those who have a history
of rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi Gunny,

Thanks for the infor. Sorry, yes I had gaps. I'll clean that up.

I'm excited that I was close thinking it might be a switch thing. I'll
work
on it tomorrow.

Thanks again for taking time to help folks!
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

First, your reporting hours grid doesn't accommodate the numbers between
500
and 501, nor the numbers between 999 and 1,000, so your update query
needs to
do so. Second, make a copy of your table as a backup in case something
goes
wrong with the query.

If I understand your question correctly, you want to replace the Hours
field
with a 0, 1, or 2, depending upon the value currently in the Hours field.
If
so, then the following update query would assign the correct values:

UPDATE tblEmpHours
SET Hours = IIF((Hours <= 2), Hours, SWITCH((Hours <= 500), 0, (Hours <
1000), 1, (Hours >= 1000), 2));

. . . where tblEmpHours is the name of the table. If your code of 0, 1,
or
2 has already been assigned by the users, then this value remains the
same.
If instead the number of hours is in the Hours field (500, 522, et
cetera),
then the appropriate code will be assigned. You may want to use
different
cutoffs, but in the above query, if the hours are 500 or less, then a 0
is
assigned. If the hours are 1,000 or more, then a 2 is assigned. If the
hours are any other value, then a 1 is assigned.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi there! Using A02 on XP. Not a programmer but learning.

I have some data that I'll be exporting to a comma delimited (.csv)
file and
would like to update a field first.

I get back a file we've sent to clients to update their employee data.
They
edit existing, add new and mark some as terminated, reporting hours
worked
and wages earned.

I have a field [Hours] that may be edited in two different ways. The
directions include the following grid to use for reporting hours
worked:

0 = 0 - 500
1 = 501 - 999
2 = 1000 and up

They may fill in the 0, 1, 2's or use the 500, 999 and 1000. Some may
actually key in the exact hours worked such as 1257.75 or 864.0 or 522.
I
need to send the data to my system using the 0, 1 & 2's.

I know I can do it with three update queries but wonder if I can do
this
with one step? Should I use a Switch or Case? Not sure.

Thanks in advance for any help or advice!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top