merge tables

[Dan @BCBS]

One table used check boxes, the other table used yes/no.
How can merge tables into one without loosing the data.
I don't mind changing the check boxes to yes/no or visa-versa, but it looks
like this will delete the data.

HELP

 

[Rick B]

Unless I am mistaken, a checkbox field is a yes/no field. At the table
level, you format the field as yes/no data type. The "format" (yes/no,
true/false, on/off) does not matter. The data in thetable will still be a 0
or a -1.

Rick B

 

[Dan @BCBS]

I'm sorry, it's not a check box in one table and a yes/no in the other - they
used the Number format in the first table. Then used Y=1 and N=2, strange!!

But for the second part of my question - how can I merge these two tables
into one??

 

[Rick B]

Y=1, N=2?

Where'd they get that?

I'd run an update query to change all the 1's into -1 and all the 2s into 0.

Then you can change the field type to boolean (yes/no)

Rick B

 

[Dan @BCBS]

Since you asked, they used a seperate table which had Y=1 and N=2 and 0=0
then they used ita as a drop down to populate it.

Could you save me some time: Once I have my new query, I changed it from a
select to a update query, what do I put in the Update To: line for each
veriable I need to change. I'm guessing something like IF Y Then 1 or IF N
then 0..

Thanks

 

[Bas Cost Budde]

Since you asked, they used a seperate table which had Y=1 and N=2 and 0=0
then they used ita as a drop down to populate it.

Could you save me some time: Once I have my new query, I changed it from a
select to a update query, what do I put in the Update To: line for each
veriable I need to change. I'm guessing something like IF Y Then 1 or IF N
then 0..

Use two update queries. In the Update To: you put the new value, in the
Criteria: the old value.

If you had many different values, I'd create a table with those first,
set a reference, with cascade update (sounds very scientific but is
simple to do), and change the table value then. One to many!

 

[Rick B]

I am not sure if you can use an IF. In the past, I have just used criteria
to find all the "1"s and updated it to -1, then changed my query to find all
the 2's and updated that to 0, etc.

BUT...

It sounds like they want a triple-state control, -1, 0, and null. If that
is the case, then you don't make the field boolean, you make it a number. I
have not set up a triple-state field in a while, but I think you have to
make it type "Number" and I think you can set the Field Size to "Single".
You would then end up with three possible values in your field. 0 =
False, -1 = True, and null = n/a or not selected.

You would add your field to your forms as a check box and you would set the
"triple-state" property of the checkbox.

You might want to read the help fiel on "triple-state" before you go any
further.

Rick B

 

[John Vinson]

One table used check boxes, the other table used yes/no.

Checkboxes are controls. Textboxes are a different type of control.

Tables do not contain checkboxes, OR textboxes. They contain data; if
the data is in a Yes/No field type, the value Yes/True/checked is
stored as -1, and the value No/False/unchecked is stored as 0.

How can merge tables into one without loosing the data.
I don't mind changing the check boxes to yes/no or visa-versa, but it looks
like this will delete the data.

Nope. Just run an Append query appending the data in one table into
the other, or two Append queries appending each table into a new
table. You can use either type of control you like to display the
value that's stored in the resulting table, without losing anything.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps