Criteria on Query based in a list of another Query

  • Thread starter Thread starter ldiaz
  • Start date Start date
L

ldiaz

I have a Query Named:
WODispatchLinkedtoExcel_QueryCurr
and I have many datas on it, and a column named [Planner]

also I have another query named:
Query_ActivePlanners and a column named [Planner_Code]

where it shows Planner Code availables..

What I want is to show in the first query only those datas which the value
is in Query_ActivePlanners

I have this criteria..

WHERE ((lbl_PlannerCodes.Planner_Code) Is Not Null)); but it shows all datas.


please help

Thanks
 
In WODispatchLinkedtoExcel_QueryCurr, use the Add Table dialog (toolbar) to
add Query_ActivePlanners as another input "table."

In the upper pane of query design, drag
WODispatchLinkedtoExcel_QueryCurr.Planner
and drop it onto:
Query_ActivePlanners.Planner_Code

Acess will create a line between the 2 "tables."
It default to an inner join, so it will show matches only.
 
Hi Alen, this is the statement and it does not work.

could you take a look at it and let me know?
Thanks
=============================================
SELECT WODispatchLinkedtoExcel.[Part ID], WODispatchLinkedtoExcel.Desc,
WODispatchLinkedtoExcel.Planner, WODispatchLinkedtoExcel.[WO ID],
WODispatchLinkedtoExcel.[No ], WODispatchLinkedtoExcel.[C-Date],
WODispatchLinkedtoExcel.[S-Date], WODispatchLinkedtoExcel.[W-Date],
WODispatchLinkedtoExcel.[P-Date], WODispatchLinkedtoExcel.[Stat ],
WODispatchLinkedtoExcel.[WO-Qty], WODispatchLinkedtoExcel.[Open-Qty],
WODispatchLinkedtoExcel.[Pend-Qty], WODispatchLinkedtoExcel.[Run Hrs ],
WODispatchLinkedtoExcel.[Res-ID], WODispatchLinkedtoExcel.[Res-Desc],
WODispatchLinkedtoExcel.[Cut Sheet], WODispatchLinkedtoExcel.[Prod Line],
WODispatchLinkedtoExcel.User3, WODispatchLinkedtoExcel.[User4 ],
WODispatchLinkedtoExcel.[User5 ], WODispatchLinkedtoExcel.[User6 ],
WODispatchLinkedtoExcel.[User7 ], WODispatchLinkedtoExcel.[User8 ],
WODispatchLinkedtoExcel.[User9 ], WODispatchLinkedtoExcel.[User10 ],
WODispatchLinkedtoExcel.[Spec Sizing]
FROM (WODispatchLinkedtoExcel LEFT JOIN IDShipmentSystem ON
WODispatchLinkedtoExcel.[WO ID] = IDShipmentSystem.WO_ID) INNER JOIN
Query_ActivePlanners ON WODispatchLinkedtoExcel.Planner =
Query_ActivePlanners.Planner_Code
WHERE (((WODispatchLinkedtoExcel.[WO ID])>110096) AND
((IDShipmentSystem.WO_ID) Is Null));

================================================

--
Lorenzo Díaz
Cad Technician


Allen Browne said:
In WODispatchLinkedtoExcel_QueryCurr, use the Add Table dialog (toolbar) to
add Query_ActivePlanners as another input "table."

In the upper pane of query design, drag
WODispatchLinkedtoExcel_QueryCurr.Planner
and drop it onto:
Query_ActivePlanners.Planner_Code

Acess will create a line between the 2 "tables."
It default to an inner join, so it will show matches only.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ldiaz said:
I have a Query Named:
WODispatchLinkedtoExcel_QueryCurr
and I have many datas on it, and a column named [Planner]

also I have another query named:
Query_ActivePlanners and a column named [Planner_Code]

where it shows Planner Code availables..

What I want is to show in the first query only those datas which the value
is in Query_ActivePlanners

I have this criteria..

WHERE ((lbl_PlannerCodes.Planner_Code) Is Not Null)); but it shows all
datas.


please help

Thanks
Click to expand...
Click to expand...
 
You have a LEFT JOIN between the 2 tables. Because you only want the values
where both tables match, you need an INNER JOIN.

Double-click the line joining the 2 tables in query design view.
Access pops up a dialog with 3 options.
Choose the first one.
 
ldiaz said:
I have a Query Named:
WODispatchLinkedtoExcel_QueryCurr
and I have many datas on it, and a column named [Planner]

also I have another query named:
Query_ActivePlanners and a column named [Planner_Code]

where it shows Planner Code availables..

What I want is to show in the first query only those datas which the value
is in Query_ActivePlanners

I have this criteria..

WHERE ((lbl_PlannerCodes.Planner_Code) Is Not Null)); but it shows all
datas.


please help

Thanks
Click to expand...
 
Hi Allen, I have re-written code, but it does not work,

