Sum rows and update another table (1 Viewer)

zazar

New member
Local time
Today, 20:12
Joined
Jul 8, 2020
Messages
14
Hello,
Thanks to y'all I had this great code yesterday
Code:
Private Sub ImportXls_Click()
Dim Filepath As String
Dim strTable As String
Dim MainTable As String
Dim strSql as String
Dim base As DAO.database

Filepath = Me.fpath.Value
MainTable = "Operation"
strTable= "TemporJ"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, Filepath, True

    Set base = CurrentDb
         strSql = "INSERT INTO MainTable SELECT * FROM " & strTable & " WHERE id_Stock IS NOT NULL"
    base.Execute strSql, dbFailOnError
    base.Close
    Set base = Nothing
DoCmd.DeleteObject acTable, strSheet
MsgBox "Data have been imported.", vbInformation,
End Sub

Now before the deletion of that temporary table I need calculations to be done. I did not find how to pose this problem a little complex, so I made a small diagram :


tempj.png

I really hope you'll understand what I want to achieve. If not I will try to give more details.
 

Ranman256

Well-known member
Local time
Today, 15:12
Joined
Apr 9, 2015
Messages
4,339
Q1, sum your totals to a temp table,
Q2, update your target fields from the temp table
 

zazar

New member
Local time
Today, 20:12
Joined
Jul 8, 2020
Messages
14
Yes but the code behind those queries... I assume it must be something like for each... sum but I can't do it.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:12
Joined
May 21, 2018
Messages
8,463
You likely do not need to have a main table unless there is something more to it. You can display this in a query. Show the data in an aggregate query grouping by ID_stock and totaling amount. You can do this in query design view by selecting the Summation symbol and grouping on ID_Stock and Sum amount. I do not understand the amount_gb calculation though with 30 and 9. If you have to save this in a table then still do an aggregate query to do the calculations and then an insert

Also do not know if these are typos, but the calculations make no sense. You say the sum for stock 1 is 800 but it is either 900 if you sum all records or 750 if you sum just gb. Then you show 750. Then for stock 2 you show 202 which looks like it should be 200 but that is a gt record. As I said, I do not understand the 30 and 9 for amount-gb.
 
Last edited:

zazar

New member
Local time
Today, 20:12
Joined
Jul 8, 2020
Messages
14
The tables have to be distinct and they have much more fields (almost 30 for the main table actualy). I just create and example with the 02 fields that where I need the calculations to be done.
I already know my way when it comes to built query in design view but I'm not yet good to code in VBA.
Here the calculations must be on the temp table because I want them to be done only on the new values.

Thank you for reminding me aggregate query, I built a lot of them in this database.

For the amount_gb, data are enter by users at the begining of the process (same for total). It could be monday. After that, operations are done (it could be tuesday) in the company software and the excel file are extrated. This excel file is the one which the user will import in this database. So the data in excel file are the goods ones. They could be the same as what the user enter at the beginning of the process but the could be differents.

The data in the firts are just randoms one to illustrate the thing.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:12
Joined
Feb 19, 2002
Messages
42,972
Storing a calculated value violates second normal form and is not recommended unless you are developing a data warehouse where all data is completely replaced periodically. Whenever you want the sum of a set of data, use a query to produce it. When you store calculated data, it becomes outdated as soon as someone updates a value that is part of the sum. This is why most inventory systems rely on storing transactions which are summed as needed rather than keeping a running total of what is on hand.

I'm not sure why you are keeping a temp table but you may not have to if you simply adjust how the "final" table is used. For example, If you must enter a set of data and confirm the results before the set can be used for anything except data entry, you can add a "complete" flag to the parent record. Once the data entry is completed, you can press a "validate" button which examines the set of data and balances it. If it satisfies the validation requirements, you can set the "complete" flag to true allowing the rest of the application to use the data.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:12
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub ImportXls_Click()
    Dim Filepath As String
    Dim strTable As String
    Dim MainTable As String
    Dim strSQL As String
    Dim base As DAO.Database

    Filepath = Me.fpath.Value
    MainTable = "Operation"
    strTable = "TemporJ"

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, Filepath, True

    Set base = CurrentDb
    strSQL = "INSERT INTO MainTable SELECT * FROM " & strTable & " WHERE id_Stock IS NOT NULL"
    base.Execute strSQL, dbFailOnError
    
    '///
    'arnelgp
    'update MainTable Totals
    '
    base.Execute _
        "UPDATE MainTable SET MainTable.total = Nz(DSum(""amount"",""" & strTable & """,""id_stock="" & [id_stock]),0), " & _
        "MainTable.amount_gb = Nz(DSum(""amount"",""" & strTable & """,""id_stock="" & [id_stock] & "" and state='gb'""),0);"
    base.Close
    Set base = Nothing
    DoCmd.DeleteObject acTable, strSheet
    MsgBox "Data have been imported.", vbInformation
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:12
Joined
Feb 19, 2002
Messages
42,972
Although arne loaded your gun with bullets, you are responsible for not shooting yourself. He showed you how to store initial values but not how to keep them current. If you store calculated values, YOU are responsible for ensuring that they are kept updated. If this database is for your own use, do what you want. If you are creating the application for someone else, they are trusting you to be responsible and provide valid data. Storing calculated values leads to presenting invalid data to the people who are relying on you.
 

Users who are viewing this thread

Top Bottom