Filtering

[leonardokroeger]

Hello everybody,
I'm a beginner and still trying to understand Access' logic. My VBA skilled are very limited. I'd say close to the zero limit

Today I'm trying to filter a clients list displayed by a query.

This query lists all clients that still have no entry (their ID saved as foreign key) in another table. I use the query e.g. to populate this table only with "unassigned" clients. The purpose of this table is to be able to have multiple entries for one client in the case of, and only if, all other entries of this same client being marked as disabled.

By now I have only this filter condition for the query: For the foreign key field "is null".
If I want to show in addition only those clients, that have exclusively deactivated entries in the second table I cannot use a filter condition (OR condition) for the field "Active" like the "= False" condition.
Because if a new entry is added, it obviously will be active, but its corresponding client will still show up in the query as it still has one (or more) deactivated entries (as it is a OR condition).
At the same time I want to leave those deactivated entries in the table and also leave them deactivated.

Basically I'd need a filter condition for the field "Active" stating "=False for all FKClient" (FKClient as foreign key).

Do you have a solution for me? I'd be so glad! I'm trying to evade this problem by some update or append query scenarios that fit my needs, but the dilemma described above always applies to everything I find.

Thanks a lot!!

 

[GinaWhipp]

I guess my first concern would be that you breaking the rules of database normalization by having the same Client in the Client table multiple times because they became inactive.

To answer your question (I think)... I would have a button on the form that toggles Active/Inactive Clients and that way you could still have access to the deactivated ones but only keep the active ones visiable.

 

[jdraw]

I think you are over-complicating the situation, but perhaps it's just your description that seems complex/convoluted.

You mentioned a second table, but didn't describe it. I think you are dealing with (possibly) 3 tables. However, because you have described your situation in terms of Access and FK, we still don't know what it is you're trying to accomplish. You haven't even hinted to what "Clients are assigned".

Let's consider that there is a list of PotentialTasks. And we will assign Tasks to Clients. A Client can be assigned to 0,1 or more Tasks. A Task is assigned to only 1 Client once assigned. A Task may exist even though it is not yet assigned.

Client PK ClientID
ClientAssignment PK ( ClientID + TaskID) This is a Junction Table
Task PK TaskID

To find out which Task(s) are not assigned, use something like

Select *
From ClientAssignment RIGHT JOIN Tasks ON
ClientAssignment.TaskID = TaskID
where ClientAssignment.TaskID Is NULL

I think your data structure (tables and relationships) may be the heart of your issue. But then again, I could have misinterpreted your post.

 

[leonardokroeger]

Okay,
I found a solution, maybe you could comment on that (for learning puroses )? Seems kind of too complicated....But after all, it works at least!

SELECT DISTINCT tblClients.IDClient
FROM tblClients LEFT JOIN tblProcesses ON tblClients.IDClient = tblProcesses.FKClient
WHERE (((tblProcesses.IDProcess) Is Null)) OR (((tblProcesses.Active)=False) AND ((Exists (SELECT tblProcesses.Active
FROM tblProcesses
WHERE Active=True AND tblProcesses.FKClient=tblClients.IDClient))=False));

 

[GinaWhipp]

Doesn't look complicated at all, especially if it works... just still wondering why you are allowing the same Client to be entered multiple times, unless I am misunderstanding something.

 

[jdraw]

I still believe you have a Many to Many situation. To resolve this you should use a junction Table.

As Gina asks, "Why you are allowing the same Client to be entered multiple times"?