adding values to a custom field

Discuss using and your tips/tricks for CATraxx
Post Reply
distero
Posts: 3
Joined: Sun Nov 12, 2017 7:55 pm

adding values to a custom field

Post by distero » Sun Nov 12, 2017 8:01 pm

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?

User avatar
DougWilliams
Site Admin
Posts: 235
Joined: Thu May 22, 2014 11:36 am
Location: Wisconsin, USA
Contact:

Re: adding values to a custom field

Post by DougWilliams » Sun Nov 12, 2017 11:36 pm

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];
Record Collector
"You Mean I'm Supposed To Listen To 'Em?!?!?!?"

distero
Posts: 3
Joined: Sun Nov 12, 2017 7:55 pm

Re: adding values to a custom field

Post by distero » Mon Nov 13, 2017 12:25 am

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.

distero
Posts: 3
Joined: Sun Nov 12, 2017 7:55 pm

Re: adding values to a custom field

Post by distero » Tue Nov 14, 2017 10:03 am

can I add some text before and after the [Album][...] fields? how? with "...text..." ?
or even combine sdome existing fields?

User avatar
DougWilliams
Site Admin
Posts: 235
Joined: Thu May 22, 2014 11:36 am
Location: Wisconsin, USA
Contact:

Re: adding values to a custom field

Post by DougWilliams » Tue Nov 14, 2017 11:22 am

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.
Record Collector
"You Mean I'm Supposed To Listen To 'Em?!?!?!?"

Bret_B
Posts: 4
Joined: Thu Nov 16, 2017 11:06 pm
Location: Arizona
Contact:

Re: adding values to a custom field

Post by Bret_B » Wed Mar 21, 2018 3:59 pm

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];
Bret Blakeslee
———————
The Moving Finger writes, and having writ,
Moves on, nor all thy piety, nor wit,
Shall lure it back to cancel half a Line,
Nor all thy tears wash out a Word of It.

Omar Khayyam ca. 1100 BCE & Edward FitzGerald ca. 1825 ACE

User avatar
DougWilliams
Site Admin
Posts: 235
Joined: Thu May 22, 2014 11:36 am
Location: Wisconsin, USA
Contact:

Re: adding values to a custom field

Post by DougWilliams » Wed Mar 21, 2018 4:11 pm

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.
Record Collector
"You Mean I'm Supposed To Listen To 'Em?!?!?!?"

Bret_B
Posts: 4
Joined: Thu Nov 16, 2017 11:06 pm
Location: Arizona
Contact:

Re: adding values to a custom field

Post by Bret_B » Wed Mar 21, 2018 4:27 pm

Okay, my task is to copy data from field to field. How would you recommend I do this?
Bret Blakeslee
———————
The Moving Finger writes, and having writ,
Moves on, nor all thy piety, nor wit,
Shall lure it back to cancel half a Line,
Nor all thy tears wash out a Word of It.

Omar Khayyam ca. 1100 BCE & Edward FitzGerald ca. 1825 ACE

User avatar
DougWilliams
Site Admin
Posts: 235
Joined: Thu May 22, 2014 11:36 am
Location: Wisconsin, USA
Contact:

Re: adding values to a custom field

Post by DougWilliams » Thu Mar 22, 2018 1:15 am

what field to what field?
Record Collector
"You Mean I'm Supposed To Listen To 'Em?!?!?!?"

Post Reply