Creating Query Within Query

[johnwayneoftheseas]

Hi!

I know I'm simply not having any luck writing the SQL statement to produce the results I'm looking for and I was hoping you could help!

I have a query in which I am pulling up IndividualID, IndividualName, StaffID (which is actually the Individual ID, but is a foreign key in a separate table), TypeofContact and DateofContact.

What I want is for the StaffID to show me the names of staff, not their ID numbers. I wrote a query to do this, but can't quite figure out how to insert it into the larger query.

In the end, I want to know what individual was met by which staff member (by name, not ID number), what type of contact was had and when the contact was made.

Could anyone help with the SQL, please? It's definitely not my strong suit and I'm not sure whether to start with the table (inserting StaffID from its respective table) and zoom and insert the SQL to point to the other query (wherein I'm looking up the names based on the StaffIDs).

Thanks for your help!

 

[pbaldy]

I doubt you want another query, but rather a join in your existing query to the table containing the names. It would be far more efficient than a subquery. In SQL view it would look like

SELECT tblReservations.ResNum, TblCustomers.CustomerName
FROM tblReservations INNER JOIN TblCustomers ON tblReservations.AcctID = TblCustomers.AcctID

 

[johnwayneoftheseas]

Thank you. I'm still not having any luck, as it is telling me I have ambiguous joins OR it will only bring up the names and IDs of the Staff and not of the Individuals.

I am willing to bet that the issue is that both tables are joined on the profile ID, but I'm afraid I've overthought all of this so much I can't figure out my next best step.

 

[pbaldy]

Can you post the db, or a sample of it?