Can Yes/No be linked to certain fields for a query

[Guest]

I have a table with Permanent Address(yes/no) with
Permstreet,permcity,permstate,permzip; then in same table Present
Address(yes/no)with Presentstreet,prescity,prestate,preszip.

How do I setup a query that will pull the permanent address if the Permanent
Address is Yes OR the Present Address if the Present Address is Yes. I need
to do this to send a letter to the address that should be used for mailing.

 

[Allen Browne]

Sharron, it would be possible to do this with a convoluted combination of
IIf() and DLookup() statements, but it would be considerably easier to
design a correctly normalized address system.

It seems that each client in your database could have more than one address,
such as a present address, a permanent address, and possibly other types.
This suggests a one-to-many relation between clients and addresses.

1. Create a small table to hold the possible address types. Just one field:
AddressTypeID Text (24 char) primary key.
Save the table as tblAddressType.
Enter a couple of records:
Present
Permanent

2. Create another table to hold the addresses. Fields:
AddressID AutoNumber primary key
ClientID relates to the primary key of your Client table.
AddressTypeID relates to tblAddressType.AddressTypeID
Priority Number 1 = top priority, 2 = second
choice, ...
Street
City
State
Zip

3. Remove all the address fields from your existing client table.

4. On your client form, create a subform for entering addresses. The subform
will automatically show only address for the client in the main form. Use a
combo for the AddressTypeID. Set Priority 1 for the preferred address.

5. Create a new query. Switch to SQL View (View menu). Paste this:
SELECT TOP 1 Street, City, State, Zip
FROM tblAddress
ORDER BY Priority, AddressID;
Save the query.

6. Presumably you already have an Access report as the "letter" you wish to
send. Create a subreport to show the address fields, based on the query
saved at step 5. The subreport acts as the address panel just below the name
in your report.

With this kind of structure, you can:
- have as many addresses as you want for any client;
- decide to add other types of address in the future (e.g. work, postal);
- set priorities for each kind of address easily;
- create other queries that show preference for a particular type of address
and so on.

A subquery might also be useful for selecting the preferred address. If
subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

Sharron,
You need to create a new query. Include the name fields in the QBE grid in
the bottom half of the window.
Start a new column in the same QBE grid as follows:
Address: IIF([Permanent Address],[Permstreet],[Presentstreet])
create a similar one for city, state, and zip.
In the query you will have one address, one city, one state, and one zip
field to use in the Mail Merge.
This works as [Permanent Address] is a Yes/No field, so the IIF test for YES
and uses PERMSTREET, if NO, it use PRESENTSTREET.

 

[Guest]

This was fantastic. I really, really appreciate your help on this problem.

Sharron,
You need to create a new query. Include the name fields in the QBE grid in
the bottom half of the window.
Start a new column in the same QBE grid as follows:
Address: IIF([Permanent Address],[Permstreet],[Presentstreet])
create a similar one for city, state, and zip.
In the query you will have one address, one city, one state, and one zip
field to use in the Mail Merge.
This works as [Permanent Address] is a Yes/No field, so the IIF test for YES
and uses PERMSTREET, if NO, it use PRESENTSTREET.
--
Hopefully helpful,

I have a table with Permanent Address(yes/no) with
Permstreet,permcity,permstate,permzip; then in same table Present
Address(yes/no)with Presentstreet,prescity,prestate,preszip.

How do I setup a query that will pull the permanent address if the Permanent
Address is Yes OR the Present Address if the Present Address is Yes. I need
to do this to send a letter to the address that should be used for mailing.

 

[Guest]

Dick D solution was much easier and faster to complete and get my user on the
road to success, but you bring up some wonderful points and extra tips
regarding subqueries that I will research and test out on some of my
databases. I thank you for your quick response and detailed solution.