Solved Update Query question

[tvman5683]

Can an update query be designed to perform a calculation on a table column(currency) then append that result to the same table in a new row that the becomes part of the total for that column. I want to sum the Customer Rate column then multiply by the 23% (FSC row) then append(or update) the result to the FSC row Customer Rate cell. Currently I do it manually by creating a sum table then manually calculate and manually enter back into the table.

Thanks for any advice

Job Date Customer Delivery Ticket # Dump Pit # Tons Loads Customer Rate 28-Jun-22 LRS 1236 115846 0 1 $550.00 28-Jun-22 LRS 1595 115836 14.9 1 $550.00 28-Jun-22 LRS 1596 115920 20.5 1 $550.00 28-Jun-22 LRS 1215 115841 19.1 1 $550.00 28-Jun-22 LRS 1545 115909 0 1 $550.00 27-Jun-22 LRS 1547 115603 20.2 1 $550.00 27-Jun-22 LRS 1593 115593 19.4 1 $550.00 27-Jun-22 LRS 1594 115683 18.6 1 $550.00 01-Jul-22 LRS 1847 116613 20 1 $550.00 01-Jul-22 LRS 1532 116632 20 1 $550.00 01-Jul-22 LRS 1444 116618 20.9 1 $550.00 06-Jul-22 FSC 0 0.23 $0.00

 

[theDBguy]

Hi. I don't see why not, but I don't see the why either. Why can't you just use a report instead? Just wondering...

 

[The_Doc_Man]

It isn't an UPDATE if you want to create a new record. It would be an INSERT INTO query.

From your description, I MIGHT consider this approach.

First, write a summation query to generate whatever sum you are trying to generate. You should be able to look up SUMMATION QUERY easily enough. I'm not clear on what else might go in this query because your description is a bit limited. Summation queries resemble this:

SELECT SUM( [Customer Rate] ) AS SumRate, [Customer], ... FROM MyTable GROUP BY [Customer] ;

This would give you a query with two fields: SumRate and Customer.

Then as a second step, write an INSERT INTO (append) query to create the new record you want. Look up the syntax of INSERT INTO ...SELECT, which is the way that you would insert records based off a table or query (which appears in the SELECT clause).

INSERT INTO MyTable (list of fields to be inserted in one record) SELECT <<<list of fields to be used in the insertion>>> FROM MyNewQuery ;

NOTE that there is A LOT more to it than this. Inserting a new record into a table is perfectly permissible, but you must not neglect the other fields in the table, particularly if any of them have validation constraints. Otherwise, the INSERT would fail.

You didn't give us much of a hint as to environment because VBA is common to Office programs. However, there is one clue... You are talking "rows and columns" which immediately tells me that you are thinking about this problem as an Excel user. If this actually IS Excel, you wouldn't do it this way anyway. VBA works OK, but Excel's main user interface has a different way to do this anyway. And if this IS Access, then you SURELY would not do it this way. As TheDBGuy suggests, this might be something you would do in a report, not via a query.

 

[Pat Hartman]

Tables are intended to hold data where the column meanings are identical for each row. Your FSC row seems to be an outlier. Perhaps it belongs in a separate table. It would be helpful to see a before and after set of data.

And as the others are suggesting, are you certain you actually need to store the calculated data?

 

[tvman5683]

Thanks for all the replies. Sorry it's an Access table that I use to create an Invoice. Once I have all the line Items I need to calculate the FSC charge and add that to the Sum

Here is the final result I'm trying for. The Sum of the Customer rate is $6050. The 23% surcharge is $1391.50 so itmakes the amount due $7441.50. I guess this would be similar to calculating tax on a sales receipt. But the FSC changes from customer to customer so I can't just have a fixed amount. I did try to add a parameter field to the report footer but sometimes it would be pushed to the last page if there are multple pages. I prefer to have it as another row.

Also below is the form I use to show me the total then I open a calculator to create the FSC charge, then open the Invoice table and add to the table which is then used to create the Invoice/report.

Thanks again for all the replies I will try some of the suggestions and report back

