access data from sub query using values from main query

[ramesh_ndyd]

then its not correct, the table that you have shown is correct, but the values will remain same even if the vehicle is changed, how can you change vehicle type in sub query during execution

identification_code	repair_schedule	type_of_vehicle	part_no	qty_to_use	available_qty	Earmarked
b001	2015	bike1	abc-111	2	20	4
b001	2015	bike1	abc-112	3	30	6
b001	2015	bike1	abc-113	1	10	2

identification_code	repair_schedule	type_of_vehicle	part_no	qty_to_use	available_qty	Earmarked
b003	2014	bike2	xyz-001	2	21	0
b003	2014	bike2	xyz-002	1	11	0

[June7]

Try nesting the queries for an all-in-one:

SELECT inventory.vehicle_type, inventory.part_no, inventory.available_qty, sum_query.SumOfqty_to_use
FROM inventory INNER JOIN
(SELECT BOM.vehicle_type, BOM.part_no, Sum(BOM.qty_to_use) AS SumOfqty_to_use
FROM (vehicle_identification INNER JOIN repair_schedule ON vehicle_identification.identification_code = repair_schedule.identification_code) INNER JOIN BOM ON vehicle_identification.vehicle_type = BOM.vehicle_type
WHERE (((repair_schedule.repair_schedule)<2015))
GROUP BY BOM.vehicle_type, BOM.part_no
HAVING (((BOM.vehicle_type)=[input vehicle type]))) As sum_query ON (inventory.part_no = sum_query.part_no) AND (inventory.vehicle_type = sum_query.vehicle_type);

You will have to type this in the SQL view window.

[ramesh_ndyd]

Wish you a very happy new year 2012

the query does not work if you change the input to bike2

[June7]

These are the outputs I get.

Result with 'bike1' input.

vehicle_type part_no available_qty SumOfqty_to_use
bike1 abc-111 20 4
bike1 abc-112 30 6
bike1 abc-113 10 2

Result with 'bike2' input.

vehicle_type part_no available_qty SumOfqty_to_use
bike2 xyz-001 21 2
bike2 xyz-002 11 1

[ramesh_ndyd]

in the second query you should get sumofqty as 0 because there are no vehicles off the same category for repairs ahead of this one in 2014. So no quantitis will be allocated. Check table above

[ramesh_ndyd]

I solved the problem, I was trying to use the main query values in the sub query rather than vice versa i.e. the value of the sub query in the main query. this solved the problem.

the final query is as under


SELECT BOM.part_no, BOM.qty_to_use, inventory.available_qty, (Sum(BOM.qty_to_use)-BOM.qty_to_use) AS Earmarked
FROM inventory INNER JOIN ((vehicle_identification INNER JOIN repair_schedule ON vehicle_identification.identification_code = repair_schedule.identification_code) INNER JOIN BOM ON vehicle_identification.vehicle_type = BOM.vehicle_type) ON inventory.part_no = BOM.part_no
WHERE (((BOM.vehicle_type)=(SELECT vehicle_identification.vehicle_type
FROM classification INNER JOIN vehicle_identification ON classification.type_of_vehicle = vehicle_identification.vehicle_type
WHERE (((vehicle_identification.identification_code)=[id : ])); )) AND ((repair_schedule.repair_schedule)<=[year :]))
GROUP BY BOM.part_no, BOM.qty_to_use, inventory.available_qty;


this gives the results as required
using Identification code b003(bike2) i get the table

part_no	qty_to_use	available_qty	Earmarked
xyz-001	2	21	0
xyz-002	1	11	0

this gives the results as required
using Identification code b001(bike1) i get the table

part_no	qty_to_use	available_qty	Earmarked
abc-111	2	20	4
abc-112	3	30	6
abc-113	1	10	2

[ramesh_ndyd]

how to use the above query in a form with combo's and subform

wherein combo's will provide for the input


* how to use link fields for sub query

regards
ramesh

[June7]

You want to refer to comboboxes for input parameters?

WHERE vehicle_identification.identification_code=Forms!f ormname!IDcomboname AND repair_schedule.repair_schedule<=Forms!formname!Ye arcomboname

[ramesh_ndyd]

should i place this query in the visual basic code

[ramesh_ndyd]

i was using this code its givin me errors
strsql =above query
me.subform.sourceobject =strsql


instead of [id:] i changed the code using """ & combo.value & ”””
For date #" & combodate.value & "#

[June7]

Not clear to me if you are asking for assistance or telling us you solved the issue.

[ramesh_ndyd]

when i was using this as query i was getting errors i even tried
form.subform.form.recordsource = strsql
still was getting errors i mentioned the above as it was the way i was using the code. Is there something wrong with it

error : object name 'TableName' you entered doesn't follow Microsoft Office
Access object-naming rules.

[June7]

I don't understand what you are trying to do. Can you provide project? I will analyze your efforts.

[ramesh_ndyd]

i have attached the sample database with the form. pls suggest the correction.

[June7]

Can't set the SourceObject property of a subform container control to just an SQL statement. It must be an Access object (table, query, form, report). Then the code can apply filter to the object or set RecodSource property of the form or report. I have never set a table or query as a SourceObject.