Solved Multiple WHERE condition to filter a query (1 Viewer)

[KitaYama]

I'm trying to write a function for a search form where users select several items from combo boxes and then they select if they want the date for specified item be null or not.
If I have the correct structure of a query for the two examples at the bottom of this request, I can write the function. For now, I only need the structure of the filter of the query.

This is the structure of the tables:

and this is a part of tblProductionStage:

Users can select several stageFK (Number) and if they want them to be null or not.

Two Examples:

(StageFK=1 AND StageDate IS NOT NULL) AND (StageFK=2 AND StageDate IS NOT NULL) AND (StageFK=20 AND StageDate IS NULL)
In above image the record surrounded by blue square should be found. The red one won't be included in result.

another example:
(StageFK=1 AND StageDate IS NOT NULL) AND (StageFK=3 AND StageDate IS NULL) AND (StageFK=2 AND StageDate IS NOT NULL)
Both red and blue records won't be included in result. The red one has no squareFK=3 and the blue one's StageDate for stageFK=3 is not null.

Thank you.

 

[arnelgp]

1.
SELECT tblProductionStage.*
FROM tblProductionStage
WHERE
(StageFK In (1, 2) And (Not ([Stage Date] Is Null))) OR
(StageFK In (20) And ([Stage Date] Is Null))

 

[KitaYama]

@arnelgp
Your first where clause shows both records blue and red one.

I didn't test the second one.

Edit:
I tested the second where.
It also shows both records.


Thanks for trying to help.

Edit 2 :
I can't understand why you have an OR in where clause. Shouldn't all be AND?

 

[arnelgp]

you add OrderFK in the condition.
and i think it's about that field more than anything.

 

[KitaYama]

you add OrderFK in the condition.
and i think it's about that field more than anything.

In case of first example, I removed OrderFK per your suggestion.
Now both records are hidden.

I need OrderFK though.

 

[plog]

So where/how will these results be displayed? The best way to achieve what you want is with DoCmd.OpenReport:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

It's the VBA function to open a report which also allows you to pass a filter argument so you can open the report to just the records meeting criteria.

This is how you would do it--strip out all the criteria from your query and make your report on that criterialess query. Then on your form you put a button to open your form. When clicked the code looks at your form for any submitted criteria, compiles a filter string and passes that string to the report when you open it.

 

[KitaYama]

So where/how will these results be displayed? The best way to achieve what you want is with DoCmd.OpenReport:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

It's the VBA function to open a report which also allows you to pass a filter argument so you can open the report to just the records meeting criteria.

This is how you would do it--strip out all the criteria from your query and make your report on that criterialess query. Then on your form you put a button to open your form. When clicked the code looks at your form for any submitted criteria, compiles a filter string and passes that string to the report when you open it.

@plog
I know how to show the result in a form or query.
My problem is I can not build the query to use it as the record source of a form or a report.

Can you help with the structure of the query?
I offered two example. How the sql of a query to show that result is?

thank you.

 

[cheekybuddha]

@KitaYama, can you clarify your original post:

Users can select several stageFK (Number) and if they want them to be null or not.

Two Examples:

(StageFK=1 AND StageDate IS NOT NULL) AND (StageFK=2 AND StageDate IS NOT NULL) AND (StageFK=20 AND StageDate IS NULL)
In above image the record surrounded by blue square should be found. The red one won't be included in result.

You say all the records in the blue square should be returned.

Why do you expect the following records with StagePK:
1053042
1053043
1053049
...
1053057

I would have thought all those records would have been excluded by the criteria you mention.

???

 

[KitaYama]

@cheekybuddha
Records from 1053042 to 1053057 (blue rectangle) all belong to one order. OrderFK=158081

I'm searching for an order that the date of its StageFK 1 is not null, the date of its StageFK 2 is not null and the date of its StageFK 20 is null.
OrderFK 158081 has the above condition. I need the query show the OrderFK 158081. All of those or only one of them.

The red rectable (orderFK=158080) won't be in the result because the third condition doesn't fit (the date of its StageFK 20 is not null)

thanks for stepping in.

 

[plog]

You never mentioned OrderFK in your initial post, but from reading through this it seems to be a big part of the criteria. It seems you want all records from an OrderFK if any of its records meets some criteria. Like if it had 100 records but only 1 met the criteria you still wanted to see all 100. Right?

Using your first example, you wanted all records from OrderFK=158081 even though some of them didn't meet the criteria. But since you had at least 1 record for OrderFK=158081 that did meet criteria you wanted to see all of them.

