Generating an expiry date based on fields in different tables I want a field that gives the date a certificate expires.

I think i would create a query and add a field using something like this..

dateadd("d", [DaysToExpire], [DateCompleted])


My trouble is, [DaysToExpire] is on a different table to [DateCompleted]. how do i resolve that?

Is there a way to get the query field to refer to both tables?

Can I create a lookup field on one of the tables that auto populates once the 2 tables a linked by a record?

I am all pretty new to this so sorry if my language is a bit off. Any help would be much appreciated.







The code generated by access:

SELECT tbl_Courses.CourseName
     , tbl_Courses.CourseDescription
     , tbl_Training.DateCompleted
  FROM tbl_Courses 
 INNER JOIN tbl_Training 
    ON tbl_Courses.CourseID = tbl_Training.CourseID;


Error after entering Mr.Brownstone's code:
You can create a view using a JOIN statement to combine the results of two related tables. In your design, the CourseID field appears on both the tbl_courses and tbl_training tables, using this field to perform the join you can easily derive the value that you require:

CREATE VIEW [CourseDetails] 
(
    [CourseName], 
    [CourseDescription], 
    [DateCompleted], 
    [ExpiryDate]
)
AS
SELECT tbl_Courses.[CourseName]
     , tbl_Courses.[CourseDescription]
     , tbl_Training.[DateCompleted]
     , DATEADD("d", tbl_Training.[DaysToExpire], tbl_Courses.[DateCompleted]) [ExpiryDate]
  FROM tbl_Courses 
 INNER JOIN tbl_Training 
    ON tbl_Courses.[CourseID] = tbl_Training.[CourseID];



There is a full tutorial on how to perform this that you might find helpful.