Update - If statement

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

Guest

My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N"
then 0 Else NA.. Can I add an experssion to do this, if so, what would it
look like..
Does the expression go in the Update To: Field????
 
Are you changing the data in the same field that has the Y or N in it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do what you want.
 
Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another table, that is
why I am changing the data.

Thanks
 
OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>
 
OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field,
obviously I cannot put Y - N or D...






Ken Snell said:
OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Dan @BCBS said:
Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another table, that is
why I am changing the data.

Thanks
Click to expand...
Click to expand...
 
No, what I posted is the entire query's SQL statement. The Criteria: box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table. Put this
expression (after changing FieldName to the real name of the field being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Dan @BCBS said:
OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field,
obviously I cannot put Y - N or D...






Ken Snell said:
OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Dan @BCBS said:
Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another table,
Click to expand...
that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has the Y or N in it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do what you want.

--

Ken Snell
<MS ACCESS MVP>

My data is Y or N, I need to Update the data to: If "Y" then -1
Click to expand...
Else
If
"N"
then 0 Else NA.. Can I add an experssion to do this, if so,
Click to expand...
what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
With your help, I'm so closer..

Let me explain my situation:
Table 1 - Type=Number and Size=Byte
Table 2 - Type=Text and Size=1

I need to merge Table 2 into 1.
Is this what you suggest:
Step 1- Update Query - Table 2 (Y=-1, N=0, D=N).
Step 2- Rename Table 1
Step 3- Create new Table 1 (because the forms/reports already exist).
Step 4- New Update Query with all 3 tables ???
This is where I get stuck, how do I get table 2 data into table 1.????










Ken Snell said:
No, what I posted is the entire query's SQL statement. The Criteria: box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table. Put this
expression (after changing FieldName to the real name of the field being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Dan @BCBS said:
OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field,
obviously I cannot put Y - N or D...






Ken Snell said:
OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Yes - I want to change the same field (Y to -1) and (N to 0) and (D to
N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another table, that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has the Y or N in it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do what you want.

--

Ken Snell
<MS ACCESS MVP>

My data is Y or N, I need to Update the data to: If "Y" then -1 Else
If
"N"
then 0 Else NA.. Can I add an experssion to do this, if so, what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
Let's back up. You're now talking about two tables; but in an earlier post,
you answered my question about this by saying that you were changing the
data in the same field -- nothing was said about two tables.

Let's go back to the beginning. State clearly what you want to achieve --
such as I want to set the value of a field < field name > in a table < table
name > based on the current value in a field < field name > in another table
< table name >, etc. Give examples of the data in the first table, and what
the data should be in the second table, etc.
--

Ken Snell
<MS ACCESS MVP>




Dan @BCBS said:
With your help, I'm so closer..

Let me explain my situation:
Table 1 - Type=Number and Size=Byte
Table 2 - Type=Text and Size=1

I need to merge Table 2 into 1.
Is this what you suggest:
Step 1- Update Query - Table 2 (Y=-1, N=0, D=N).
Step 2- Rename Table 1
Step 3- Create new Table 1 (because the forms/reports already exist).
Step 4- New Update Query with all 3 tables ???
This is where I get stuck, how do I get table 2 data into table 1.????










Ken Snell said:
No, what I posted is the entire query's SQL statement. The Criteria: box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table. Put this
expression (after changing FieldName to the real name of the field being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Dan @BCBS said:
OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field,
obviously I cannot put Y - N or D...






:

OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Yes - I want to change the same field (Y to -1) and (N to 0) and (D to
N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another
Click to expand...
table,
that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has the Y or N
Click to expand...
in
it?
Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do what
Click to expand...
you
want.
--

Ken Snell
<MS ACCESS MVP>

My data is Y or N, I need to Update the data to: If "Y"
Click to expand...
then -1
Else
If
"N"
then 0 Else NA.. Can I add an experssion to do this, if
Click to expand...
so,
what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
I have two databases.
I need to add the data from Database 2 into Database 1, (just one table).

The fields in both tables are Y/N/D, the problem is:
Table 1 - Type=Number (Y/N/D's were entered)
Table 2 - Type=Text (Y/N/D's were entered)
And I cannot change any of the data in Table 1.

I ran the update Query to change Table 2 from Y/N/D to 1/0/N. Done.

Was this a waste of time, I thought if I update Table 2, I could merge the
data.
But did I update it to something that can be merged together?

I want to set the value of a field < ADVDIR > in a table < NGBU> name >
based on the current value in a field < ADVRDIR > in table < SGBU >

SGBU Table Example = field <ADVDIR> format Number.
NGBU Table Example = field <ADVDIR> format Text.

I just want to add the results of NGBU into SGBU.
I hope I explained it better, my desired outcome is very simple.

Thanks for your patience.


Ken Snell said:
Let's back up. You're now talking about two tables; but in an earlier post,
you answered my question about this by saying that you were changing the
data in the same field -- nothing was said about two tables.

Let's go back to the beginning. State clearly what you want to achieve --
such as I want to set the value of a field < field name > in a table < table
name > based on the current value in a field < field name > in another table
< table name >, etc. Give examples of the data in the first table, and what
the data should be in the second table, etc.
--

Ken Snell
<MS ACCESS MVP>




Dan @BCBS said:
With your help, I'm so closer..

Let me explain my situation:
Table 1 - Type=Number and Size=Byte
Table 2 - Type=Text and Size=1

I need to merge Table 2 into 1.
Is this what you suggest:
Step 1- Update Query - Table 2 (Y=-1, N=0, D=N).
Step 2- Rename Table 1
Step 3- Create new Table 1 (because the forms/reports already exist).
Step 4- New Update Query with all 3 tables ???
This is where I get stuck, how do I get table 2 data into table 1.????










Ken Snell said:
No, what I posted is the entire query's SQL statement. The Criteria: box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table. Put this
expression (after changing FieldName to the real name of the field being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field,
obviously I cannot put Y - N or D...






:

OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Yes - I want to change the same field (Y to -1) and (N to 0) and (D to
N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another table,
that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has the Y or N in
it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do what you
want.

--

Ken Snell
<MS ACCESS MVP>

My data is Y or N, I need to Update the data to: If "Y" then -1
Else
If
"N"
then 0 Else NA.. Can I add an experssion to do this, if so,
what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
ACCESS is not designed to allow the storage of letters in a number field in
a table. How did you get such results in Table 1?

If indeed you have letters in the field in Table 1, and indeed that field
has a data type of Number, then the table probably is corrupted and the data
will need to be put into a new table.

--

Ken Snell
<MS ACCESS MVP>


Dan @BCBS said:
I have two databases.
I need to add the data from Database 2 into Database 1, (just one table).

The fields in both tables are Y/N/D, the problem is:
Table 1 - Type=Number (Y/N/D's were entered)
Table 2 - Type=Text (Y/N/D's were entered)
And I cannot change any of the data in Table 1.

I ran the update Query to change Table 2 from Y/N/D to 1/0/N. Done.

Was this a waste of time, I thought if I update Table 2, I could merge the
data.
But did I update it to something that can be merged together?

I want to set the value of a field < ADVDIR > in a table < NGBU> name >
based on the current value in a field < ADVRDIR > in table < SGBU >

SGBU Table Example = field <ADVDIR> format Number.
NGBU Table Example = field <ADVDIR> format Text.

I just want to add the results of NGBU into SGBU.
I hope I explained it better, my desired outcome is very simple.

Thanks for your patience.


Ken Snell said:
Let's back up. You're now talking about two tables; but in an earlier post,
you answered my question about this by saying that you were changing the
data in the same field -- nothing was said about two tables.

Let's go back to the beginning. State clearly what you want to achieve --
such as I want to set the value of a field < field name > in a table < table
name > based on the current value in a field < field name > in another table
< table name >, etc. Give examples of the data in the first table, and w hat
the data should be in the second table, etc.
--

Ken Snell
<MS ACCESS MVP>




Dan @BCBS said:
With your help, I'm so closer..

Let me explain my situation:
Table 1 - Type=Number and Size=Byte
Table 2 - Type=Text and Size=1

I need to merge Table 2 into 1.
Is this what you suggest:
Step 1- Update Query - Table 2 (Y=-1, N=0, D=N).
Step 2- Rename Table 1
Step 3- Create new Table 1 (because the forms/reports already exist).
Step 4- New Update Query with all 3 tables ???
This is where I get stuck, how do I get table 2 data into table 1.????










:

No, what I posted is the entire query's SQL statement. The Criteria: box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table. Put this
expression (after changing FieldName to the real name of the field being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field,
obviously I cannot put Y - N or D...






:

OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Yes - I want to change the same field (Y to -1) and (N to 0)
Click to expand...
and
(D to
N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another table,
that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has the Y
Click to expand...
or N
in
it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do
Click to expand...
what
you
want.

--

Ken Snell
<MS ACCESS MVP>

My data is Y or N, I need to Update the data to: If "Y" then -1
Else
If
"N"
then 0 Else NA.. Can I add an experssion to do this,
Click to expand...
if
so,
what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
That does not sound good!
Table 1 is all of South Florida answers to a Doctor survey, all of table 2
is North FL.
Two Databases were used for the past few years, but now North and South have
merged the data into one for both N & S. And of course I need the results
yesterday. Got any openings at Microsoft, I can sweep floors...




Ken Snell said:
ACCESS is not designed to allow the storage of letters in a number field in
a table. How did you get such results in Table 1?

If indeed you have letters in the field in Table 1, and indeed that field
has a data type of Number, then the table probably is corrupted and the data
will need to be put into a new table.

--

Ken Snell
<MS ACCESS MVP>


Dan @BCBS said:
I have two databases.
I need to add the data from Database 2 into Database 1, (just one table).

The fields in both tables are Y/N/D, the problem is:
Table 1 - Type=Number (Y/N/D's were entered)
Table 2 - Type=Text (Y/N/D's were entered)
And I cannot change any of the data in Table 1.

I ran the update Query to change Table 2 from Y/N/D to 1/0/N. Done.

Was this a waste of time, I thought if I update Table 2, I could merge the
data.
But did I update it to something that can be merged together?

I want to set the value of a field < ADVDIR > in a table < NGBU> name >
based on the current value in a field < ADVRDIR > in table < SGBU >

SGBU Table Example = field <ADVDIR> format Number.
NGBU Table Example = field <ADVDIR> format Text.

I just want to add the results of NGBU into SGBU.
I hope I explained it better, my desired outcome is very simple.

Thanks for your patience.


Ken Snell said:
Let's back up. You're now talking about two tables; but in an earlier post,
you answered my question about this by saying that you were changing the
data in the same field -- nothing was said about two tables.

Let's go back to the beginning. State clearly what you want to achieve --
such as I want to set the value of a field < field name > in a table < table
name > based on the current value in a field < field name > in another table
< table name >, etc. Give examples of the data in the first table, and w hat
the data should be in the second table, etc.
--

Ken Snell
<MS ACCESS MVP>




With your help, I'm so closer..

Let me explain my situation:
Table 1 - Type=Number and Size=Byte
Table 2 - Type=Text and Size=1

I need to merge Table 2 into 1.
Is this what you suggest:
Step 1- Update Query - Table 2 (Y=-1, N=0, D=N).
Step 2- Rename Table 1
Step 3- Create new Table 1 (because the forms/reports already exist).
Step 4- New Update Query with all 3 tables ???
This is where I get stuck, how do I get table 2 data into table 1.????










:

No, what I posted is the entire query's SQL statement. The Criteria: box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table. Put this
expression (after changing FieldName to the real name of the field being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0",
IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field,
obviously I cannot put Y - N or D...






:

OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Yes - I want to change the same field (Y to -1) and (N to 0) and
(D to
N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another
table,
that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has the Y or N
in
it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do what
you
want.

--

Ken Snell
<MS ACCESS MVP>

My data is Y or N, I need to Update the data to: If "Y"
then -1
Else
If
"N"
then 0 Else NA.. Can I add an experssion to do this, if
so,
what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
Can't help you with Microsoft... I don't work for them! < g >

Seeing as how you apparently are able to read the data, then you may not be
in as dire straits as it might seem, depending upon how the database is set
up. You can copy all the data into a new table. Then, the next steps to
recover what appears to be a corrupt table will depend upon what
relationships have been established between this table and other tables
(Relationships window, stored queries), as changes to the table's fields
and/or replacing the table may require the reestablishment of those
relationships.

My thought about a corrupt database is based on just the info that you've
posted, not from hands-on experience with your actual database. It may be
something else entirely.

--

Ken Snell
<MS ACCESS MVP>



Dan @BCBS said:
That does not sound good!
Table 1 is all of South Florida answers to a Doctor survey, all of table 2
is North FL.
Two Databases were used for the past few years, but now North and South have
merged the data into one for both N & S. And of course I need the results
yesterday. Got any openings at Microsoft, I can sweep floors...




Ken Snell said:
ACCESS is not designed to allow the storage of letters in a number field in
a table. How did you get such results in Table 1?

If indeed you have letters in the field in Table 1, and indeed that field
has a data type of Number, then the table probably is corrupted and the data
will need to be put into a new table.

--

Ken Snell
<MS ACCESS MVP>


Dan @BCBS said:
I have two databases.
I need to add the data from Database 2 into Database 1, (just one table).

The fields in both tables are Y/N/D, the problem is:
Table 1 - Type=Number (Y/N/D's were entered)
Table 2 - Type=Text (Y/N/D's were entered)
And I cannot change any of the data in Table 1.

I ran the update Query to change Table 2 from Y/N/D to 1/0/N. Done.

Was this a waste of time, I thought if I update Table 2, I could merge the
data.
But did I update it to something that can be merged together?

I want to set the value of a field < ADVDIR > in a table < NGBU> name
based on the current value in a field < ADVRDIR > in table < SGBU >

SGBU Table Example = field <ADVDIR> format Number.
NGBU Table Example = field <ADVDIR> format Text.

I just want to add the results of NGBU into SGBU.
I hope I explained it better, my desired outcome is very simple.

Thanks for your patience.


:

Let's back up. You're now talking about two tables; but in an
Click to expand...
earlier
post,
you answered my question about this by saying that you were changing the
data in the same field -- nothing was said about two tables.

Let's go back to the beginning. State clearly what you want to achieve --
such as I want to set the value of a field < field name > in a table
Click to expand...
<
table
name > based on the current value in a field < field name > in
Click to expand...
another
table
< table name >, etc. Give examples of the data in the first table,
Click to expand...
and w
hat
the data should be in the second table, etc.
--

Ken Snell
<MS ACCESS MVP>




With your help, I'm so closer..

Let me explain my situation:
Table 1 - Type=Number and Size=Byte
Table 2 - Type=Text and Size=1

I need to merge Table 2 into 1.
Is this what you suggest:
Step 1- Update Query - Table 2 (Y=-1, N=0, D=N).
Step 2- Rename Table 1
Step 3- Create new Table 1 (because the forms/reports already exist).
Step 4- New Update Query with all 3 tables ???
This is where I get stuck, how do I get table 2 data into table 1.????










:

No, what I posted is the entire query's SQL statement. The
Click to expand...
Criteria:
box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table. Put this
expression (after changing FieldName to the real name of the
Click to expand...
field
being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0",
IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field,
obviously I cannot put Y - N or D...






:

OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Yes - I want to change the same field (Y to -1) and (N to
Click to expand...
0)
and
(D to
N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another
table,
that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has the
Click to expand...
Y
or N
in
it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do what
you
want.

--

Ken Snell
<MS ACCESS MVP>

My data is Y or N, I need to Update the data to: If "Y"
then -1
Else
If
"N"
then 0 Else NA.. Can I add an experssion to do
Click to expand...
this,
if
so,
what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
You use the term Corrupt, this is fileds formatted in numeric but text was
entered, is that considered corrupt, because the entire database works fine
with the data.
I realise the developer should have not used numeric, but that is my
challenge, to convert the format of the other database (text) into numeric to
read it into this database. There are no relationships between the two
tables.

Just to over simplify and repeat my question - is there any way to change
the format from text to numeric, without losing all the data (which is only
Y/N/D)..

Thanks



Ken Snell said:
Can't help you with Microsoft... I don't work for them! < g >

Seeing as how you apparently are able to read the data, then you may not be
in as dire straits as it might seem, depending upon how the database is set
up. You can copy all the data into a new table. Then, the next steps to
recover what appears to be a corrupt table will depend upon what
relationships have been established between this table and other tables
(Relationships window, stored queries), as changes to the table's fields
and/or replacing the table may require the reestablishment of those
relationships.

My thought about a corrupt database is based on just the info that you've
posted, not from hands-on experience with your actual database. It may be
something else entirely.

--

Ken Snell
<MS ACCESS MVP>



Dan @BCBS said:
That does not sound good!
Table 1 is all of South Florida answers to a Doctor survey, all of table 2
is North FL.
Two Databases were used for the past few years, but now North and South have
merged the data into one for both N & S. And of course I need the results
yesterday. Got any openings at Microsoft, I can sweep floors...




Ken Snell said:
ACCESS is not designed to allow the storage of letters in a number field in
a table. How did you get such results in Table 1?

If indeed you have letters in the field in Table 1, and indeed that field
has a data type of Number, then the table probably is corrupted and the data
will need to be put into a new table.

--

Ken Snell
<MS ACCESS MVP>


I have two databases.
I need to add the data from Database 2 into Database 1, (just one table).

The fields in both tables are Y/N/D, the problem is:
Table 1 - Type=Number (Y/N/D's were entered)
Table 2 - Type=Text (Y/N/D's were entered)
And I cannot change any of the data in Table 1.

I ran the update Query to change Table 2 from Y/N/D to 1/0/N. Done.

Was this a waste of time, I thought if I update Table 2, I could merge the
data.
But did I update it to something that can be merged together?

I want to set the value of a field < ADVDIR > in a table < NGBU> name
Click to expand...
based on the current value in a field < ADVRDIR > in table < SGBU >

SGBU Table Example = field <ADVDIR> format Number.
NGBU Table Example = field <ADVDIR> format Text.

I just want to add the results of NGBU into SGBU.
I hope I explained it better, my desired outcome is very simple.

Thanks for your patience.


:

Let's back up. You're now talking about two tables; but in an earlier
post,
you answered my question about this by saying that you were changing the
data in the same field -- nothing was said about two tables.

Let's go back to the beginning. State clearly what you want to
achieve --
such as I want to set the value of a field < field name > in a table <
table
name > based on the current value in a field < field name > in another
table
< table name >, etc. Give examples of the data in the first table, and w
hat
the data should be in the second table, etc.
--

Ken Snell
<MS ACCESS MVP>




With your help, I'm so closer..

Let me explain my situation:
Table 1 - Type=Number and Size=Byte
Table 2 - Type=Text and Size=1

I need to merge Table 2 into 1.
Is this what you suggest:
Step 1- Update Query - Table 2 (Y=-1, N=0, D=N).
Step 2- Rename Table 1
Step 3- Create new Table 1 (because the forms/reports already exist).
Step 4- New Update Query with all 3 tables ???
This is where I get stuck, how do I get table 2 data into table 1.????










:

No, what I posted is the entire query's SQL statement. The Criteria:
box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table. Put this
expression (after changing FieldName to the real name of the field
being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0",
IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination
field,
obviously I cannot put Y - N or D...






:

OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Yes - I want to change the same field (Y to -1) and (N to 0)
and
(D to
N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another
table,
that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has the Y
or N
in
it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do
what
you
want.

--

Ken Snell
<MS ACCESS MVP>

message
My data is Y or N, I need to Update the data to: If "Y"
then -1
Else
If
"N"
then 0 Else NA.. Can I add an experssion to do this,
if
so,
what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
If you have copied the data from that table into another table so that
you'll have it available for putting back in, you could go into design view
of the "offending" table and change the data type to Number (this assumes
that the data type is currently Text -- if it is already Number, then try
changing it to Text, save the table, and then change it back to Number).
Then save the table; if the table is working correctly, ACCESS will probably
give you a warning that the data in that field for "x" records will be
deleted because the data do not meet the data type requirement. Then you can
use an update query to put the original data (changing it to 1 or 0) back
into the field for each record.

Note that a Number field will not accept N as a data value.
--

Ken Snell
<MS ACCESS MVP>



Dan @BCBS said:
You use the term Corrupt, this is fileds formatted in numeric but text was
entered, is that considered corrupt, because the entire database works fine
with the data.
I realise the developer should have not used numeric, but that is my
challenge, to convert the format of the other database (text) into numeric to
read it into this database. There are no relationships between the two
tables.

Just to over simplify and repeat my question - is there any way to change
the format from text to numeric, without losing all the data (which is only
Y/N/D)..

Thanks



Ken Snell said:
Can't help you with Microsoft... I don't work for them! < g >

Seeing as how you apparently are able to read the data, then you may not be
in as dire straits as it might seem, depending upon how the database is set
up. You can copy all the data into a new table. Then, the next steps to
recover what appears to be a corrupt table will depend upon what
relationships have been established between this table and other tables
(Relationships window, stored queries), as changes to the table's fields
and/or replacing the table may require the reestablishment of those
relationships.

My thought about a corrupt database is based on just the info that you've
posted, not from hands-on experience with your actual database. It may be
something else entirely.

--

Ken Snell
<MS ACCESS MVP>



Dan @BCBS said:
That does not sound good!
Table 1 is all of South Florida answers to a Doctor survey, all of table 2
is North FL.
Two Databases were used for the past few years, but now North and
Click to expand...
South
have
merged the data into one for both N & S. And of course I need the results
yesterday. Got any openings at Microsoft, I can sweep floors...




:

ACCESS is not designed to allow the storage of letters in a number
Click to expand...
field
in
a table. How did you get such results in Table 1?

If indeed you have letters in the field in Table 1, and indeed that field
has a data type of Number, then the table probably is corrupted and
Click to expand...
the
data
will need to be put into a new table.

--

Ken Snell
<MS ACCESS MVP>


I have two databases.
I need to add the data from Database 2 into Database 1, (just one table).

The fields in both tables are Y/N/D, the problem is:
Table 1 - Type=Number (Y/N/D's were entered)
Table 2 - Type=Text (Y/N/D's were entered)
And I cannot change any of the data in Table 1.

I ran the update Query to change Table 2 from Y/N/D to 1/0/N. Done.

Was this a waste of time, I thought if I update Table 2, I could
Click to expand...
merge
the
data.
But did I update it to something that can be merged together?

I want to set the value of a field < ADVDIR > in a table < NGBU> name

based on the current value in a field < ADVRDIR > in table < SGBU

SGBU Table Example = field <ADVDIR> format Number.
NGBU Table Example = field <ADVDIR> format Text.

I just want to add the results of NGBU into SGBU.
I hope I explained it better, my desired outcome is very simple.

Thanks for your patience.


:

Let's back up. You're now talking about two tables; but in an earlier
post,
you answered my question about this by saying that you were
Click to expand...
changing
the
data in the same field -- nothing was said about two tables.

Let's go back to the beginning. State clearly what you want to
achieve --
such as I want to set the value of a field < field name > in a
Click to expand...
table
<
table
name > based on the current value in a field < field name > in another
table
< table name >, etc. Give examples of the data in the first
Click to expand...
table,
and w
hat
the data should be in the second table, etc.
--

Ken Snell
<MS ACCESS MVP>




With your help, I'm so closer..

Let me explain my situation:
Table 1 - Type=Number and Size=Byte
Table 2 - Type=Text and Size=1

I need to merge Table 2 into 1.
Is this what you suggest:
Step 1- Update Query - Table 2 (Y=-1, N=0, D=N).
Step 2- Rename Table 1
Step 3- Create new Table 1 (because the forms/reports already exist).
Step 4- New Update Query with all 3 tables ???
This is where I get stuck, how do I get table 2 data into
Click to expand...
table
1.????
:

No, what I posted is the entire query's SQL statement. The Criteria:
box
should be empty on your grid after you create this query.

To set it up in design view of the query, select the table.
Click to expand...
Put
this
expression (after changing FieldName to the real name of the field
being
updated) in the UpdateTo: box:

IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0",
IIf([FieldName]="D",
"N", [FieldName])))


--

Ken Snell
<MS ACCESS MVP>

OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination
field,
obviously I cannot put Y - N or D...






:

OK:

UPDATE TableName
SET FieldName = IIf([FieldName]="Y", "-1",
IIf([FieldName]="N", "0", IIf([FieldName]="D", "N",
[FieldName])));

--

Ken Snell
<MS ACCESS MVP>


Yes - I want to change the same field (Y to -1) and (N
Click to expand...
to
0)
and
(D to
N) - N
because N/A will not work.

Yes - it is Text.

Once I change the field I want to merge the data into another
table,
that
is
why I am changing the data.

Thanks




:

Are you changing the data in the same field that has
Click to expand...
the
Y
or N
in
it?

Is that field a boolean or text field?

Please, a bit more info so that we can suggest how to do
what
you
want.

--

Ken Snell
<MS ACCESS MVP>

message
My data is Y or N, I need to Update the data to:
Click to expand...
If
"Y"
then -1
Else
If
"N"
then 0 Else NA.. Can I add an experssion to do this,
if
so,
what
would
it
look like..
Does the expression go in the Update To: Field????
Click to expand...
Click to expand...
Click to expand...
 
Back
Top