connecting two sql statements

[patrichek]

Hi,
in a previous post I was trying to create an update query from an unmatched query but was getting an ambiguous join error. now i'm trying to create the join before my update to show my unique identifier (contactID) in my query results.

I need help connecting these two querys:

1. creates the join

SELECT UpdateCallCenterSX2QRY.ContactID
FROM [Patient Status Table] INNER JOIN UpdateCallCenterSX2QRY ON [Patient Status Table].ContactID = UpdateCallCenterSX2QRY.ContactID;

2. Performs the unmatched 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));

Its probably simple, but i'm not very good with sql code.

thanks in advance!

 

[nicsin]

do you mean something like this?

SELECT UpdateCallCenterSX2QRY.ContactID,
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) INNER JOIN
[Patient Status Table]  ON [Patient Status Table].ContactID = UpdateCallCenterSX2QRY.ContactID
WHERE UpdateCallCenterSX2QRY.PostalCode Is Null
GROUP BY UpdateCallCenterSX1QRY.PatientAccountNumber, UpdateCallCenterSX1QRY.PatientLastName, UpdateCallCenterSX1QRY.PatientFirstName, UpdateCallCenterSX1QRY.PatientCity, UpdateCallCenterSX1QRY.PatientState, UpdateCallCenterSX1QRY.PatientZipCode, UpdateCallCenterSX1QRY.[Appointment Date], UpdateCallCenterSX2QRY.PostalCode

N

 

[patrichek]

Hi,
I received "join not supported" error. It didn't point to which join.
any other ideas?

thanks

 

[nicsin]

try this

SELECT join1.ContactID,
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) as join1 INNER JOIN
[Patient Status Table]  ON [Patient Status Table].ContactID = join1.ContactID
WHERE UpdateCallCenterSX2QRY.PostalCode Is Null
GROUP BY UpdateCallCenterSX1QRY.PatientAccountNumber, UpdateCallCenterSX1QRY.PatientLastName, UpdateCallCenterSX1QRY.PatientFirstName, UpdateCallCenterSX1QRY.PatientCity, UpdateCallCenterSX1QRY.PatientState, UpdateCallCenterSX1QRY.PatientZipCode, UpdateCallCenterSX1QRY.[Appointment Date], UpdateCallCenterSX2QRY.PostalCode

N

 

[patrichek]

Hi,
In your code I'm receiving "FROM clause error" and it highlights "as"

I'm playing with it but not having much luck....

 

[nicsin]

SELECT UpdateCallCenterSX2QRY.ContactID,
UpdateCallCenterSX1QRY.PatientAccountNumber, UpdateCallCenterSX1QRY.PatientLastName, UpdateCallCenterSX1QRY.PatientFirstName, UpdateCallCenterSX1QRY.PatientCity, UpdateCallCenterSX1QRY.PatientState, UpdateCallCenterSX1QRY.PatientZipCode, UpdateCallCenterSX1QRY.[Appointment Date]
FROM [Patient Status Table] INNER JOIN (UpdateCallCenterSX1QRY LEFT JOIN UpdateCallCenterSX2QRY ON UpdateCallCenterSX1QRY.PatientZipCode = UpdateCallCenterSX2QRY.PostalCode) 
  ON [Patient Status Table].ContactID = UpdateCallCenterSX2QRY.ContactID
WHERE UpdateCallCenterSX2QRY.PostalCode Is Null
GROUP BY UpdateCallCenterSX1QRY.PatientAccountNumber, UpdateCallCenterSX1QRY.PatientLastName, UpdateCallCenterSX1QRY.PatientFirstName, UpdateCallCenterSX1QRY.PatientCity, UpdateCallCenterSX1QRY.PatientState, UpdateCallCenterSX1QRY.PatientZipCode, UpdateCallCenterSX1QRY.[Appointment Date], UpdateCallCenterSX2QRY.PostalCode

 

[patrichek]

still no luck, same error but not showing on "as" now.

Maybe i should try a different approach...

 

[patrichek]

i ended up matching fields from my contacts table and the unmatched query to return the desired results....at least i think its right.

UPDATE (UpdateCallCenterSX1QRY INNER JOIN Contacts ON (UpdateCallCenterSX1QRY.PatientLastName = Contacts.LastName) AND (UpdateCallCenterSX1QRY.PatientFirstName = Contacts.FirstName) AND (UpdateCallCenterSX1QRY.PatientCity = Contacts.City)) INNER JOIN [Patient Status Table] ON Contacts.ContactID = [Patient Status Table].ContactID SET [Patient Status Table].Status = "Performed"
WHERE ((([Patient Status Table].Status)="scheduled") AND (([Patient Status Table].Type)="surgery"));

thanks to everyone