Hello all,
This is what I am trying to do.
Compare two tables. Find associated records in table 1 that do not match records in table 2 and vice versa. Next be able to further filter those results based on a combo box selection. I am trying to do this all in one query. Data is being pulled from two separate sources, which is why I need to compare using two tables.
What is happening: Nonmatching records are returned by query, but it won't filter by combo box.
The results are being displayed on a multiple item form. Combo box selection is on the same form. After combo box selection I have tried both openquery and requery with no results.
Here is the SQL. I tried to make it a little easier to read. Thanks.
SELECT tblMasterList.Rank, tblMasterList.Last, tblMasterList.First, tblMasterList.Section, tblMasterList.DEPARTMENT, tblMasterList.Command, tblMasterList.[YB Date], tblCPIMS.[CPIMS YB], tblMasterList.[LCM Date], tblCPIMS.[CPIMS LCM], tblMasterList.[GB Date], tblCPIMS.[CPIMS GB], tblMasterList.[GB Cert Date], tblCPIMS.[CPIMS GB Cert], tblMasterList.[BB Date], tblCPIMS.[CPIMS BB], tblMasterList.[BB Cert Date], tblCPIMS.[CPIMS BB Cert]
FROM tblMasterList LEFT JOIN tblCPIMS ON (tblMasterList.First = tblCPIMS.First) AND (tblMasterList.Last = tblCPIMS.Last)
WHERE (((tblMasterList.Command)=Forms!frmDBCPIMSDataErr!cmbCommandSelect) And ((tblMasterList.[YB Date]) Is Null) And ((tblCPIMS.[CPIMS YB]) Is Not Null)) Or (((tblMasterList.[YB Date]) Is Not Null) And ((tblCPIMS.[CPIMS YB]) Is Null))
Or (((tblMasterList.[LCM Date]) Is Null) And ((tblCPIMS.[CPIMS LCM]) Is Not Null)) Or (((tblMasterList.[LCM Date]) Is Not Null) And ((tblCPIMS.[CPIMS LCM]) Is Null))
Or (((tblMasterList.[GB Date]) Is Null) And ((tblCPIMS.[CPIMS GB]) Is Not Null)) Or (((tblMasterList.[GB Date]) Is Not Null) And ((tblCPIMS.[CPIMS GB]) Is Null))
Or (((tblMasterList.[GB Cert Date]) Is Null) And ((tblCPIMS.[CPIMS GB Cert]) Is Not Null)) Or (((tblMasterList.[GB Cert Date]) Is Not Null) And ((tblCPIMS.[CPIMS GB Cert]) Is Null))
Or (((tblMasterList.[BB Date]) Is Null) And ((tblCPIMS.[CPIMS BB]) Is Not Null)) Or (((tblMasterList.[BB Date]) Is Not Null) And ((tblCPIMS.[CPIMS BB]) Is Null))
Or (((tblMasterList.[BB Cert Date]) Is Null) And ((tblCPIMS.[CPIMS BB Cert]) Is Not Null)) Or (((tblMasterList.[BB Cert Date]) Is Not Null) And ((tblCPIMS.[CPIMS BB Cert]) Is Null));
Thanks for any help.
This is what I am trying to do.
Compare two tables. Find associated records in table 1 that do not match records in table 2 and vice versa. Next be able to further filter those results based on a combo box selection. I am trying to do this all in one query. Data is being pulled from two separate sources, which is why I need to compare using two tables.
What is happening: Nonmatching records are returned by query, but it won't filter by combo box.
The results are being displayed on a multiple item form. Combo box selection is on the same form. After combo box selection I have tried both openquery and requery with no results.
Here is the SQL. I tried to make it a little easier to read. Thanks.
SELECT tblMasterList.Rank, tblMasterList.Last, tblMasterList.First, tblMasterList.Section, tblMasterList.DEPARTMENT, tblMasterList.Command, tblMasterList.[YB Date], tblCPIMS.[CPIMS YB], tblMasterList.[LCM Date], tblCPIMS.[CPIMS LCM], tblMasterList.[GB Date], tblCPIMS.[CPIMS GB], tblMasterList.[GB Cert Date], tblCPIMS.[CPIMS GB Cert], tblMasterList.[BB Date], tblCPIMS.[CPIMS BB], tblMasterList.[BB Cert Date], tblCPIMS.[CPIMS BB Cert]
FROM tblMasterList LEFT JOIN tblCPIMS ON (tblMasterList.First = tblCPIMS.First) AND (tblMasterList.Last = tblCPIMS.Last)
WHERE (((tblMasterList.Command)=Forms!frmDBCPIMSDataErr!cmbCommandSelect) And ((tblMasterList.[YB Date]) Is Null) And ((tblCPIMS.[CPIMS YB]) Is Not Null)) Or (((tblMasterList.[YB Date]) Is Not Null) And ((tblCPIMS.[CPIMS YB]) Is Null))
Or (((tblMasterList.[LCM Date]) Is Null) And ((tblCPIMS.[CPIMS LCM]) Is Not Null)) Or (((tblMasterList.[LCM Date]) Is Not Null) And ((tblCPIMS.[CPIMS LCM]) Is Null))
Or (((tblMasterList.[GB Date]) Is Null) And ((tblCPIMS.[CPIMS GB]) Is Not Null)) Or (((tblMasterList.[GB Date]) Is Not Null) And ((tblCPIMS.[CPIMS GB]) Is Null))
Or (((tblMasterList.[GB Cert Date]) Is Null) And ((tblCPIMS.[CPIMS GB Cert]) Is Not Null)) Or (((tblMasterList.[GB Cert Date]) Is Not Null) And ((tblCPIMS.[CPIMS GB Cert]) Is Null))
Or (((tblMasterList.[BB Date]) Is Null) And ((tblCPIMS.[CPIMS BB]) Is Not Null)) Or (((tblMasterList.[BB Date]) Is Not Null) And ((tblCPIMS.[CPIMS BB]) Is Null))
Or (((tblMasterList.[BB Cert Date]) Is Null) And ((tblCPIMS.[CPIMS BB Cert]) Is Not Null)) Or (((tblMasterList.[BB Cert Date]) Is Not Null) And ((tblCPIMS.[CPIMS BB Cert]) Is Null));
Thanks for any help.