Suggestions needed for subform query

[Fazered]

Right, you'll have to excus me if this is hard to understand, but i have done my best to try and make this as clear as possible to understand.

I have a form with multiple Tabs. On each tab is a subform which links to a continous form with a query that selects jobs with a specific status from a large 'Jobs' table. (A tab for each status.)

I have another table ('Header' table) which contains multiple records for each Job in the Jobs table. (One to many relationship on 'OrderNumber')

The problem is a single record in the 'Header' table does not contain all of the fields that I need shown on the subform in the tab. Instead, each record in the 'Header' table which relates to a specifc job contains a different selection of fields with data in.
So all of the records for a job in the 'Header' table, when combined, make up all of the fields which I need shown on the subform.

Here is an example of what i mean for the 'Header' table.

In this example, i need the most recent value for the 'EstimatedEndDate'. (This is given by the second column from the left 'NoticeSequ...', the most recent being the largest number)

So, all i need to do is combine the data in the table above to effectively make one record out of 3 so it can be shown on the continous subform. How can this be done in a query?


Thank you in advance if you have any suggestion or answers to the problem.

 

[Fazered]

Was this too confusing for everybody? Still struggling with this so any thoughts would be appreciated like you won't believe.

Thanks

 

[dkinley]

So your requirement, is that you want a query that will return 1 row of data from the Header table with the most recent 'EstimatedEndDate'?

By most recent ... define as the date value that was last inputted or the date value that is closer to today's date.

-dK