check box query

[vandy]

Hi All.

I would require some inputs and direction as to how to proceed.

I have 3 tables tblprojects, tblitems, tbltransactions
tblprojects : ProjectID,pno,pname,pselected
(pselected - yes / no ) checkbox

tblItems: ItemID,Itemno,ItemDesc,Uom,StockNo,Selected,status
(Selected - yes / no ) checkbox

tbltransactions:TransactionID,PID,TranItemID,Location,Units,DOT,Type

Relationship:
tblprojects ~ tbltransactions - ProjectID = PID
tblItems ~ tbltransactions - ItemID = TranItemID

I have a scenario were after a project is completed the items remaining will
be transferred to another table called Rawmaterial. It looses its identity as
being linked to a project and gets transferred as itemno, itemdesc, qoh

This step works by writing append and delete queries.

Question:

I have to select the items that i want to move to raw material table ,
delete the related transaction records in the master tables and nullify the
item no and project no values.

Problem:
When i select a particular project and items related to the project for data
transfer it gets transferred into raw material but after that when i go to
open the selected form all other corresponding items related to the projects
and visa versa are checked automatically.

How can i avoid this or have a button or check box to uncheck all checkboxes
after i have done selection to avoid unrelated records being deleted.

Is there any other approach i can follow.

Queries used:

selection query
SELECT tblprojects.Pno, tblitems.Itemno, tblitems.ItemDesc,
tblitems.Stock_no, tbltransactions.Location, tblitems.selected,
tblprojects.Pelected
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID=tbltransactions.TranItemID) ON
tblprojects.ProjectID=tbltransactions.PID
WHERE tblprojects.ProjectID=tbltransactions.PID
ORDER BY tblprojects.Pno, tblitems.Itemno;

delete qry:
DELETE tblitems.*, tblitems.selected, tblprojects.Pno, tblprojects.Pelected
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID
WHERE (((tblitems.selected)=True) AND ((tblprojects.Pelected)=True));

Inserting into rawmaterial

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected, QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, tblprojects.Pelected
HAVING (((tblitems.selected)=True) AND ((tblprojects.Pelected)=True));

thanks for your patience in reading this post. How can i access the selected
checkbox control and update the table .

 

[vandy]

I seem to have found out the solution by writing another query which will set
the selected to false in the table after all the transaction and data
transfer is completed.

it is working. Will appreciate the groups comments on if i am in the right
track here.
thanks

Hi All.

I would require some inputs and direction as to how to proceed.

I have 3 tables tblprojects, tblitems, tbltransactions
tblprojects : ProjectID,pno,pname,pselected
(pselected - yes / no ) checkbox

tblItems: ItemID,Itemno,ItemDesc,Uom,StockNo,Selected,status
(Selected - yes / no ) checkbox

tbltransactions:TransactionID,PID,TranItemID,Location,Units,DOT,Type

Relationship:
tblprojects ~ tbltransactions - ProjectID = PID
tblItems ~ tbltransactions - ItemID = TranItemID

I have a scenario were after a project is completed the items remaining will
be transferred to another table called Rawmaterial. It looses its identity as
being linked to a project and gets transferred as itemno, itemdesc, qoh

This step works by writing append and delete queries.

Question:

I have to select the items that i want to move to raw material table ,
delete the related transaction records in the master tables and nullify the
item no and project no values.

Problem:
When i select a particular project and items related to the project for data
transfer it gets transferred into raw material but after that when i go to
open the selected form all other corresponding items related to the projects
and visa versa are checked automatically.

How can i avoid this or have a button or check box to uncheck all checkboxes
after i have done selection to avoid unrelated records being deleted.

Is there any other approach i can follow.

Queries used:

selection query
SELECT tblprojects.Pno, tblitems.Itemno, tblitems.ItemDesc,
tblitems.Stock_no, tbltransactions.Location, tblitems.selected,
tblprojects.Pelected
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID=tbltransactions.TranItemID) ON
tblprojects.ProjectID=tbltransactions.PID
WHERE tblprojects.ProjectID=tbltransactions.PID
ORDER BY tblprojects.Pno, tblitems.Itemno;

delete qry:
DELETE tblitems.*, tblitems.selected, tblprojects.Pno, tblprojects.Pelected
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID
WHERE (((tblitems.selected)=True) AND ((tblprojects.Pelected)=True));

Inserting into rawmaterial

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected, QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, tblprojects.Pelected
HAVING (((tblitems.selected)=True) AND ((tblprojects.Pelected)=True));

thanks for your patience in reading this post. How can i access the selected
checkbox control and update the table .