If so, you need a subquery. That subquery is where you apply your criteria to find just the OrderFKs that meet the criteria. Then you take that subquery and JOIN it back to your main data source and show the full records of just the OrderFKs you want.

If, I'm wrong about what you want. I suggest you demonstrate what you want with just data. 2 sets:

A. STarting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results based on A. Show us what data you expect to end up with after you run your query and feed in the data from A.

 

[KitaYama]

You never mentioned OrderFK in your initial post, but from reading through this it seems to be a big part of the criteria. It seems you want all records from an OrderFK if any of its records meets some criteria. Like if it had 100 records but only 1 met the criteria you still wanted to see all 100. Right?

Sorry for the confusion. I have a language problem here.
I don't care if the query shows all the records or not. I need the query show OrderFK of the records that meets the criteria. Only one of them or all of them is not a concern. I need to know which OrderFK meets all given criteria.

As I explained in #9 I need the query show 158081 and hide 158080 orderFk.

Thank you and sorry again for not being able to explain myself better.

 

[cheekybuddha]

If you just want to see the orders, then try:

SELECT
  o.*
FROM tblOrders o
WHERE o.OrderPK IN (
  SELECT
    ps.OrderFK
  FROM tblProductionStage ps
  WHERE (ps.StageFK = 1 AND ps.StageDate IS NOT NULL)
     OR (ps.StageFK = 2 AND ps.StageDate IS NOT NULL)
     OR (ps.StageFK = 20 AND ps.StageDate IS NULL)
);

If you want to see the stages too, then try:

SELECT
  o.*,
  ps.StageFK,
  ps.StageDate,
  ps.StageUserPK
FROM tblOrders o
INNER JOIN tblProductionStage ps
        ON o.OrderPK = ps.OrderFK
WHERE (ps.StageFK = 1 AND ps.StageDate IS NOT NULL)
   OR (ps.StageFK = 2 AND ps.StageDate IS NOT NULL)
   OR (ps.StageFK = 20 AND ps.StageDate IS NULL)
;

As an aside, your field in tblProductionStage StageUserPK seems to be named inconsistently. Surely it should be StageUserFK as it will be a foreign key in this table (with the PK in the user table)?

 

[cheekybuddha]

@KitaYama - note I made a couple of small edits to the SQL above

 

[KitaYama]

I need to see the orders. No need for the stages.
I'll do some checking and will be back to you.

Thanks for your time.

In case of fields name, the first field is StageUserFK in our database. While copying the data and doing some cleaning, I made a mistake and it has been changed to StageUserPK.

Thanks again.

 

[plog]

I gotcha now. No subquery needed to find those ORderFKs

SELECT OrderFK
FROM tblProductionStage
WHERE ((StageFK=1) AND (IsNull(StageDate)=False))
    OR ((StageFK=2) AND (IsNull(StageDate)=False))
    OR ((StageFK=20) AND (IsNull(StageDate)=True)
GROUP BY OrderFK
HAVING COUNT(OrderFK)=3

 

[cheekybuddha]

@plog

You might need a GROUP BY clause in that.

 

[KitaYama]

If you just want to see the orders, then try:

SELECT
  o.*
FROM tblOrders o
WHERE o.OrderPK IN (
  SELECT
    ps.OrderFK
  FROM tblProductionStage ps
  WHERE (ps.StageFK = 1 AND ps.StageDate IS NOT NULL)
     OR (ps.StageFK = 2 AND ps.StageDate IS NOT NULL)
     OR (ps.StageFK = 20 AND ps.StageDate IS NULL)
);

@cheekybuddha your query shows both 158080 & 158081.

There's a database attached to first post for testing.
Once again I can't understand why OR has been used. While I need all the three condition being met.

 

[cheekybuddha]

There's a database attached to first post for testing.

Will try and look later when I get a moment

 

[KitaYama]

I gotcha now. No subquery needed to find those ORderFKs

SELECT OrderFK
FROM tblProductionStage
WHERE ((StageFK=1) AND (IsNull(StageDate)=False))
    OR ((StageFK=2) AND (IsNull(StageDate)=False))
    OR ((StageFK=20) AND (IsNull(StageDate)=True)
GROUP BY OrderFK
HAVING COUNT(OrderFK)=3

@plog this sql brings up error. It's 1 AM here and I had a very hard day. I'll take a half an hour nap and will be back

I really appreciate the time everyone is spending on this.

 

[cheekybuddha]

I think I see what you're aiming for.

It's actually quite complex I think. You will need to use plog's grouping, but maybe more than once.

Will get back later if no one else provides the answer first