Ok Access gurus,
I am using Windows 7, MS Access 2010, 32 bit and I have a database that has 9 entities. This database is used to help manage New Products Introduction. There is a 3rd party PLM product that we use to manager our Product life cycle, Bills of Materials management etc. This PLM app generates a request with a request number such as this MER BRF-000979 or MER BOF-000364. I use these numbers in my database to tie information used on the NPI to an assembly and its release to production status.
The Query uses the 3 tables – tblNPIReleaseRequest, tblAssembly, and tblNPI. All are linked thru foreign key of Assembly_ID. However, in the tblNpiRequest, I also have foreign key pointing to the table tblNPI that is the NPI_ID for that table.

So when I release an NPI I create a record in the tblNpiRelease table and enter the request number such as NPI>Prod-000621 (Text field), I also enter the Assembly_ID from the tblAssembly table. I then enter the NPI_ID number from the tblNPI table; This number usually ties the assembly being released to the NPI that was ran to qualify it. However, occasionally we run an NPI where the product revision is at say Rev B and it was not release for one reason or another. Then several months down the road, they get an ECO to rev the board and then they decide to release it. Now, Here is where I am not sure how to handle. Because keep records in the Assembly table by revision, I cannot use the NPI_ID or Assembly for the release. I then go to the Assembly table and create a new record that is the Assembly number and the new revision. When I create an NPI release record in the tblNpiRelease, I then am able to enter the new Assembly_ID to tie to that but leave the NPI_ID field blank. This creates an issue I cannot seem to resolve. I have a query to return the records in NPI release, but this one record will not show because it is not tied to an NPI_ID. Have been trying to figure this out, but cannot seem to. I need help please.
Here is my SQL Syntax for the Query.



Code:
SELECT tblNPI.BRF_BOF_Number, tblNpiReleaseRequests.RequestNumber, tblAssembly.AssemblyNumber, tblNpiReleaseRequests.ProdSignoff, tblNpiReleaseRequests.TestEngSignoff, tblNpiReleaseRequests.ProcEngSignoff, tblNpiReleaseRequests.QASignoff
FROM (tblNpiReleaseRequests LEFT JOIN tblAssembly ON tblNpiReleaseRequests.Assembly_ID = tblAssembly.Assembly_ID) LEFT JOIN tblNPI ON tblNpiReleaseRequests.Assembly_ID = tblNPI.Assembly_ID
WHERE (((tblNpiReleaseRequests.ReqCompleted) Is Null) AND ((tblNPI.ReleasingProduct)="YES"));