Help with filter (1 Viewer)

pbuethe · Oct 5, 2018

I have a lookup table with a compound primary key: CaseNbr and ReviewLevel. An example of the data in these fields is:

CaseNbr ReviewLevel
1 PRA
1 MRA1
2 PRA
3 PRA
3 PC1
3 PC2
4 PRA
4 PC1
4 MRA1
5 MRA1
5 MRA2
6 PC1

I have a query to select cases with a “PRA” review level with other criteria.
I have a form with a subform which is based on another query. The second query is based on the first query linked by a left join to another table which also has CaseNbr and Review Level in the PK. However, they are linked only on CaseNbr.

I have an option group on my form which I am trying to program to filter the subform/report.
Four of the 5 options select cases with an “MRA1” review level with other criteria and display fields from the MRA1 review level (from the 2nd table) on the subform and on a report. These are working.

The other option needs to find the cases that do not have an MRA1 level.
With the example above, the filter should return cases 2 and 3.
Thanks for your help.

JHB · Oct 6, 2018

You need 2 queries, the first one find all with "MRA1" in it, the second query is an unmatch query which find all where the CaseNbr isn't in the first query.
Remember to change the tablename to fit yours.
1. query:

SELECT CaseNbr, ReviewLevel
FROM Table2
WHERE ReviewLevel="MRA1";

2. query:

SELECT Table2.CaseNbr, Table2.ReviewLevel
FROM Table2 LEFT JOIN Query1 ON Table2.[CaseNbr] = Query1.[CaseNbr]
WHERE Query1.CaseNbr Is Null;

pbuethe · Oct 9, 2018

JHB,

Thanks for your reply. I will try this and let you know how it goes.

pbuethe · Oct 9, 2018

I tried it. The selection looks like it works but the recordsets are not updateable.

The first query (qryMRA1) is:

Code:

SELECT qryDRGReferred.CaseNbr, tlkpReview.ReviewLevel, qryDRGReferred.SampleNbr, qryDRGReferred.ProviderNbr, qryDRGReferred.ProviderName, tlkpReview.Outcome, tlkpReview.ReviewDate, tlkpHIMTracking.PickEntryDate, tlkpHIMTracking.PickEntryStaff, tlkpHIMTracking.LetterDate
FROM (qryDRGReferred INNER JOIN tlkpReview ON qryDRGReferred.CaseNbr = tlkpReview.CaseNbr) LEFT JOIN tlkpHIMTracking ON tlkpReview.CaseNbr = tlkpHIMTracking.CaseNbr
WHERE (((tlkpReview.ReviewLevel)="MRA1"));

The second query (qryMRA1Incomplete) is:

Code:

SELECT tblWkshtHeader.CaseNbr, tlkpReview.ReviewLevel, tblWkshtHeader.SampleNbr, tblWkshtHeader.ProviderNbr, tblWkshtHeader.ProviderName, qryMRA1.Outcome, tlkpHIMTracking.ReviewLevel, tlkpHIMTracking.PickEntryDate, tlkpHIMTracking.PickEntryStaff, tlkpHIMTracking.LetterDate
FROM (tblWkshtHeader INNER JOIN (tlkpReview LEFT JOIN qryMRA1 ON tlkpReview.CaseNbr = qryMRA1.CaseNbr) ON tblWkshtHeader.CaseNbr = tlkpReview.CaseNbr) LEFT JOIN tlkpHIMTracking ON qryMRA1.CaseNbr = tlkpHIMTracking.CaseNbr
WHERE (((tlkpReview.ReviewLevel)="PRA") AND ((qryMRA1.CaseNbr) Is Null))
ORDER BY tblWkshtHeader.CaseNbr;

I need the fields from tlkpHIMTracking to be updateable.

JHB · Oct 10, 2018

pbuethe said:
I tried it. The selection looks like it works but the recordsets are not updateable.
..

No it is not update able, (nothing in your post indicates that it should be update able).
Then you need to think in an alternative solution, like form filters.
Something like below.

Code:

Private Sub Form_Load()
  Dim rst As DAO.Recordset, filterStr As String
  
  Set rst = CurrentDb.OpenRecordset("SELECT CaseNbr " _
  & "FROM Table2 " _
  & "WHERE ReviewLevel='MRA1';")
  If Not rst.EOF Then
    Do
      filterStr = filterStr & "CaseNbr<>" & rst![CaseNbr] & " AND "
      rst.MoveNext
    Loop Until rst.EOF
    Me.Filter = Left(filterStr, Len(filterStr) - 5)
    Me.FilterOn = True
  End If
End Sub

pbuethe · Oct 10, 2018

I believe I have resolved the issue. I created tblHIMTracking which does not contain ReviewLevel. I created an append and an update query to populate this table (run in the Form_Open code of the main form) so that the subform source is now an updateable query using only tblWkshtHeader and tblHIMTracking. I updated the field names in the filter code accordingly. All appears to be working now.

Thanks for your assistance.

Help with filter (1 Viewer)

pbuethe

Returning User

JHB

Have been here a while

pbuethe

Returning User

pbuethe

Returning User

JHB

Have been here a while

pbuethe

Returning User

Similar threads

Users who are viewing this thread