Subquery syntax

[Blessed]

Any help you can give me on writing this expression will greatly be appreciated.


tblClinic
Clinic
Address
BillingAddress
Phone
Fax
ClinicNPI

tblProvider
FirstName
LastName
SS
1Clinic
2Clinic
3Clinic
4Clinic


In my query I want to know what the ClinicNPI is if Clinic is the same as 1Clinic.

How do I write the expression?

I just tried

SELECT [ClinicNPI] FROM [tblClinic] WHERE [tblClinic]![Clinic] = [tblProvider]![1Clinic]

And it did not work!

The error reads: The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses.

 

[Mihail]

Database Normalization and Table Structures - Microsoft Access / VBA

 

[CJ_London]

with regards syntax use dots(.), not bangs(!) when referring to tables

with regards your query, Why don't you use the query builder and then view the sql generated to see how it is done.

You have two ways of doing this but it depends on what you are trying to do as to which is the better one

These are your two options:

SELECT [ClinicNPI] 
FROM [tblClinic] INNER JOIN [tblProvider] ON [tblClinic].[Clinic] = [tblProvider].[1Clinic]

and since you mention subqueries in your thread header

SELECT [ClinicNPI] 
FROM [tblClinic] 
WHERE EXISTS(SELECT * FROM  [tblProvider] as T WHERE [1Clinic]=[tblClinic].[Clinic])

Note I agree with Paul - it looks like you need to read up on and apply database normalisation - specifically in relation to your clinic fields in tblprovider

 

[pr2-eugin]

Note I agree with Paul - it looks like you need to read up on and apply database normalisation - specifically in relation to your clinic fields in tblprovider

You mean Mihail ?

 

[CJ_London]

You mean Mihail ?

Oops! Sorry Mihail

 

[Blessed]

Thank you so much for your reply. I will read up tonight on normalization and see if that works. Again, THANK YOU.

 

[Mihail]

Oops! Sorry Mihail

No problem.
I know some very skilled Pauls here. So, I interpret your mistake as something very nice for me Thank you !