access data from sub query using values from main query

[ramesh_ndyd]

hi,

how do you use row values from the main query in the sub query. i am obtaining values of the rows in the main query by joining three tables and sub query will use data from the rows obtained.

the problem is for an forecasting spares inventory management system wherein i have to set earmarked items for the repairs of same type of equipment which will come in before the projected equipment/machinery.

four tables are like this

table 1
class_of_vehicle vehicle_type

table 2
vehicle_type make identification_code

table 3
identification_code part_no qty_to_use

table 4
vehicle_type part_no Available_qty






regards

ramesh

[June7]

Don't really understand the request. Possibly you need to set up form/subform arrangement.

[ramesh_ndyd]

hi,
Its like this

table 1(classification of vehicle)
class_of_vehicle vehicle_type
two wheeler bike1
two wheeler bike2
four wheeler car1


table 2(vehicle identification, same model can be landed by different person for repairs)
vehicle_type make identification_code
bike1 make1 b001
bike1 make1 b002
bike2 make2 b003
car1 make1 c001

table 3(Billl of materials i.e. list of spares to be kept for repairs when a vehicle is planned for repairs)
identification_code part_no qty_to_use
b001 abc-111 2
b001 abc-112 3
b002 xyz-001 1
b002 xyz-002 3
c001 uvw-01 2
c002 uvw-02 1

table 4(actual inventory available with me)
vehicle_type part_no Available_qty
bike1 abc-111 12
bike1 abc-112 30
bike2 xyz-001 10
bike2 xyz-002 23
car1 uvw-01 2
car1 uvw-02 1

repair schedule
identification_code repair_schedule(year)
b001 2012
b003 2014
b002 2014
c001 2013

this is the data tables
my question is
how to write a query that will give results in the format below
using two inputs(inputbox/dialogbox)
1. identification code
2. year of repair

suppose b001 is the code and year of repairs is 2014

result table:
identification_code repair_schedule part_no qty_to_use Earmarked_Qty available_qty
b002 2014 abc-111 2 2 12


where earmarked_qty is the quantities which are allocated for repairs of b001 in year 2012

hope i have made myself clear

regards
ramesh

[ramesh_ndyd]

sorry please correct it

suppose b002 is the code and year of repairs is 2014


if my table structure is incorrect please suggest changes.

[June7]

Looks like repair_schedule field can be in Table2 and then Table5 is not needed. Where does field earmarked_quantity come from?

Maybe this is what you want:
SELECT Table2.identification_code, Table2.repair_schedule, Table3.part_no, Table3.qty_to_use, Table4.available_qty
FROM Table4 INNER JOIN (Table3 INNER JOIN Table2 ON Table3.identification_code = Table2.identification_code) ON Table4.part_no = Table3.part_no;

[ramesh_ndyd]

earmarked qty is the difference between the sum of total quantities being used for earlier repairs of a particular vehicle type and the actual inventory

like spares of b001 in 2012 has to be allocated before. so in this case it will be

table4 available_qty(12)- table 3 Qty_to_use(2)

this will be repeating for each part_no of b001

so the sub query should check for three criteria ie year(table5), vehicle_type(table2) and part_no(table3 or table4)

i am able to get all the other values using a normal query.

[June7]

I am still confused by what you want. You want to know the total of qty_to_use for a vehicle/part for all years prior to target year? Either filter the records to include only those prior to that year or build query that does a 'running total'. Check this thread http://stackoverflow.com/questions/2...current-record

An Access report has RunningSum as a property of textbox.

[ramesh_ndyd]

hi,
i have attached the file with the two queries ie EARMARKED and SUM_QUERY

the problem is how to merge both the queries together with the inputs to the sum_query being optained from the rows in the earmarked query
i want to add a column earmarked in the EARMARKED query with the values from sum_query updating each rows while running

like if you put identification code as b001 and schedule as 2015 in earmarked quer you get
identification_code repair_schedule type_of_vehicle part_no qty_to_use available_qty
b001 2015 bike1 abc-111 2 20
b001 2015 bike1 abc-112 3 30
b001 2015 bike1 abc-113 1 10

