SQL joining


the structures of the Access2000 tables as follows.

tblIN     tblOut    tblProf
CardNo    CardNo    ID
...       Picked    Name

I wanto select all records from the two tables for the following criteria.

lblIN.CardNo = tblOut.CardNo
tblOut.Picked= tblProf.ID

can anyone pls. help me with sql statements?

Who is Participating?
Koala119Connect With a Mentor Commented:
I don't think there is any syntax error in this query.
Are you running the query in MS Access directly? There should not have any problem if you run it here, you can test it.
Or running as part of the codes  in vb .net? If yes, could you post your codes? I guess the problem might be here.

You may try this as well:
SELECT TblProf.Name, TblIn.*, TblOut.*
FROM TblProf, TblOut, TblIn
WHERE TblProf.ID = TblOut.Pick AND TblIn.CardNo = TblOut.CardNo
      INNER JOIN [tblProf] ON [tblProf].[ID] = [tblOut].[Picked]
      INNER JOIN [tblIN] ON [tblIN].[CardNo] = [tblOut].[CardNo]
ayha1999Author Commented:

None of the answers worked. I think my question was not clear.
I will explain:
Suppose I have the following data in tblProf table;

ID    Name
1     ABC

Pick are related to ID field of Prof.
I want to retrive all records from tblIn and tbnOut and ...

suppose Pick=1 then I want retrieve Names from Prof table for ID equal to 1.

ID  Name
1   Abc

I don't want to use WHERE in query because sometimes my query will look like:

select * from tblIn,tblOut,tblProf Where CardNo = ' txtCardNo ' Or
select * from tblIn,tblOut,tblProf Where Pick= ' txtPick 'etc.

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

If you want to get all the tblProf.Name, tblIn.*, tblOut.*, then try this:

SELECT tblProf.Name, tblIn.*, tblOut.*
FROM tblProf INNER JOIN tblOut ON tblProf.ID = tblOut.Picked
         INNER JOIN tblIN ON lblIN.CardNo = tblOut.CardNo

If you want to get a particular record, you have to add Where clause.
ayha1999Author Commented:
Hi Koala119,

I tried ur query but I get the error that...
Syntax error, missing operator then query...

SELECT TblProf.Name, TblIn.*, TblOut.* FROM TblProf INNER JOIN TblOut ON TblProf.ID = TblOut.Pick INNER JOIN TblIn ON TblIn.CardNo = TblOut.CardNo

could u pls. check what's wrong?

thanks in advance.

One thing about the error, it may be because of the datatype different. Could you check if they have the same datatype in MS Access e.g.Number, Text
     1. TblProf.ID = TblOut.Pick
     2. TblIn.CardNo = TblOut.CardNo
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.