473,322 Members | 1,614 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to update a field based on another fields data

I have a table with the following fields:

Line - text
PartNo - text
Price1 - double
Price2 - double
Price3 - double

I need to look at 'Line' and based on it's data update 'Price2' with a calculaton based off 'Price1'.

There are 45 'Lines' to look at and 124,700 records to update

for example:
if Line = 01 then Price2 = Price1 / .634
if Line = 43 then Price2 = Price1 / .663
etc

I can write 45 update queries and create a macro to execute them, but there has to be a more effecient way to accomplish this...
Oct 7 '10 #1
5 2629
NeoPa
32,556 Expert Mod 16PB
It's possible of course - You'd use an UPDATE query - but your two lines of example with no explanation doesn't even give us a starting point.

How does one determine what the divisor should be if one knows the line number?
Oct 7 '10 #2
nico5038
3,080 Expert 2GB
When the Line determines the multiplication factor, you should create a table with the Line value as key and the factor to be used.
Now a JOIN by the Price1 will get the needed factor and one query will do.

Remains the question why the price2 is updated. In general that's against normalization rules...

Nic;o)
Oct 7 '10 #3
My apologies. I was trying to keep my post as simple as possible and apparently over simplified.

Every week I get a .csv file with the fields listed above. The field 'Line' is pre-populated with a number between '01' and '60'. Price2 and Price3 are populated with 0.00 and Price1 is populated with an actual price (all are 'double' fields). Each 'Line' has a corresponding margin that I use to calculate Price2 using Price1. IE: if Line = 01 then Price2 = Price1 / .634 (.634 is a predetermined margin - not currently in a table, but easily enough added to one). I need to update all 124,700 records. Some 'Lines' will have as few as 30 records while others will have as many 20,000.

I export this file to another application for pricing purposes - hence the need to calculate and populate Price2 (and Price3 eventually as well, but one hurdle at a time).

My goal is to get the original .csv file to a flat .txt file with all the pricing fields updated.

did that muddy the waters enough?
Oct 8 '10 #4
nico5038
3,080 Expert 2GB
Clear, so I would go with the proposed additional table and the JOIN by Line to calculate price2.

Nic;o)
Oct 9 '10 #5
NeoPa
32,556 Expert Mod 16PB
If the multiplication value is not determinable by the value of the [Line] field then a table would be called for (Far better than complicated code with multiple Ifs or even a Select Case).

If you have a table anyway, doing [Price3] would involve little more than a single extra field in there for it.
Oct 9 '10 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Mike | last post by:
Here is my situation; I have two tables in a MS-SQL DB. One table with dollar amounts and service codes. I have a second table that I want to move some information into from the first table. The...
7
by: Cruisemate | last post by:
I have a table with numerous fields including timeIn and timeOut field. I need to find out how many people were clocked in during each operating hour. Can I run a query that will allow me to...
1
by: Tony | last post by:
Hi, I have an ID text field that is composed of two alphabetical letters and the rest are numbers. This field is also a primary key. In this same table, I want to separate this field into two...
2
by: Brett | last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the...
2
by: ruthboaz | last post by:
Hi, I am exporting a table with data to an excel file.. Some fields are empty in the exported file while the others and the key field, has values. The empty field(s) in excel, will be entered...
13
by: shookim | last post by:
I don't care how one suggests I do it, but I've been searching for days on how to implement this concept. I'm trying to use some kind of grid control (doesn't have to be a grid control, whatever...
1
by: abprules | last post by:
Can somebody help me with this scenario? I want to update a date field when another field has a certain value entered. It is something like this: When the PDetType field ( from tlkpERegStatus...
4
by: mmanojkumar | last post by:
Sir, I have a recordset "ADODC_path.recordset" which takes input from two tables When the field values are put into the textboxes and then edited and updated back into the tables, using the...
1
by: Kombz | last post by:
Please help -- been away from access for seven years and trying to set up a field that will return the data based on another field. i.e.: if they choose underwriter in the typeofdonation field,...
4
by: whamo | last post by:
I have the need to populate a field based on the selection in a combo box. Starting out simple. (2) tables tbl_OSE_Info and tbl_Input; tbl_OSE_Info has three fields: Key, OSE_Name and OSE_Wt...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.