if you put vehicle type as bike1 and schedule as 2015 and part no abc-111 in sum_query you get value 4 which is the required value
but these input should be automatically provided from the rows above and added to a column EARMARKED in table above


regards
ramesh

[June7]

Is this close? I changed sum_query to:
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)="bike1") AND ((BOM.part_no)="abc-111"));

Then I joined that query to inventory table:
SELECT inventory.vehicle_type, inventory.part_no, inventory.available_qty, sum_query.SumOfqty_to_use
FROM inventory INNER JOIN sum_query ON (sum_query.vehicle_type = inventory.vehicle_type) AND (inventory.part_no = sum_query.part_no);

I used actual values for testing instead of input parameter prompts. I don't use parameter prompts in queries, can't validate user entries. I create a form for user input and then query refers to controls on form for input values.

[ramesh_ndyd]

the values of the sub query should be automatically updated from the main query not based on inputs

the query should be something like this


SELECT repair_schedule.identification_code, repair_schedule.repair_schedule, classification.type_of_vehicle, BOM.part_no, BOM.qty_to_use, inventory.available_qty, (SELECT sum(BOM.qty_to_use) AS Earmarked_qty
FROM (classification INNER JOIN (vehicle_identification INNER JOIN repair_schedule ON vehicle_identification.identification_code=repair_ schedule.identification_code) ON classification.type_of_vehicle=vehicle_identificat ion.vehicle_type) INNER JOIN BOM ON vehicle_identification.vehicle_type=BOM.vehicle_ty pe
WHERE (((classification.type_of_vehicle)=classification. type_of_vehicle) AND ((repair_schedule.repair_schedule)<repair_schedule .repair_schedule) AND ((BOM.part_no)=BOM.part_no)) ; ) AS Earmarked
FROM (((classification INNER JOIN vehicle_identification ON classification.type_of_vehicle = vehicle_identification.vehicle_type) INNER JOIN repair_schedule ON vehicle_identification.identification_code = repair_schedule.identification_code) INNER JOIN BOM ON vehicle_identification.vehicle_type = BOM.vehicle_type) INNER JOIN inventory ON BOM.part_no = inventory.part_no
WHERE (((repair_schedule.identification_code)=[identification code : ]) AND ((repair_schedule.repair_schedule)=[repair Scedule: ]));


the bold part is the sub query with the portion in italics being the problematic part

(((classification.type_of_vehicle)=classification.type_of_vehicle) AND ((repair_schedule.repair_schedule)<repair_schedule.repair_schedule) AND ((BOM.part_no)=BOM.part_no)) the bold portions should be obtained from the row of the main query

[June7]

Well, my suggested structure has only the one set of parameters in the WHERE and HAVING clauses. Did not use the Earmarked query. Did you test suggestion?

[ramesh_ndyd]

i tried your query it gives me values for a particular part_no only. whereas
when i am considering an vehicle say bike1 it has many spares with individual part numbers. each of them has to be calculated while running the query. with my query statement i am not able to get result.

the earmarked query table should automatically be filled with earmarked values.

inputting values for a particular part no will not serve the purpose.

the system is designed for a vehicle garage where different persons land different vehicles for repairs. the stock in hand has to be considered for this. suppose a 10 vehicles are being landed for repairs in 10 days. to predict exact stock in hand at for the 10th vehicle we have to find how much we have and what will have to be allocated(earmarked) for the 9 vehicles coming before, irrespective whether the parts will be used or not.

when the query executes it should work like this
data generated

r1.c1.value, r1.c2.value, r1.c3.value, r1.c4.value, subquery (sum) where vehilcetype=r1.c1.value and partno=r1.c3.value

r2.c1.value, r2.c2.value, r2.c3.value, r2.c4.value, subquery (sum) where vehilcetype=r2.c1.value and partno=r2.c3.value

r3.c1.value, r3.c2.value, r3.c3.value, r3.c4.value, subquery (sum) where vehilcetype=r3.c1.value and partno=r3.c3.value

[June7]

If I remove the part_no from filter criteria I can get this result:

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

If this still is not the output you want, show a tabulated example I can aim for.

[ramesh_ndyd]

can you please write down the query for me, the resul obtained looks almost right.

[June7]

It's the same queries in post #9, with the part_no criteria removed.