Job Date	Customer	Delivery Ticket #	Dump Pit #	Tons	Loads	Customer Rate
28-Jun-22	LRS	1236	115846	0	1	$550.00
28-Jun-22	LRS	1595	115836	14.9	1	$550.00
28-Jun-22	LRS	1596	115920	20.5	1	$550.00
28-Jun-22	LRS	1215	115841	19.1	1	$550.00
28-Jun-22	LRS	1545	115909	0	1	$550.00
27-Jun-22	LRS	1547	115603	20.2	1	$550.00
27-Jun-22	LRS	1593	115593	19.4	1	$550.00
27-Jun-22	LRS	1594	115683	18.6	1	$550.00
01-Jul-22	LRS	1847	116613	20	1	$550.00
01-Jul-22	LRS	1532	116632	20	1	$550.00
01-Jul-22	LRS	1444	116618	20.9	1	$550.00
06-Jul-22		FSC		0	0.23	$1,391.50
						$7,441.50​

 

[tvman5683]

Thanks for all the replies. Sorry it's an Access table that I use to create an Invoice. Once I have all the line Items I need to calculate the FSC charge and add that to the Sum

Here is the final result I'm trying for. The Sum of the Customer rate is $6050. The 23% surcharge is $1391.50 so itmakes the amount due $7441.50. I guess this would be similar to calculating tax on a sales receipt. But the FSC changes from customer to customer so I can't just have a fixed amount. I did try to add a parameter field to the report footer but sometimes it would be pushed to the last page if there are multple pages. I prefer to have it as another row.

Also below is the form I use to show me the total then I open a calculator to create the FSC charge, then open the Invoice table and add to the table which is then used to create the Invoice/report.

Thanks again for all the replies I will try some of the suggestions and report back

Job Date	Customer	Delivery Ticket #	Dump Pit #	Tons	Loads	Customer Rate
28-Jun-22	LRS	1236	115846	0	1	$550.00
28-Jun-22	LRS	1595	115836	14.9	1	$550.00
28-Jun-22	LRS	1596	115920	20.5	1	$550.00
28-Jun-22	LRS	1215	115841	19.1	1	$550.00
28-Jun-22	LRS	1545	115909	0	1	$550.00
27-Jun-22	LRS	1547	115603	20.2	1	$550.00
27-Jun-22	LRS	1593	115593	19.4	1	$550.00
27-Jun-22	LRS	1594	115683	18.6	1	$550.00
01-Jul-22	LRS	1847	116613	20	1	$550.00
01-Jul-22	LRS	1532	116632	20	1	$550.00
01-Jul-22	LRS	1444	116618	20.9	1	$550.00
06-Jul-22		FSC		0	0.23	$1,391.50
						$7,441.50​

View attachment 101706

Ok went with create a table to hold the Sum and the Rate, then append them along with the calculation back into the Invoice Data Table.
I tried it before but was trying to do it in one query. I guess it could not Sum then use that for a second calculation in the same Query.

Thanks All!


Customer Invoice Data Customer Invoice Data

Job Date	Customer	Delivery Ticket #	Dump Pit #	Loading Location	Delivered/Dump Location	Tons	Loads	Customer Rate	Total Tons
7/2/2022​		FSC				0.23​	0​	$1,391.50​	$0.00​

 

[Pat Hartman]

We don't know what the FSC charge is but I'm pretty sure you don't want to store it as a row in the items table.

Typically things like tax or handling or shipping would be calculated and displayed (notice I didn't say stored) in the Order header.

It is almost always wrong to store calculated data and I don't see anything in your description that tells me this should be stored.

What happens if the the details are changed AFTER the FSC is calculated and saved?

 

[The_Doc_Man]

If those are normal Access tables, you cannot do what you are doing without gutting the meaning of the tables. You are clearly using Excel thinking and Access doesn't work that way at all. You can do this in a spreadsheet easily. But Access is not Excel. Data layout and data relationships that are a primary part of the power of Access just don't exist in Excel or any other similar spreadsheet tools.

You can do what you want in a report that could be made to take the sums and do other math in a group footer or report footer. But in a table or a query? No. You are asking for something that, depending on either of a couple of approaches, either is not possible at all or is so improper that the resulting table will be subsequently unusable.