Take information from one table and add to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Take information from one table and add to another

Author

Topic

Sweet16
Starting Member

15 Posts

Posted - 2011-08-08 : 16:01:14

I want to combine information from a query and a table into one table without being able to change or overwrite any information in the query.

I have 3 tables Info, Referral Initiate, and Referral Update.

I have a query that joins Info and Referral Initiate. The Info table contains customer demographics and the Referral Initiate table contains information specific to where the customer is admitting and discharging to. Thhe tables are connected through the customer's unique ID. This query works fine (qryReferralEntry)

I want to be able to take the Referral Entry qry and add information to that from the Referral Update table without being able to change any information in the query. This table also contains the customer's uniqe ID.

I have tried multiple options but none seem to do the trick.

Please Help

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-08-08 : 16:10:24

Can you tell us what your table looks like... what fields they have? Also how were you trying?

Sweet16
Starting Member

15 Posts

Posted - 2011-08-08 : 16:20:42

Sure,

I have 3 tables and currently 1 query.

1)Table Patient Info has Last Name, First Name, ID, and DOB fields

2)Table Referral Initiate has a ReferralID (autonumber), an ID field (looked up from the Patient Info table), in addition to Admit Facility, Discharge Facility, Referral Date, and Placement Date Fields.

Referrl Entry Query - combines Last Name, First Name and ID from Patietn Info table with the Admiting Facility, Discharging Facility, Referral DAte, adn Placment date fields from the Referral Initiate table.

3) Table Referral Update contains the Referral ID (looked up from the Referral Initiate table) and fields showing wethere the patient has been placed or denied in addition to two memo fields.

I would like to join the query with the Referral Update table without being able to change any informion from the Referral Entry Query.

I tried an update query and a join as random attemtps but neither returned the information I was looking for.

Thanks for the help!

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-08-08 : 16:25:47

You have qryReferralEntry Query that joins two tables

Seelct columns you need FROM
Third_table a
JOIN (check subquery - use qryReferralEntry) b
ON a.[key col] = b.[key col]

using the keys you should get what you need... use left, right, full join according to your needs.

Sweet16
Starting Member

15 Posts

Posted - 2011-08-08 : 16:29:33

Thanks for the feedback!! I'm new at this, learning as I go, so I'm not quite familiar with all of the references you made. I tried a JOIN query with no luck as one of my options. Perhaps I did not initiate it correctly. Can you walk me through it.

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-08-08 : 16:40:51

Seelct a.col, b.col ---Add all columns u need from 'a' and 'b' below
FROM
ReferralUpdate a
JOIN (Slect referralID, ...
-- this is your QRYReferralEntry) b ---treat qryReferralEntry like a table
ON a.[ReferralID] = b.[ReferralID]

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-08-08 : 16:52:30

OR you can use--if all ids and referralIds are in both parent and child table you should be fine with INNER JOIN else u have to use left or right based on ur need.

SELECT p.id,
p.firstname,
p.lastname,
p.dob,
i.admitfacility,
i.dischargefacility,
i.referraldate,
i.placementdate,
u.fieldname,
u.memo
FROM patient_info p
INNER JOIN referral_intiate i
ON p.id = i.id
INNER JOIN referral_update u
ON u.referralid = i.referralid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources