By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Two tables and a query

P: n/a
Okay, long story semi-short. Two tables for two different agencies and
two different sets of clients. All the fields are identical. Two
programs (diff funding streams) have different clients from both
agencies, so I'm trying to create a query that provides all active
clients/program. I managed to create a union query that did exactly
what I wanted, until I realized the data couldn't be changed in the
query (bear with me, never had a computer class). Is it possible to
create a select query or other query which allows the data to be
changed? The union query is below:

SELECT [Client Code],[Activity],[CM],[First Name],[Last
Name],[Address],[City],[State],[ZIP],[Phone Number],[Receive
Calls],[Receive
Mail],[DOB],[Age],[Race],[Hispanic],[Sex],[Status],[Doctor],[Title
XIX],[Medicaid #],[Income],[Household Size],[% of FPL],[Within
City],[Neighborhood],[PCI],[Date Closed],[Route]
FROM [SAN Clients]
WHERE [Meals]=Yes
UNION
SELECT [Client Code],[Activity],[CM],[First Name],[Last
Name],[Address],[City],[State],[ZIP],[Phone Number],[Receive
Calls],[Receive
Mail],[DOB],[Age],[Race],[Hispanic],[Sex],[Status],[Doctor],[Title
XIX],[Medicaid #],[Income],[Household Size],[% of FPL],[Within
City],[Neighborhood],[PCI],[Date Closed],[Route]
FROM [SRHD Clients]
WHERE [Meals]=Yes;

Any help is GREATLY appreciated, and thank you in advance. :)

-Cami

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I would create another field.. AGENCY to store a code (IDNumber) that
identifies the agency that this person is through....then just use a normal
select query for all other purposes.

Create a new table...all fields, plus the Agency ID.
Use an APPEND query to add all the records from [SAN Clients] and set the
AgencyID to whatever criteria you want it to be.
the APPEND all the records from [SRHD Clients] and set their AgencyID as
well.

If this is something that will be repeated becuase you don't 'own' the
original tables...than make sure that there is some kind of PK for each
person.....then next time you try to APPEND them the records that are
already there will fail (due to Key violations) and any new ones will be
added. This is probably an ugly way to do it (the deliberate violations) but
it works for me.

HTH
Mal.
"Cami Huysman" <ca***@san-nw.org> wrote in message
news:40*********************@news.frii.net...
Okay, long story semi-short. Two tables for two different agencies and
two different sets of clients. All the fields are identical. Two
programs (diff funding streams) have different clients from both
agencies, so I'm trying to create a query that provides all active
clients/program. I managed to create a union query that did exactly
what I wanted, until I realized the data couldn't be changed in the
query (bear with me, never had a computer class). Is it possible to
create a select query or other query which allows the data to be
changed? The union query is below:

SELECT [Client Code],[Activity],[CM],[First Name],[Last
Name],[Address],[City],[State],[ZIP],[Phone Number],[Receive
Calls],[Receive
Mail],[DOB],[Age],[Race],[Hispanic],[Sex],[Status],[Doctor],[Title
XIX],[Medicaid #],[Income],[Household Size],[% of FPL],[Within
City],[Neighborhood],[PCI],[Date Closed],[Route]
FROM [SAN Clients]
WHERE [Meals]=Yes
UNION
SELECT [Client Code],[Activity],[CM],[First Name],[Last
Name],[Address],[City],[State],[ZIP],[Phone Number],[Receive
Calls],[Receive
Mail],[DOB],[Age],[Race],[Hispanic],[Sex],[Status],[Doctor],[Title
XIX],[Medicaid #],[Income],[Household Size],[% of FPL],[Within
City],[Neighborhood],[PCI],[Date Closed],[Route]
FROM [SRHD Clients]
WHERE [Meals]=Yes;

Any help is GREATLY appreciated, and thank you in advance. :)

-Cami

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.