changing values of one field based on another

How can I best change the values of one field in a table based on
values of another field of the same table.  We have an existing table
of thousands of entries and I would like to use the following logic to
populate a new boolean field.
If field1 = "Done" Then BooleanFieldCompleted = True

I have some Excel VBA experience but limited Access.  I dont want to
do this manually! Any assistance appreciated.
0
bluegrassstateworker
3/13/2008 7:11:33 PM
access 16762 articles. 3 followers. Follow

6 Replies
508 Views

Similar Articles

[PageSpeed] 33

In general, you'd use an Update query. However, in this case I don't see why 
you'd need such a field.

Why not just create a query with a computed field that returns True or False 
based on the value in field1? You can then use the query wherever you'd 
otherwise have used the table.

Storing a field that's entirely derivable from one or more other fields in 
the same row is seldom a good idea.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"bluegrassstateworker" <andy.crumbacker@gmail.com> wrote in message 
news:5425d58f-cf89-406e-8c58-0cee997d523f@b1g2000hsg.googlegroups.com...
> How can I best change the values of one field in a table based on
> values of another field of the same table.  We have an existing table
> of thousands of entries and I would like to use the following logic to
> populate a new boolean field.
> If field1 = "Done" Then BooleanFieldCompleted = True
>
> I have some Excel VBA experience but limited Access.  I dont want to
> do this manually! Any assistance appreciated. 


0
Douglas
3/13/2008 7:37:48 PM
UPDATE TableName SET TableName.BooleanFieldCompleted = True
WHERE (([TableName]![Field1]="Done"));

Change the names to what they really are in your application
I assume the BooleanFieldCompleted is a True/False (not text) field.  If not 
(it is text) then change to 

UPDATE TableName SET TableName.BooleanFieldCompleted = "True"
WHERE (([TableName]![Field1]="Done"));

Good luck


-- 
Wayne
Manchester, England.



"bluegrassstateworker" wrote:

> How can I best change the values of one field in a table based on
> values of another field of the same table.  We have an existing table
> of thousands of entries and I would like to use the following logic to
> populate a new boolean field.
> If field1 = "Done" Then BooleanFieldCompleted = True
> 
> I have some Excel VBA experience but limited Access.  I dont want to
> do this manually! Any assistance appreciated.
> 
0
Utf
3/13/2008 7:48:01 PM
bluegrassstateworker,

Do you NEED to store this in the table?  It seems you are adding an 
unnecessary field to a table...you already have one that tells you the same 
thing.  By adding a new field to your table, you are increasing the size of 
it.  You can do what you are asking with a query where it will create a new 
calculated column based on the value of [field1].

What are all the possible values for [field1]?  If it is only "Done" and ONE 
other thing (ie. NULL, "Not Done", etc.), then the most normalized thing to 
do is remove [field1] from the table and replace it with a field of booleen 
data type.  If there are more than 2 possible values for [field1], then 
leaving it like it is and having a calculated column in a query is probably 
the better option.

If you need to store a separate field in the table, then maybe something 
like this would work:

UPDATE [YourTableName]
SET [BooleanFieldCompleted] = -1
WHERE [field1] = "Done"

HTH,

Conan





"bluegrassstateworker" <andy.crumbacker@gmail.com> wrote in message 
news:5425d58f-cf89-406e-8c58-0cee997d523f@b1g2000hsg.googlegroups.com...
> How can I best change the values of one field in a table based on
> values of another field of the same table.  We have an existing table
> of thousands of entries and I would like to use the following logic to
> populate a new boolean field.
> If field1 = "Done" Then BooleanFieldCompleted = True
>
> I have some Excel VBA experience but limited Access.  I dont want to
> do this manually! Any assistance appreciated. 


0
Conan
3/13/2008 7:51:26 PM
bluegrassstateworker,

Just to add on to Douglas's comments (and mine), if you store this booleen 
field/column separately in the table, then some of the records get changed 
to "Done" in the future, you will have to manually change this field to TRUE 
for each of these records, or rerun this update query.  It would be very 
easy for these 2 fields to be out of sync.

If you just have a calculated column in a query, then this column would be 
re-calculated every time the query is run, which would mean these two 
columns will always be in sync.

HTH,

Conan


