Selecting a Record from a linked table

[HillTJ]

Hi,

I am writing a module for a manufacturing database. See summarised structure;

TBLRawMaterial
MaterialGradeID - PK
Supplier - lookup from TBLSupplier & filtered to show only "Material" suppliers.

TBLSupplier
SupplierID -PK (linked to Supplier in TBLRawMaterial)

TBLAssessment (my ambition is to have a subform that allows multiple supplier assessments each @ a different date)
AssessmentID-PK
AssessmentDate
ApprovalStatus (to contain whether the supplier is approved or not)
SupplierID-FK (ie a 1 to many relationship)
ApprovalID - PK (lookup linked to ApprovalID)

TBLApprovedSupplierStatus
ApprovalID-PK

I need to be able to select an approved raw material supplier to purchase raw materials. I need to assess the supplier periodically & record both date & assessment status (either approved not). Problem is that I need to look at the latest date & refer to that status.After all a supplier may not always be approved.

So I need to be able to select a "Raw Material" supplier who's latest assessment is "approved".

Maybe my structure is wrong or i've overthought it. I hope I've explained my dilemma sufficiently & would appreciate some help.

Thanks in advance.

 

[MajP]

You can build this query first and either join on it or use it as a subquery

qryMaxAssessment (returns supplierid where last assessment was Approved)

SELECT tblassessment.supplierid_fk, 
       tblassessment.assessmentdate, 
       tblassessment.approvalstatus 
FROM   tblassessment 
WHERE  ( ( ( tblassessment.assessmentdate ) IN (SELECT Max([assessmentdate]) 
                                                FROM   tblassessment 
                                                GROUP  BY supplierid_fk) ) 
         AND ( ( tblassessment.approvalstatus ) = true ) );

 

[HillTJ]

Hi, thanks. I suspected I would need such a query. Will give it a go. Thanks for the heads up. Will let you know how it works.

 

[HillTJ]

Hi, i currently run a query that selects suppliers with a "SupplierType"=2 (this selects only raw material suppliers). How do i incorporate this into the above listed select query?. Appreciate any help as always.

 

[HillTJ]

Hi,

For the record, I did manage to achieve my result with a combination of Access Query builder & assistance from MajP.

Any comments or recommendations would be greatfully received. I'm stoked!

SELECT TBLAssessment.supplierID, TBLSupplier.Supplier_Name, TBLSupplier.SupplierType, TBLAssessment.Assessment_date, TBLAssessment.Approval_Status
FROM TBLSupplier INNER JOIN TBLAssessment ON TBLSupplier.Supplier_ID = TBLAssessment.SupplierID
WHERE (((TBLSupplier.SupplierType)=2) AND ((TBLAssessment.Assessment_date) In (select max([Assessment_Date]) from TBLAssessment GROUP by SupplierID )) AND ((TBLAssessment.Approval_Status)="approved"));