Access 2003 query help please

[Sid Price]

Hello,
I am in need of some advice for a table create query. I have to work with a
database that is imported using XML from an online database. Unfortunately
the online database is a single (flat) table of membership data. I need to
generate a mailing list from that data. The problem is that each record may
contain one of two addresses for the mailing to a member. One is the
organization address they work at the other is an optional mailing address.
If the optional mailing address is present those fields must be used,
otherwise the fields containing the organization address should be used.
I can generate two tables that contain the mailing and organization data
individually using these two queries:

SELECT members.firstname, members.lastname, members.mail_address INTO
MailingList_1
FROM members
WHERE (((members.mail_address)<>""));

SELECT members.firstname, members.lastname, members.org_address INTO
MailingList_2
FROM members
WHERE (((members.mail_address)=""));

However I would really like to have a single query that makes a new table
that has the mailing data.
Thank you for any help,
Sid.

 

[Guest]

Easy!
Just modify your 2 queries to be plain SELECT queries and connect them with
UNION:

SELECT firstname, lastname, mail_address As mailaddr
FROM members
WHERE mail_address<>"";
UNION
SELECT firstname, lastname, org_address As mailaddr
FROM members
WHERE mail_address="";

Then create a make table query reading in the results of the above query:

SELECT * INTO MailingList_1
FROM qryUnionQuery;

- Dorain

 

[Guest]

Sid,

1. Why would you want to have another table, when the data already exists
in your members table. Just use a query.

2. You can use the NZ( ) function to get the appropriate mailing address.
If "mail_address" is null, then the NZ( ) function below will return the
"org_address". Your query should look like:

Select FirstName, LastName, NZ(mail_Address, org_Address) as MailingAddress
From Members

HTH
Dale

 

[Sid Price]

Thank you. This is much closer to what I need. I realize that the new table
has to have a single set of fields for addresses. So, one more step is
required and that is to be able to place the "mailto" address or the
"Org_Address" into the "address" field of the new table. Is it possible to
assign the value of a field in a source table to a named field in the
destination table?
I hope that is clear,
Sid.

 

[Guest]

The query I gave you already does that, note the 'As mailaddr' on each query
in the union. Your new table should have a single column called 'mailaddr'.
Of course, I have not tested any of this, you will need to do that and make
appropriate adjustments yourself.

-Dorian

 

[Sid Price]

Again, thank you; I will give this idea a try.
Sid.

 

[Sid Price]

So as not to provide more information than necessary to achieve the
immediate goal I did not include the full specification of what the project
must do. Part of the requirements have to do with retrieving the database
from a web server and producing some results files. The results are required
to be persisted while the data retrieved from the online database (the
source table) may and probably will change from day to day. That is why I
chose to produce a new table.
Your query looks like I will probably provide the input for my new table. I
will give it a try.
Thanks,
Sid.

 

[Dale Fye]

Glad to help.

Dale

So as not to provide more information than necessary to achieve the
immediate goal I did not include the full specification of what the
project must do. Part of the requirements have to do with retrieving the
database from a web server and producing some results files. The results
are required to be persisted while the data retrieved from the online
database (the source table) may and probably will change from day to day.
That is why I chose to produce a new table.
Your query looks like I will probably provide the input for my new table.
I will give it a try.
Thanks,
Sid.