Joining tables

[peekay]

I have a table named Jobs which has a JobNumber and a Closed field. I also have another table named JobItems which contains the job items for the jobs in the first table. This table also has JobNo and Done fields.
I need to find out which job items in the JobItems table has their field done=false for which their jobno in the Jobs table has the field closed=false.
I read that I have to join the tables and have come as far as the following SQL statement :
"Select * from JobItems,Closed from Jobs INNER JOIN JobItems ON JobItems.JobNo=Jobs.JobNo where JobItems.done=false and Jobs.closed=false"
It does not give an error but neither does it give me any records in the recordset whilst I know there should be records.
Can somebody please help me.



PK Odendaal
pko@mweb.co.za

 

[rafe]

PK,

In database view go to you the "Tools" menu & select "Relationships..."

Now, go to the "View" menu and choose "Show Table..." and select your Jobs table. Repeat for the JobItems table.

Now put your cursor over the Job# in the Jobs table press left mouse & drag to the Job# in the JobItems table.

When the edit relationships window appears click on the “Enforce Referential Integrity Box” and then click the “Create” button and then save. You’ll see a “1” on the Jobs side & a infinity sign on the other side of the line you just made. Now if Access whines about it we’ll have to take a different tact but if the DB is clean in these two fields (or empty) it should be OK.

Now if you go to your Jobs table on the left you should see a “+” button on the left. Click on that to expand the list & the Job Items for the job should appear.

It’s a start.

rafe

 

[peekay]

rafe
Thanks for the help. I however cannot find a database view in my program or a tools/relationships menu. I have however found the correct sql string.

PK Odendaal
pko@mweb.co.za