\
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
news:uiog5GUhIHA.2268@TK2MSFTNGP02.phx.gbl...
> In general, you'd use an Update query. However, in this case I don't see 
> why you'd need such a field.
>
> Why not just create a query with a computed field that returns True or 
> False based on the value in field1? You can then use the query wherever 
> you'd otherwise have used the table.
>
> Storing a field that's entirely derivable from one or more other fields in 
> the same row is seldom a good idea.
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "bluegrassstateworker" <andy.crumbacker@gmail.com> wrote in message 
> news:5425d58f-cf89-406e-8c58-0cee997d523f@b1g2000hsg.googlegroups.com...
>> How can I best change the values of one field in a table based on
>> values of another field of the same table.  We have an existing table
>> of thousands of entries and I would like to use the following logic to
>> populate a new boolean field.
>> If field1 = "Done" Then BooleanFieldCompleted = True
>>
>> I have some Excel VBA experience but limited Access.  I dont want to
>> do this manually! Any assistance appreciated.
>
> 


0
Conan
3/13/2008 7:58:37 PM
On Mar 13, 3:48=A0pm, Wayne-I-M <Wayn...@discussions.microsoft.com>
wrote:
> UPDATE TableName SET TableName.BooleanFieldCompleted =3D True
> WHERE (([TableName]![Field1]=3D"Done"));
>
> Change the names to what they really are in your application
> I assume the BooleanFieldCompleted is a True/False (not text) field. =A0If=
 not
> (it is text) then change to
>
> UPDATE TableName SET TableName.BooleanFieldCompleted =3D "True"
> WHERE (([TableName]![Field1]=3D"Done"));
>
> Good luck
>
> --
> Wayne
> Manchester, England.
>
>
>
> "bluegrassstateworker" wrote:
> > How can I best change the values of one field in a table based on
> > values of another field of the same table. =A0We have an existing table
> > of thousands of entries and I would like to use the following logic to
> > populate a new boolean field.
> > If field1 =3D "Done" Then BooleanFieldCompleted =3D True
>
> > I have some Excel VBA experience but limited Access. =A0I dont want to
> > do this manually! Any assistance appreciated.- Hide quoted text -
>
> - Show quoted text -

To some, I didnt make clear: I want to change a group of values
permanently in the table.  This will be a one-time maintenance task.
Going forward, this task will not be performed.
0
bluegrassstateworker
3/13/2008 8:00:51 PM
On Mar 13, 4:00=A0pm, bluegrassstateworker <andy.crumbac...@gmail.com>
wrote:
> On Mar 13, 3:48=A0pm, Wayne-I-M <Wayn...@discussions.microsoft.com>
> wrote:
>
>
>
>
>
> > UPDATE TableName SET TableName.BooleanFieldCompleted =3D True
> > WHERE (([TableName]![Field1]=3D"Done"));
>
> > Change the names to what they really are in your application
> > I assume the BooleanFieldCompleted is a True/False (not text) field. =A0=
If not
> > (it is text) then change to
>
> > UPDATE TableName SET TableName.BooleanFieldCompleted =3D "True"
> > WHERE (([TableName]![Field1]=3D"Done"));
>
> > Good luck
>
> > --
> > Wayne
> > Manchester, England.
>
> > "bluegrassstateworker" wrote:
> > > How can I best change the values of one field in a table based on
> > > values of another field of the same table. =A0We have an existing tabl=
e
> > > of thousands of entries and I would like to use the following logic to=

> > > populate a new boolean field.
> > > If field1 =3D "Done" Then BooleanFieldCompleted =3D True
>
> > > I have some Excel VBA experience but limited Access. =A0I dont want to=

> > > do this manually! Any assistance appreciated.- Hide quoted text -
>
> > - Show quoted text -
>
> To some, I didnt make clear: I want to change a group of values
> permanently in the table. =A0This will be a one-time maintenance task.
> Going forward, this task will not be performed.- Hide quoted text -
>
> - Show quoted text -

A bit of history: this is a database containing tables not well
normalized and they are now realizing it with reporting and archiving
issues.  I need to keep current data and then normalize these tables
in phases.  FIELD1 is actually called: status.  Lo and behold, they
cannot capture entries that could have been in a status other than
"Done" (ie "Declined", "Rejected").  This means that questions such
as, "how many transactions were rejected?" can be answered now and
their last active status recorded.  In the past, the status would need
to be changed to "Done" to separate it out from the active records
currently viewed but their last status would be lost.  Make sense?

In this exercise, my intent is to take the "Done" entry in one FIELD1
and convert it to a TRUE value in the BooleanFieldCompleted field.
That will mark all past transactions that were closed.  Then, I will
remove the "Done" choice from the list box users see when changing the
value in FIELD1.  Going forward, users will have to manually mark the
record using the BooleanFieldCompleted entry when it is completed.  I
will need to do something like this exercise on other fields with the
same reporting issues.  I hate cleaning up: whether it be a house or a
database!
0
bluegrassstateworker
3/13/2008 8:32:21 PM
Reply:

