Help: set values of two fields based on value of a third

[JB]

I'm working with a simple membership database for a political action group.
What I want to do is very simple and has nothing to do with user input. I
just want to update the records with information myself, behind the scenes.

Specifically, for each record, I want to fill the CONGRESSIONAL DISTRICT and
CONGRESSPERSON fields based on the values of the ZIP field.

In the simple language of database programming - which apparently no longer
exists - this is what I want to do:

USE MEMBERS INDEX ZIP
LOCATE for ZIP="10300"
DO WHILE ZIP < "10304"
REPLACE CONGRESSPERSON with "Vito Fossella (R)"
REPLACE CONGRESSIONAL DISTRICT with "13"
NEXT
ENDDO

I've already spent over an hour trying to get this simple program into a a
macro in Access (XP Pro) with Set Value. But something is wrong with the
expressions and I can't get it to work.

How can something so simple get so complicated?

JB

 

[Nikos Yannacopoulos]

JB,

What you need here is an Update query on the table, not a macro! Start
making a new query in design view, add the table in question and drag
down to the grid the fields ZIP, CONGRESSPERSON and CONGRESSIONAL
DISTRICT. In the first criterion line under ZIP, type:

= "10300" And <= "10304"

(omit the quotes if the field is numeric)

revert to datasheet view, to verify that your criterion is working as
expected. When happy with it, go Query > Update in the menu, and notice
the new row headed "Update To" that appears in the grid; in it, type the
values that you want under the respective fields, and go Query > Run...
job is done.

Note: it never hurt anyone to back up before trying something new!

HTH,
Nikos

 

[JB]

Thank you! It never occurred to me that one could do anything with a query
but look at existing data - certainly not change it. This is terrific, it
works like a charm.

Let me pose another problem: How can I quickly search for duplicate records
- ones with the same firsta nd last names, or duplicate addresses, not
knowing ahead of time what content I would be looking for - just that it
would be duplicate?

Thanks again
JB

 

[Nikos Yannacopoulos]

JB,

What you refer to by "look at existing data" is a Select query. There
are also Action queries, including Update (what you did), Append, Make
Table and Delete. The right combination of those can work wonders is
many cases, so you might want to play around with them in a test
database, to familiarize with them.

The new query wizard (which starts when you click on New in the datbase
window, queries container) offers more useful choices, one of which is
the Find Duplicates one; this is the one you need.

That said, if you have many duplications on names, then your database is
probably not well normalized. Try the analyzer (Tools > Analyze >
Table), Access will give you some hints if this is the case.

HTH,
Nikos

 

[JB]

Nikos: Thank you! Great help. I've been playing with the various forms of
queries - apparently one can change the type of query Action to Select and
back again. Very useful.
I actually used to know about the various types of queries. But a hiatus of
only a year or so has really burned big holes in my Access skill set. Never
has anyone forgotton so much in so little time. Apparently I'm succumbing to
CRS. Chomps bigtime..
JB

 

[Nikos Yannacopoulos]

Glad I could help. I admit I didn't know what CRS stands for, I had to
google it and came up with a number of different explanations. I suppose
you're referring to "can't remember s**t"? (I'm not being shy here, the
corporate gateway firewall is and won't even let this word go through!).

Nikos

 

[JB]

You got it.
I have another problem. What I do most is web sites. I just sterted creating
a brand new site for a firiend. The only thing on it is four jpegs and one
line of text. The thing is, only one of the jpegs will show up! I've never
seen this before. I've rechecked the code a zillion times, but the image
simply won't display. The biggest is 1052 KB, converted in Photoshop to jpg
from from an .eps file- but I've seen much bigger images display with no
problem, and the smaller ones - 600 KB - won't show up either. If you want
to look at the page it's http://home.si.rr.com/jbostrom/ISS. If you can't
help me, please point me to the proper newsgroup. Thanks. JOhn

 

[Nikos Yannacopoulos]

Sorry, JB, my web design skills aren't much better than your Greek!
Suggest you look for a newsgroup on the software you are using to create
the page.

Nikos