AnsweredAssumed Answered

How to refer to each record from same field from different tables

Question asked by sapa88 on Mar 8, 2012
Latest reply on Apr 17, 2012 by philmodjunk

Title

How to refer to each record from same field from different tables

Post

This is the table structure i have

 

project details table


contract no

contract name 

etc

 

Drawing Register

_fkcontractno

Drawing No

Drawing Title

etc

 

Drawing Issue Sheet

_fkconractno

Drawing No

Drawing Title

Drawing Issued Date

 

Programme Report

_fkContract No

Drawing No

Due to Commenced On

 

Drawing Status Report

_fkcontractno

Drawing No

Drawing Title

Drawing Issued Date

Due to Commenced On

Issued Status

Returned Status

 

The scenario is. first of all a "contract no" in project details table is created. and all the other tables are linked using contract no as the foreign key. and the drawing register is used to create details of a drawing for a particular contract.

ie : contract no "1" can have a drawing no "55" and that drawing no's title can be "aaa"

     contract no "1" can have a drawing no "56" and that drawing no's title can be "bbb"

      and there can be many contracts which includes many drawing like this.

 

once a drawing is created its been checked whether it should be issued or not. if its been issued then the details of that particular contract no,drawing no, drawing title are been entered to the drawing issue sheet. and all these 3 fields can have many records.

 

what i need to find out is a way to issue drawing no "5" in contract no "2" with its exact drawing title. for an example.

if i refer to the example above.

when i enter Contract no "1" in the drawing issue table it should validate and show me that there is such a contract no in the project details table and if not it should show a error message. and after that when i enter the drawing no it should check with the drawing resgister table if there is such drawing no under the contract no i specified. and if there is no such drawing no then it should give an error message but if there is such drawing no then it should auto-enter the drawing title of that particular drawing no.

 

likewise this validation happends to all other tables i have mentioned above when these three fields comes in.

please give me an idea how to do this. i tried every possible way but something goes wrong and doesnt give this output which im expecting.

 

thanks a ton.

 

Outcomes