473,605 Members | 2,637 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Update Query Help!

Hi,

I need some help on writing an update query to update "UnitsSold" field in
Products Table whenever I save a transaction. The transaction may contain
several "Subtransaction s", one for each product sold in that transaction. I
have a subtransaction table that maintains record of each subtransaction,
with data such as: ProductID, Quantity, TransactionID etc.
The Products table has fields: ProductID, UnitsSold, UnitsOrdered,
UnitCostPrice etc.

What I want is to update UnitsSold for each ProductID in Products table by
adding Quantity field for each subtransaction in Subtransaction table that
has this ProductID.

Currently I have this Query:
UPDATE Products INNER JOIN Subtransactions ON
[Products].[ProductID]=[Subtransactions].[ProductID]
SET Products.UnitsS old =
DSum("Quantity" ,"Subtransactio ns","Subtransac tions.ProductID =[Forms]![Transa
ctions]![Subtransactions Subform].[Form]![ProductID]")
WHERE
((([Subtransactions].[ProductID])=[Forms]![Transactions]![Subtransactions
Subform].Form!ProductID ));

The problem with this query is that it only updates the UnitsSold for the
product that is currently "selected" in the Subtransactions Subform.

Can someone suggest how can the query be modified so as to update ALL
products, and not the only one selected?

Thanks in advance.

Kunal.

Nov 12 '05 #1
1 5171
Can I suggest you do not do this?
How will you handle it when a user deletes one of the subform records? What
about if someone changes the quantity from 100 to 10 after you have already
run this update query?

For an alternative way to solve this issue, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Kunal" <ku*********@mo torola.com> wrote in message
news:bj******** **@avnika.corp. mot.com...
Hi,

I need some help on writing an update query to update "UnitsSold" field in
Products Table whenever I save a transaction. The transaction may contain
several "Subtransaction s", one for each product sold in that transaction. I have a subtransaction table that maintains record of each subtransaction,
with data such as: ProductID, Quantity, TransactionID etc.
The Products table has fields: ProductID, UnitsSold, UnitsOrdered,
UnitCostPrice etc.

What I want is to update UnitsSold for each ProductID in Products table by
adding Quantity field for each subtransaction in Subtransaction table that
has this ProductID.

Currently I have this Query:
UPDATE Products INNER JOIN Subtransactions ON
[Products].[ProductID]=[Subtransactions].[ProductID]
SET Products.UnitsS old =
DSum("Quantity" ,"Subtransactio ns","Subtransac tions.ProductID =[Forms]![Transa ctions]![Subtransactions Subform].[Form]![ProductID]")
WHERE
((([Subtransactions].[ProductID])=[Forms]![Transactions]![Subtransactions
Subform].Form!ProductID ));

The problem with this query is that it only updates the UnitsSold for the
product that is currently "selected" in the Subtransactions Subform.

Can someone suggest how can the query be modified so as to update ALL
products, and not the only one selected?

Thanks in advance.

Kunal.

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
10222
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline View Query: Select Sq from ( Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank() Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by End_Date) As Sq
2
8520
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An example of what I'm trying to do is below: update (tbl_ind_mananas LEFT JOIN (select count(*) as count, (dubicacion || zona || manzana) as cod_manzana from tbl_censo_poblacion_1993 group by dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as...
10
3260
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This wouldn't work (nothing changed): UPDATE tblManuals SET tblManuals.PARTNUM = Trim(); Would someone please tell me how to do an update query that will trim the spaces?
8
3704
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: "Date","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15","P16","P17","P18","P19","P20","P21" 1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21 1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
7
3524
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" Transactions = name of the table I want to update balance = name of the field i want to update daily balance= name of the query result that I want to move to the table
3
3437
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all the necessary records in it when testing it. I get the error "No value given for one or more required parameters." when I try to update the database. Can you tell me what am I doing wrong?
8
1896
by: rriness | last post by:
I'm getting an inconsistent failure when trying to save data in ADO.Net. I'm using an Access database with a simple query - SELECT StudentID, FirstName, LastName FROM Students - and have no other users in the database. I keep getting the message 'Operation must use an updateable query.' Process seems to work on some PC's, not others. Is there a difference in .Net framework versions? Or some other
3
2668
by: eholz1 | last post by:
Hello PHP programmers. I had a brilliant idea on one of my pages that selects some data from my mysql database. I first set the page up to display some info and an image, just one item, with a row of data, etc. then I thought it would be nice to do a select, and perhaps an update (the title of the image) on the same page.
16
3482
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
0
8004
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8418
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8071
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8288
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5445
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2438
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1541
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1271
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.