the LEF JOIN that I have on my query is for another criteria, I did what you
said but it only shows only datas with a Planner Code,
I have four Planner Codes,
BAM-06
JAXM-06
AT-06
DG-06
and this query only shows datas with Planner Codes equal to:BAM-06 ( the
first onw on the list of the Query_ActivePlanners.Planner_Code

could you help...I can send the DB if you want

SELECT WODispatchLinkedtoExcel.[Part ID], WODispatchLinkedtoExcel.Desc,
WODispatchLinkedtoExcel.Planner, WODispatchLinkedtoExcel.[WO ID],
WODispatchLinkedtoExcel.[No ], WODispatchLinkedtoExcel.[C-Date],
WODispatchLinkedtoExcel.[S-Date], WODispatchLinkedtoExcel.[W-Date],
WODispatchLinkedtoExcel.[P-Date], WODispatchLinkedtoExcel.[Stat ],
WODispatchLinkedtoExcel.[WO-Qty], WODispatchLinkedtoExcel.[Open-Qty],
WODispatchLinkedtoExcel.[Pend-Qty], WODispatchLinkedtoExcel.[Run Hrs ],
WODispatchLinkedtoExcel.[Res-ID], WODispatchLinkedtoExcel.[Res-Desc],
WODispatchLinkedtoExcel.[Cut Sheet], WODispatchLinkedtoExcel.[Prod Line],
WODispatchLinkedtoExcel.User3, WODispatchLinkedtoExcel.[User4 ],
WODispatchLinkedtoExcel.[User5 ], WODispatchLinkedtoExcel.[User6 ],
WODispatchLinkedtoExcel.[User7 ], WODispatchLinkedtoExcel.[User8 ],
WODispatchLinkedtoExcel.[User9 ], WODispatchLinkedtoExcel.[User10 ],
WODispatchLinkedtoExcel.[Spec Sizing]
FROM (WODispatchLinkedtoExcel LEFT JOIN IDShipmentSystem ON
WODispatchLinkedtoExcel.[WO ID] = IDShipmentSystem.WO_ID) INNER JOIN
Query_ActivePlanners ON WODispatchLinkedtoExcel.Planner =
Query_ActivePlanners.Planner_Code
WHERE (((WODispatchLinkedtoExcel.[WO ID])>110096) AND
((IDShipmentSystem.WO_ID) Is Null));
============================================
 
Hello allen

I have found the problem,

by mistake I put wrong Planners Codes in Planners Table, I have corrected
them, and now the Query is Working perfectly


Thanks for all your help
BAM-06
JAXM-06
AT-06
DG-06
Click to expand...

--
Lorenzo Díaz
Cad Technician


ldiaz said:
Hi Allen, I have re-written code, but it does not work,

the LEF JOIN that I have on my query is for another criteria, I did what you
said but it only shows only datas with a Planner Code,
I have four Planner Codes,
BAM-06
JAXM-06
AT-06
DG-06
and this query only shows datas with Planner Codes equal to:BAM-06 ( the
first onw on the list of the Query_ActivePlanners.Planner_Code

could you help...I can send the DB if you want

SELECT WODispatchLinkedtoExcel.[Part ID], WODispatchLinkedtoExcel.Desc,
WODispatchLinkedtoExcel.Planner, WODispatchLinkedtoExcel.[WO ID],
WODispatchLinkedtoExcel.[No ], WODispatchLinkedtoExcel.[C-Date],
WODispatchLinkedtoExcel.[S-Date], WODispatchLinkedtoExcel.[W-Date],
WODispatchLinkedtoExcel.[P-Date], WODispatchLinkedtoExcel.[Stat ],
WODispatchLinkedtoExcel.[WO-Qty], WODispatchLinkedtoExcel.[Open-Qty],
WODispatchLinkedtoExcel.[Pend-Qty], WODispatchLinkedtoExcel.[Run Hrs ],
WODispatchLinkedtoExcel.[Res-ID], WODispatchLinkedtoExcel.[Res-Desc],
WODispatchLinkedtoExcel.[Cut Sheet], WODispatchLinkedtoExcel.[Prod Line],
WODispatchLinkedtoExcel.User3, WODispatchLinkedtoExcel.[User4 ],
WODispatchLinkedtoExcel.[User5 ], WODispatchLinkedtoExcel.[User6 ],
WODispatchLinkedtoExcel.[User7 ], WODispatchLinkedtoExcel.[User8 ],
WODispatchLinkedtoExcel.[User9 ], WODispatchLinkedtoExcel.[User10 ],
WODispatchLinkedtoExcel.[Spec Sizing]
FROM (WODispatchLinkedtoExcel LEFT JOIN IDShipmentSystem ON
WODispatchLinkedtoExcel.[WO ID] = IDShipmentSystem.WO_ID) INNER JOIN
Query_ActivePlanners ON WODispatchLinkedtoExcel.Planner =
Query_ActivePlanners.Planner_Code
WHERE (((WODispatchLinkedtoExcel.[WO ID])>110096) AND
((IDShipmentSystem.WO_ID) Is Null));
============================================
--
Lorenzo Díaz
Cad Technician


Allen Browne said:
You have a LEFT JOIN between the 2 tables. Because you only want the values
where both tables match, you need an INNER JOIN.

Double-click the line joining the 2 tables in query design view.
Access pops up a dialog with 3 options.
Choose the first one.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Value error in a Subform 0
Access Dcount (multiple criteria) 3
Problem with 1 Query with Criteria feeding another query 3
Query Parameter using multiple options 0
Access Access Query Criterion 1
2 independent criteria in a query 2
Cascade queries 7
Amaze/Delrina Daily Planner on Windows 10 and beyond... 3

Back
Top