Similar Artilces:

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Territory Change
Our Sales VP has restructured all of the geographical territories that we had set up in Microsoft CRM. Our many thousands of Accounts are associated with territories. Obviously it is ludicrous to think that we would have to go one by one and change the territory on each account to the new territories. However, I have heard that there are strict rules for making changes directly to the backend SQL database. If we develop a SQL statement to change the territories assigned to each account to the new territories, are we going to be breaking anything? We don't want to screw up any de...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

OWA jumping mouse
I have two computers on my network that are having an interesting issue. When they are creating a message and typing away in the body of the message, randomly when they hit a key, any key, they system will act as if the mouse was clicked. If the mouse is over the send button, it would have sent the email. If the mouse was over the start button, the start menu would have come up. Where ever the mouse was left at, it will act as if it was clicked. It is very random but often. This only happens in OWA, not in outlook or any other place. Both systems are running XP pro SP2 with IE 6 SP2. ...

forward to: field
Hi Everyone, im looking for the AD attribute in which the 'forward to:' field of the delivery options is stored. Thank you in advance kind regards marc -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Its in the altRecipient attribute. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Marc Wenger" <haga(at).iesg--nspm-noa9h.gmx.ch> wrote in message news:opr64hs7z6fyi4rt@news.microsoft.com... > Hi Everyone, > > im looking for the AD attribute in which the 'forward to:' field of ...

Changing font in Money 2004 register?
Is there any way to change the font(s) used in MS Money 2004's registers? The default font is too small and difficult for me to read. Also, is there any way to change the color scheme to something more pleasing to my eye (like you can do in Quicken...) Thanks. Nope and Nope. See http://umpmfaq.info/faqdb.php?q=136. "Debbie R." <debbimsr@bellsouth.net> wrote in message news:f5ff01c43e15$e2ae3700$a401280a@phx.gbl... > Is there any way to change the font(s) used in MS Money > 2004's registers? The default font is too small and > difficult for me to read....

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Money 2000 Account Balance Changed Inexplicably
Opening Money 2000, which I've used without problem since late 1999, I noticed that my checking account balance was overstated by almost $2000! I went to the register to see if there was a false transaction entered and it appears that this balance change goes back years with no obvious single entry being the culprit. When I run the "balance this account" function, it shows that the closing balance from my last statement, which was correct and rectified, is now also wrong and reflects the higher balance. What do I do now? The only thing that I can think of is to restore...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Changing language
I am running the Swedish version of Excel for XP and I need the US (or English) version. Is there a way to convert the language and all the settings associated with it? Using the swedish version is rather annoying since the formula names are translated to swedish. Thank you, Magnus ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

COUNTIF on Summary Fields
Can't use on a cell that "sums" with arithmetic operators "+". Is there a workaround, etc.? a bit more explanation? -- Don Guillett SalesAid Software donaldb@281.com "DLC" <dlcopesr@yahoo.com> wrote in message news:117v954kaf4s2f6@corp.supernews.com... > Can't use on a cell that "sums" with arithmetic operators "+". Is there a > workaround, etc.? > > ...

Changing a profile on Microsoft Outlook 2003
I set up two profiles through the control panel and directed Outlook to prompt me for which profile to use each time it was opened. But now it skips the prompt and goes straight to one of the profiles. I need to restore that prompt, but it won't respond to the instructions in the control panel Mail dialogue box Hi Chris, did you get the same behavior after a restart of the computer? You could try "Sart/run/fixmapi.exe" (you don�t get any confirmation message) and restart the computer again. If this wouldn�t wotk, I would create a 3rd (test) profile. Maybe Outlook don�t ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Macro to change default setting on startup
I am in need of a macro that can change a default setting in excel and for it to run on startup The task is: Tools Options General Web Options Files uncheck Update links on save Below is the recording of the macro: With ActiveWorkbook.WebOptions ..RelyOnCSS = True ..OrganizeInFolder = True ..UseLongFileNames = True ..DownloadComponents = False ..RelyOnVML = False ..AllowPNG = False ..ScreenSize = msoScreenSize800x600 ..PixelsPerInch = 96 ..Encoding = msoEncodingWestern End With With Application.DefaultWebOptions ..SaveHiddenData = True ..LoadPictures = True ....

Cant read one instance of recurring appointment
The problem we're having is a little different that what I've seen posted. A user randomly gets the message "Cant read one instance of recurring appointment. close any open appointments and try again, or recreate the appointment" several times a day. When you click OK the same message appears several times. Outlook doesn't indicate which recurring appointment is having the problem and the message just pops up at various times even if the user isn't in the calendar. We've deleted the Frmcache file and run the clean profile switch, but that hasn't worked. Has an...