help creating update query

[patrichek]

Hello,

I am trying to create an unmatched query in which i can take the results and update another table.

Query A

ContactID(primary key)
Lastname
FirstName
City
State
Zip
Status = "scheduled" (Criteria)

Query B

LastName
FirstName
City
State
Zip
Appointment type = "~" (criteria)

Query C

LastName
FirstName
City
State
Zip


The first 5 columns of A and B are how i find my data. When status = "scheduled" and Appointment type= "~" (~ is the symbol we use for surgery performed) then status column in my PatientStatusTBL should be updated to "performed"

Because in Query C there is no Primary Key(ContactID) I'm not sure how to create this update.

Any help is greatly appreciated.

THanks!

 

[traingamer]

Are these all against the same table? What does C have to do with A & B?

Give us a hint what table(s) these queries are using...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[patrichek]

Hi,

I'm working with 2 tables:

1. PatientStatusTBL (access 2k)

2. PatientMaster (linked from SQL backend)

We use Access for our marketing db and a VB program made by a 3rd party for Practice Management db.

here's the workflow, we get a lead in our marketing db, the lead is later scheduled for surgery, the data is transferred to the PM db. The problem is going back and updating the status of the lead to "performed" in the marketing db so we can track leads that went to surgery for ROI in our marketing campaigns. Doing this manually will require another step for our already busy staff.

I am using an unmatched query to return results that show the same last name, first name, address, city, state and zip based on the criteria for patients who had surgery. So far this is working. Now I would like to update the marketing db table to reflect the status of the lead to surgery being "Performed". I then can run another query on the Marketing db that shows the source where we received the lead and how many actually had surgery.
The table needs updated first....

make sense?

 

[patrichek]

i'll try and start over with table names, fields and exactly what i'm try to do, as to make myself more clear:

I need to take records from PatientStatusTBL in marketing db and compare them to records in PatientMaster in our Practice Mgmt db by select criteria. then I want to update PatientStatusTbl's status field to "Performed" on the matching records.

I made an unmatched query that identifies records by name and demographics-this works.

problem:

How to update my PatientStatusTBL

My patientstatustbl and patientmaster have no way of relating,except by lastname, firstname, address, city, state and zip hence the unmatched record query. The unmatched query records do not contain the needed primary key to update the patientstatustbl.

I tried converting the unmatched query table to an update query and then adding my patientstatustbl to the query grid and joining the 2 but i get an ambiguous inner join error.

i'm stuck at this point.

 

[PHV]

It's probably time now to post some SQL code ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[patrichek]

here's what i have for sql code:

UPDATE UpdateCallCenterSX3QRY LEFT JOIN ([Patient Status Table] RIGHT JOIN Contacts ON [Patient Status Table].ContactID = Contacts.ContactID) ON UpdateCallCenterSX3QRY.PatientLastName = Contacts.LastName SET [Patient Status Table].Status = "Performed"
WHERE ((([Patient Status Table].Type)="surgery"));

thanks!

 

[patrichek]

Please disregard the last post, here is the correct code before i change my query to an update query:

SELECT UpdateCallCenterSX1QRY.PatientAccountNumber, UpdateCallCenterSX1QRY.PatientLastName, UpdateCallCenterSX1QRY.PatientFirstName, UpdateCallCenterSX1QRY.PatientCity, UpdateCallCenterSX1QRY.PatientState, UpdateCallCenterSX1QRY.PatientZipCode, UpdateCallCenterSX1QRY.[Appointment Date]
FROM UpdateCallCenterSX1QRY LEFT JOIN UpdateCallCenterSX2QRY ON UpdateCallCenterSX1QRY.PatientZipCode = UpdateCallCenterSX2QRY.PostalCode
GROUP BY UpdateCallCenterSX1QRY.PatientAccountNumber, UpdateCallCenterSX1QRY.PatientLastName, UpdateCallCenterSX1QRY.PatientFirstName, UpdateCallCenterSX1QRY.PatientCity, UpdateCallCenterSX1QRY.PatientState, UpdateCallCenterSX1QRY.PatientZipCode, UpdateCallCenterSX1QRY.[Appointment Date], UpdateCallCenterSX2QRY.PostalCode
HAVING (((UpdateCallCenterSX2QRY.PostalCode) Is Null));

I would like the records from this query to update in my patient status table by changing the status field to equal "Performed"

not sure how to do it.