Help with query please.

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

Hi,
I have to export a range of addresses from an access database.I have built a
query which returns all of the addreses. (I had tons of help with it all by
the person referenced in the next sentence. But the final part has me
confused. I am very new. This was given to me by an extremely knowledgable
young lady.
Select <field List>
From YourTable
Where Number <= 799 OR (Number >= 900 AND Number <=999) OR (Number <= 4000
AND Number <= 5599)

Do I replace <field list> with the name of my field(which is "number") Does
this all get pasted into the field box in my query? Do I replace "yourtable"
with mine "membertable". I have no doubt the expression is correct, but I
need help implementing it.
Thanks,
Frank L
 
Frank,

Well, the expression you have given is not correct as it stands,
actually, although part of that could just be a typo in your post.

Are you familiar with using the Query design window in Access? Make a
new query, add the membertable table to the top panel of the design
window, and drag the fields you want included in your query into the
columns of the grid in the lower panel. Then, go to the Criteria row
in the column with the Number field. Type...
<800
Then go to the next row down in the grid, and type...
Between 900 And 999
Then go to the next row down in the grid, and type...
Between 4000 And 5599

If I have correctly interpreted your requirements, this should do the
trick. Run the query by clicking the toolbar icon with the red ! and
see if it gives the expected result.

If you are interested to see the SQL view of this query, select 'SQL
View' from the View menu.

The only other comment I would make is that it is not a good idea to
use the word Number as the name of a field.

- Steve Schapel, Microsoft Access MVP
 
Steve,
Thanks so much for your response. The original query had fields of
name,address city, state, zip. I had to put switches in each of the
fields(except name) because it is a Club DB (built on Access 97)and has the
following fields(among 40 others) address1,city1,state1,zip1 and
address2,city2,state2,zip2. Depending whether the member is up north or
here in florida(where I am) there would be a 1 or 2 to determine this in the
accounting program. The switches (which I got much help with from the young
lady I mentioned , worked great. It listed only the name, and adress,city,
state,zip where the person was , florida or upnorth address.. Did that make
sense? But, there are many members (whose member # in this accounting
program is a field named Number. We bought this program (over $20,000.) I am
the IT person there. W2kserver, and desktop support. I am trying to learn
access as best I can between my other duties. I will pass along the note
about naming a field Number. You see, I will not mention the company, but
the program is terrible and riddled with bugs. Anyway, I have added the
number field to my original query and ended up here. Monday, when I go to
work I will implement your response and report results back here. One thing,
in the column in my query which is my Number field, when tyou say go to the
next grid, I am assuming you mean criterai, then the cell below it, etc.
Thanks so much,
Frank
 
Frank,

Yes, I think you have got my meaning now.

If you have other criteria in other fields in the same query, this may
complicate the method I explained before about putting in the
membership number criteria. If you need more help with it, it may be
good to post back with the SQL view of the actual query.

I probably shouldn't comment on your database without more
information... But to be honest the idea of 2 sets of address fields
is enough to make one feel very jittery.

To expand on my comment on the field name, Number has a special
meaning in Access, and is classified (along with others) as a Reserved
Word. Its use as the name of a field or control or database object
can lead to errors under some circumstances.

- Steve Schapel, Microsoft Access MVP
 
Hi Steve,
Actuall there are 6 address fields. we only use 2. They are numbered
adress1, address2,address3, etc, and the same for ciyt,state and zip. This
is a DB accounting program for Country clubs . The 6 addresses are for
Billing, Summer,Social,etc. The idea was depending on the mailing(is it a
bill, a calendar of events, special notice, etc) you could direct the mail
to that address. I personally think it is one of the stupidest things I have
ever seen. To clutter a DB with seperate mailings like this is ludicrous and
bad programming (planning), in my opinion. I will let you know how the query
works out on minday.
Thanks again,
Frank L W2000 Server MCP
 
Hi Frank,

Whereas there is sometimes a requirement for more than one address per
whatever, e.g. street address, postal address, etc, propogating this
through additional fields is not good design, so I agree with you in
this. If there is a need for these additional addresses, they should
be in a separate table, with an AddressType field.

- Steve Schapel, Microsoft Access MVP
 
Back
Top