If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can not update records based on two queries
I have a form based on this query
Browse_All_Query SELECT T_PartNumbers.PartNumberID, T_PartNumbers.PartNumber, T_PartNumbers.Description, T_PartNumbers.CategoryID, IIf([CategoryID] In (1,2,3,4,5),"Edit","") AS Edit, T_TempAdd.Quantity, * FROM T_PartNumbers LEFT JOIN T_TempAdd ON T_PartNumbers.PartNumberID = T_TempAdd.PartNumberID; The form is a datasheet with PartNumberID, PartNumber, Description, and T_TempAdd.Quantity, I use it with an append query to add items to a parts order Table I would like to add a UnitsinStk field but when I add the the inventory_totals_query I can not change the values in my T_TempAdd.Quantity. Inventory_totals_query SELECT T_InventoryTransactions.PartNumberID, Sum(T_InventoryTransactions.UnitsOrdered) AS SumOfUnitsOrdered, Sum(T_InventoryTransactions.UnitsReceived) AS SumOfUnitsReceived, Sum(T_InventoryTransactions.UnitsSold) AS SumOfUnitsSold, Sum(T_InventoryTransactions.UnitsShrinkage) AS SumOfUnitsShrinkage, Sum(NZ([UnitsReceived])-nz([UnitsSold])-nz([unitsshrinkage])) AS UnitsinStk FROM T_InventoryTransactions GROUP BY T_InventoryTransactions.PartNumberID; I have spent a little time researching un-updatable queries but have not found an answer.. Any help would be greatly appreciated.. Barry |
#2
|
|||
|
|||
Can not update records based on two queries
If you have a query with a GROUP BY or DISTINCT clause, that will make the
query unupdateable. There's a whole bunch of things that can make a query or form unupdateable. The link below explains it. http://support.microsoft.com/?kbid=328828 Thanks for posting the SQL. It made it much easier for me to answer. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Barry A&P" wrote: I have a form based on this query Browse_All_Query SELECT T_PartNumbers.PartNumberID, T_PartNumbers.PartNumber, T_PartNumbers.Description, T_PartNumbers.CategoryID, IIf([CategoryID] In (1,2,3,4,5),"Edit","") AS Edit, T_TempAdd.Quantity, * FROM T_PartNumbers LEFT JOIN T_TempAdd ON T_PartNumbers.PartNumberID = T_TempAdd.PartNumberID; The form is a datasheet with PartNumberID, PartNumber, Description, and T_TempAdd.Quantity, I use it with an append query to add items to a parts order Table I would like to add a UnitsinStk field but when I add the the inventory_totals_query I can not change the values in my T_TempAdd.Quantity. Inventory_totals_query SELECT T_InventoryTransactions.PartNumberID, Sum(T_InventoryTransactions.UnitsOrdered) AS SumOfUnitsOrdered, Sum(T_InventoryTransactions.UnitsReceived) AS SumOfUnitsReceived, Sum(T_InventoryTransactions.UnitsSold) AS SumOfUnitsSold, Sum(T_InventoryTransactions.UnitsShrinkage) AS SumOfUnitsShrinkage, Sum(NZ([UnitsReceived])-nz([UnitsSold])-nz([unitsshrinkage])) AS UnitsinStk FROM T_InventoryTransactions GROUP BY T_InventoryTransactions.PartNumberID; I have spent a little time researching un-updatable queries but have not found an answer.. Any help would be greatly appreciated.. Barry |
#3
|
|||
|
|||
Can not update records based on two queries
Jerry Thanks for the help on the Group By Clause.. Do you have any insight
as to how i might deal with the issue i am having. my T_Partnumbers joined to my T_Temp Add works exactly as i like i would like users to somehow be able to also view the quantityavailable before ordering more.. is my initial design way off or is there a genius but simple step or method i am overlooking Thanks Barry "Jerry Whittle" wrote: If you have a query with a GROUP BY or DISTINCT clause, that will make the query unupdateable. There's a whole bunch of things that can make a query or form unupdateable. The link below explains it. http://support.microsoft.com/?kbid=328828 Thanks for posting the SQL. It made it much easier for me to answer. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Barry A&P" wrote: I have a form based on this query Browse_All_Query SELECT T_PartNumbers.PartNumberID, T_PartNumbers.PartNumber, T_PartNumbers.Description, T_PartNumbers.CategoryID, IIf([CategoryID] In (1,2,3,4,5),"Edit","") AS Edit, T_TempAdd.Quantity, * FROM T_PartNumbers LEFT JOIN T_TempAdd ON T_PartNumbers.PartNumberID = T_TempAdd.PartNumberID; The form is a datasheet with PartNumberID, PartNumber, Description, and T_TempAdd.Quantity, I use it with an append query to add items to a parts order Table I would like to add a UnitsinStk field but when I add the the inventory_totals_query I can not change the values in my T_TempAdd.Quantity. Inventory_totals_query SELECT T_InventoryTransactions.PartNumberID, Sum(T_InventoryTransactions.UnitsOrdered) AS SumOfUnitsOrdered, Sum(T_InventoryTransactions.UnitsReceived) AS SumOfUnitsReceived, Sum(T_InventoryTransactions.UnitsSold) AS SumOfUnitsSold, Sum(T_InventoryTransactions.UnitsShrinkage) AS SumOfUnitsShrinkage, Sum(NZ([UnitsReceived])-nz([UnitsSold])-nz([unitsshrinkage])) AS UnitsinStk FROM T_InventoryTransactions GROUP BY T_InventoryTransactions.PartNumberID; I have spent a little time researching un-updatable queries but have not found an answer.. Any help would be greatly appreciated.. Barry |
#4
|
|||
|
|||
Can not update records based on two queries
On Wed, 4 Mar 2009 08:06:01 -0800, Barry A&P
wrote: Jerry Thanks for the help on the Group By Clause.. Do you have any insight as to how i might deal with the issue i am having. my T_Partnumbers joined to my T_Temp Add works exactly as i like i would like users to somehow be able to also view the quantityavailable before ordering more. If you can calculate the quantityavailable in a query, the users can view it (using that query). It's NOT necessary, in fact it's a bad idea, to store that calculated quantity in any Table to do so! Just create a form based on your calculated query rather than trying to store the field. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Can not update records based on two queries
John
My Units available is a calculation based on units sold and units purchased. when i link my Totals query and my part numbers table and a temp table. the data set becomes un-updatable. The temp table is on each front end to store a grocery list of parts that need to be ordered until my appent query sends them to the back end table. Any ideas?? "John W. Vinson" wrote: On Wed, 4 Mar 2009 08:06:01 -0800, Barry A&P wrote: Jerry Thanks for the help on the Group By Clause.. Do you have any insight as to how i might deal with the issue i am having. my T_Partnumbers joined to my T_Temp Add works exactly as i like i would like users to somehow be able to also view the quantityavailable before ordering more. If you can calculate the quantityavailable in a query, the users can view it (using that query). It's NOT necessary, in fact it's a bad idea, to store that calculated quantity in any Table to do so! Just create a form based on your calculated query rather than trying to store the field. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Can not update records based on two queries
On Wed, 4 Mar 2009 13:15:02 -0800, Barry A&P
wrote: John My Units available is a calculation based on units sold and units purchased. when i link my Totals query and my part numbers table and a temp table. the data set becomes un-updatable. The temp table is on each front end to store a grocery list of parts that need to be ordered until my appent query sends them to the back end table. Any ideas?? That doesn't really give me enough of a view of the database to be certain; but could you perhaps have a textbox on the Form with a control source using DSum or DCount or DLookUp to display the needed value? -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Can not update records based on two queries
Voila Dsum the answer to all my problems..
Got it with =DSum("UnitsReceived","T_InventoryTransactions","P artNumberID = [ID]") Youre a lifesaver John Barry "John W. Vinson" wrote: On Wed, 4 Mar 2009 13:15:02 -0800, Barry A&P wrote: John My Units available is a calculation based on units sold and units purchased. when i link my Totals query and my part numbers table and a temp table. the data set becomes un-updatable. The temp table is on each front end to store a grocery list of parts that need to be ordered until my appent query sends them to the back end table. Any ideas?? That doesn't really give me enough of a view of the database to be certain; but could you perhaps have a textbox on the Form with a control source using DSum or DCount or DLookUp to display the needed value? -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|