Can not update records based on two queries

B

Barry A&P

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
 
J

Jerry Whittle

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.
 
B

Barry A&P

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

John W. Vinson

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.
Click to expand...

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.
 
B

Barry A&P

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??
 
J

John W. Vinson

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??
Click to expand...

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?
 
B

Barry A&P

Voila Dsum the answer to all my problems..
Got it with
=DSum("UnitsReceived","T_InventoryTransactions","PartNumberID = [ID]")
Youre a lifesaver John

Barry
 
Top