Page 1 of 1

adding values to a custom field

Posted: Sun Nov 12, 2017 8:01 pm
by distero
I would like to add the Album.Title to the Custom Field 01.
How can I do this for all the Albums in the database with one instruction?

Re: adding values to a custom field

Posted: Sun Nov 12, 2017 11:36 pm
by DougWilliams
You will need to use the utility SQLUpdate: http://www.fnprg.com/index.html

BACKUP YOUR DATABASE BEFORE USING THIS QUERY.

This will update EVERY CUSTOM01 with the Album Title regardless of whether or not there is already data in the field.

Code: Select all

UPDATE Album INNER JOIN AlbumCustom ON Album.AlbumID = AlbumCustom.AlbumID SET AlbumCustom.Custom01 = [album].[Title];

Re: adding values to a custom field

Posted: Mon Nov 13, 2017 12:25 am
by distero
thank you for the quick respons!
Frederik had send me a solution some 10 years ago, but I've lost it ...
This looks the same it seems to me.

Re: adding values to a custom field

Posted: Tue Nov 14, 2017 10:03 am
by distero
can I add some text before and after the [Album][...] fields? how? with "...text..." ?
or even combine sdome existing fields?

Re: adding values to a custom field

Posted: Tue Nov 14, 2017 11:22 am
by DougWilliams
Something like
SET AlbumCustom.Custom01 = "The Greatest Album:" +[album].[Title]+" is my favorite" (it might be single quotes instead of the double quotes. I'm not able to test)

Other fields can be done but becomes more complicated if the field is in a different table. If you can provide the field names I can provide some examples.

Re: adding values to a custom field

Posted: Wed Mar 21, 2018 3:59 pm
by Bret_B
Hi Doug,

This appears to be a solution to copy data from any single field to any single field, with the caveat that copies within a table are easier and I have to use the SQLUpdate utility, is this correct?
UPDATE Album INNER JOIN AlbumCustom ON Album.AlbumID = AlbumCustom.AlbumID SET AlbumCustom.Custom01 = [album].[Title];

Re: adding values to a custom field

Posted: Wed Mar 21, 2018 4:11 pm
by DougWilliams
In the most simplistic updating, yes.
However, it can drastically change depending on what field you are wanting to update and from where.
Using MS Access to create an update does make things easier (remembering to backup of course) but still, understanding HOW the data is stored and WHY it is stored that way is very important too.

Re: adding values to a custom field

Posted: Wed Mar 21, 2018 4:27 pm
by Bret_B
Okay, my task is to copy data from field to field. How would you recommend I do this?

Re: adding values to a custom field

Posted: Thu Mar 22, 2018 1:15 am
by DougWilliams
what field to what field?