Sometimes you need to make changes in your data - cleanup, standardization, etc. A question often heard in NICAR bootcamps is something like "I know how to do this in FoxPro, but I don't know how to do it in Access." Since the reverse of this question also comes up, I'll cover the basics of both.
Updating data
Here is the query in Access:
UPDATE Contribs SET NewOccupation = "Homemaker" WHERE Occupation = "Housewife"
This is how to do the same thing in FoxPro: there are two commands typed in the command window.
USE Contribs REPLACE ALL ; NewOccupation WITH "Homemaker" ; FOR Occupation = "Housewife"
In FoxPro, the table you are working on is defined first, in a separate command ("use," which is the same as going through the File/Open dialog box.) In Access, the table to work on is specified after the word UPDATE. The words are different, but they have in common a way to define the column to work on, the text to put in it and which rows to perform the change.
Deleting data
Access: Maybe you won't use this as often, and never on the only copy of original data. Uplink readers would never commit a blunder like that.
Anyway, in creating a subset of a larger data set, sometimes you make the first cut by creating a new table based on rows meeting a certain criteria.
You could do subsequent delete commands to further whittle down that table. This SQL command in Access will delete all rows where the occupation field contains "ATTORNEY" DELETE FROM Contribs WHERE Occupation = "ATTORNEY"
Access tells you how many records will be deleted before it actually deletes the records. If you click cancel on the message box, nothing happens and the records remain. If you click OK, Access deletes the records and you can't retrieve them.
FoxPro
USE Contribs DELETE FOR Occupation = "ATTORNEY"
Again, FoxPro takes two commands to do what is specified in one command in SQL and Access. After FoxPro command, the records fitting the specified condition are only marked for deletion; they still remain in the table. You can still RECALL the records. This next command does the deletion and is not reversible.
PACK
FoxPro will not give you a prompt before this command is carried out. If you say "oops," the next thing you say as the smart Uplink TechTip reader that you are: "I'm glad I have a backup."
Richard Mullins can be reached at (573) 882-2127, or send e-mail to richard@nicar.org