Criteria from other query

[krzysiekk]

Hi i had two querys:

SELECT dbo_2m.order_no, dbo_2m.return_code
FROM dbo_2m
GROUP BY dbo_2m.order_no, dbo_2m.return_code, dbo_2m.warehouse, dbo_2m.date_received
HAVING (((dbo_2m.return_code)="REJECT") AND ((dbo_2m.warehouse)="GI") AND ((dbo_2m.date_received) Between [Forms]![index]![index_sub]![start] And [Forms]![index]![index_sub]![end]));

and second

SELECT dbo_23m.product, dbo_23m.order_no, dbo_23m.warehouse, Max(dbo_23m.local_cost) AS MaxOflocal_cost
FROM dbo_23m
GROUP BY dbo_23m.product, dbo_23m.order_no, dbo_23m.warehouse, dbo_23m.date_received
HAVING (((dbo_23m.warehouse)="GI") AND ((dbo_23m.date_received) Between [Forms]![index]![index_sub]![start] And [Forms]![index]![index_sub]![end]))
ORDER BY dbo_23m.product;

I want exclude from second query rejected orders = put in criteria orders number form first query

please help

 

[llkhoutx]

Make a 3rd query, joining the first two, retrieving records that are not in both of the first 2 queries.

 

[krzysiekk]

Sorry I try but still not working can you show me exmple?

 

[llkhoutx]

See the attached "jpg" file.

Substitute you 1st query name for Table1 (your Query1) and second for Table2 (your Query2). The new query is a left join, even though the tables are related, and results in all Table1 (your Query1) records with no corresponding Table2 (your Query2) record, i.e. all records of Table1 (your Query1) not in Table2 (your Query2).

Note that there is a Query Wizard that does the same and that a query can be queried, just like a table is queried.

 

[krzysiekk]

See the attached "jpg" file.

Substitute you 1st query name for Table1 (your Query1) and second for Table2 (your Query2). The new query is a left join, even though the tables are related, and results in all Table1 (your Query1) records with no corresponding Table2 (your Query2) record, i.e. all records of Table1 (your Query1) not in Table2 (your Query2).

Note that there is a Query Wizard that does the same and that a query can be queried, just like a table is queried.

Why in PK1 Table 2 criteria - Is null??

 

[llkhoutx]

It means that no corresponding entry exists in Table2 (your query2).

 

[krzysiekk]

not working - showing blank screen

 

[llkhoutx]

Your displaying the wrong fields. Of course they don't display, there's no data. You don't understand what the query is doing.

Delete the 2 right columns. Add Kwerenda1.status to e displayed. You want to see Kwerenda1.order_no with no corresponding Kwerenda2.order_no.

I seldom give code because posters just accept it on blind faith. Give a man a fish, he'll eat today. Teach a man to fish . . .