A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can not update records based on two queries



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2009, 03:28 AM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default 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  
Old March 4th, 2009, 04:01 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old March 4th, 2009, 05:06 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default 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  
Old March 4th, 2009, 07:25 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old March 4th, 2009, 10:15 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default 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  
Old March 5th, 2009, 02:25 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old March 5th, 2009, 09:54 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:07 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.