Format

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

Guest

Two Access tables, one text, the other numeric, both populate Y or N.
I need to copy/paste the text data into the numeric table (not visa versa
beacuse I need the rest of the DB to work).
I used Replace in Excel, to change all the text (Y/N) to 1/2. Then I copied
all the data onto one excel sheet and imported it into the Access DB.

Sounds good, but a large amount to the data I replaced, gets lost..

Help, I'm now in crunch time...
 
Don't use EXCEL as a fancy clipboard for this. Instead, use an Update query
to write the data from one table into the other (assuming that the records
already exist in both tables), or an Append query to copy the data from the
first table to the second (assuming that the records exist only in the first
table at this point).

If you can post back with more info, we can assist you in writing the
appropriate query.
 
Please help me with the Update query - data is in both tables.
Table #1 is DataType - Number.
Table #2 is Data Type - Text.
Both only have Y or N.

I need to keep Table #1-number because the rest of the DB is build, I just
have to add data from another DB which is Table #2-text.

Could you please help.
 
A number field will not have a Y or N as a value, so I assume that a Y in
the text field should be a 1 in the number field, and an N in the text field
should be a 2 in the number field.

UPDATE [Table #1] INNER JOIN
[Table #2] ON [Table #1].[NameOfLinkingField] =
[Table #2].[NameOfLinkingField]
SET [Table #1].[FieldName] =
IIf([Table #2].[FieldName] = "Y", 1, 2);

--

Ken Snell
<MS ACCESS MVP>
 
Back
Top