Updating from text to number fields 1

[kupe]

I’m trying to make a relational database of a large single-table db.

There are some look-up fields that I would like to update from the previous table. The information to be moved across sits in text fields. But the new ones, being look-up fields, has the data type ‘Number’.

Is there a way to update from a text field to a Number field with an update query, please Experts?

 

[KenReay]

Hi

assuming the text fields actually contain numbers, then how about the Val() function

eg INSERT INTO tblNewTable SELECT val(mytext) FROM tblOldTable;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Sorry a typo

eg INSERT INTO tblNewTable SELECT val(mytext) As nLookup FROM tblOldTable;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kupe]

Thanks very much, Ken.

Just one thing: I moved over the 'old' fields into the new relational tables, so it's a case of moving the fields across in the same table.

Will that make the code

INSERT INTO tblNewTable SELECT val(mytext) As nLookup FROM OldField;

please? (That doesn't look quite right somehow.) Cheers

 

[KenReay]

Hi

No if you are "moving" within the same table, you need an update query

UPDATE tblNewTable SET nField = Val(txtField);

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kupe]

Very grateful for that Ken. When I had a chance to try the previous suggestion - not till last night - I could see that is wasn't going to work. And didn't, of course.

This looks great - and it's going straight into my sql prompt book. Many thanks

 

[kupe]

Ug ...

UPDATE tblEmployees SET UnitID = Val(hostel);

... only Access says key violations and won't oblige. Gloom. Is there an answer, do you think, Ken?

 

[KenReay]

Hi

Is UnitId a key field with a unique constraint on it? (look in table design view and see indexes on menu)

Not sure how you want to proceed, you could remove the Unique constraint, but thatmay lead to problems furtehr down the line, difficult to comment without knowinging your data

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kupe]

Not a key field, and I have set no indexing for this table, and see no reference to indexing on the menu in table design view.

Of course, I could do the updating manually - it would take just an hour or two which is less than I've spent in trying to guess the query way of doing it.

But you'd think there has to be a way. Thanks, Ken, for the directions.

 

[KenReay]

Hi

From the error message I think you will find there is an index. I note your column name ends with Id. It is possible to set Access options so that fields ending in certain letters are automatically indexed. Id is a common setting for this (see Tools \ Options \ TablesQueries), and double check the View \ Indexes , table design view

There is no doubt it is possible to do what you are trying to do

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kupe]

Thanks, Ken. UnitID has ID added to it, of course, because it is coming from a lookup table. Because the field is part of the lookup arrangement, it's data type is Number.

I've been assuming that because the new field is Number and the old one Text, Access won't action the queries. Cheers.