4 Replies - 1930 Views - Last Post: 03 March 2012 - 11:55 AM

#1 supr4  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 02-March 12

SQL query that updates a table

Posted 03 March 2012 - 07:49 AM

Hello,

I have created a query from two tables (Appointments and Referrals). I need to cause the referrals table to be updated whenever an appointment is made in the 'Appointment' table that has a referral, if it makes sense.

The 'Appointment' table has a column called referral that has a Check box as a display control. So appointments that lead to referrals will be checked. When this happens I want the 'Referral' table to be automatically updated with that appointment.

So far I created a query that has the following SQL code. How should I proceed next?

SELECT Appointments.AppID, Appointments.AppReferral, Referral.RefHosp, Appointments.AppDate
FROM Appointments INNER JOIN Referral ON Appointments.AppID = Referral.AppID
ORDER BY Appointments.AppDate;



Is This A Good Question/Topic? 0
  • +

Replies To: SQL query that updates a table

#2 supr4  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 02-March 12

Re: SQL query that updates a table

Posted 03 March 2012 - 08:09 AM

I don't know if this is a correct code but I tried

 Update Referral
FROM Appointments
WHERE AppReferral = True; 


However when I try to go in design view it says "Syntax error, (missing operator) in query expression 'Appointments.AppDate"
Was This Post Helpful? 0
  • +
  • -

#3 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: SQL query that updates a table

Posted 03 March 2012 - 09:10 AM

the correct syntax for update is
UPDATE Table
Set ColumnName = new_Value,
ColumnName2 = new_Value2 --if you have more columns to update and it goes to n 
where <Filters> --in your case AppReferral = True


Was This Post Helpful? 0
  • +
  • -

#4 supr4  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 02-March 12

Re: SQL query that updates a table

Posted 03 March 2012 - 09:53 AM

thanks for replying

I changed the code to

UPDATE Referral
Set RefID = AppID
WHERE AppReferral = True;


Which I hope is correct?

But the same syntax error problem comes up
Was This Post Helpful? 0
  • +
  • -

#5 supr4  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 02-March 12

Re: SQL query that updates a table

Posted 03 March 2012 - 11:55 AM

Looks like the INSERT INTO statement is what I want.

I'm getting a syntax error with this code though, can anyone check it for me?

INSERT INTO Referral (AppID, AppDate, AppTime)
SELECT AppID, AppDate, AppTime
FROM Appointments
WHERE AppReferral = True;

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1