Query to divide numbers from one table by another, and insert into a third

[jcuoco78]

Hello,

I have an Access database that I am creating that I need to take values from multiple tables and divide one by another then insert it into a third table. All three tables have a column for part number which will be the driver. I need to take the value of the week column from table1 and divide it by footage column in table2, then insert it into the week column in table3.

So for example, I have part number 123 where I have 30,000 in the week column of table1, 300 in the footage column in table2. I need to enter the value 100 into table 3 in the row that matches the part number 123. I have been able to do this with a single part number but I want the query to loop through every line and update all of them without having to enter every part number 1 by 1. Is there a way to do this?

 

[jcuoco78]

I should have posted this example code:

UPDATE Table3 SET Week = Table1.Week / Table2.Footage
WHERE Table1.PN = Table2.PN AND Table1.PN = Table3.PN;

 

[stumac]

Normally you wouldn't have this as a table, just a query - unless you need the values at a point in time.

The query would be:

SELECT Table1.[Part Number], Table1.Week, Table2.Footage, IIf([footage]=0,0,[week]/[footage]) AS Calc
FROM Table1 INNER JOIN Table2 ON Table1.[Part Number] = Table2.[Part Number]

Just seen that you have the part numbers already in table3:

UPDATE (Table1 INNER JOIN Table2 ON Table1.[Part Number] = Table2.[Part Number]) INNER JOIN Table3 ON Table1.[Part Number] = Table3.[Part Number] 
SET Table3.Week = IIf([footage]=0,0,[Table1].[week]/[footage]);

 

[jcuoco78]

The update code looks like it is going to update the values in table1, is that correct? I want the calculated values to go into table 3 but when I put your code into my query and go to run it it says that it is going to update the data in table1, which in my case is a linked Excel table that I do not want to modify.

 

[stumac]

No table one is part of the selection, the key part is:

[COLOR=#FF0000][B]SET [/B][/COLOR]Table3.Week = IIf([footage]=0,0,[Table1].[week]/[footage]);

 

[xenou]

Been a while but I thought MS Access always update the FIRST Table listed in the join clause ... not quite ansi compliant but there you go.

 

[stumac]

The query I posted will do the same as:

UPDATE (Table3 INNER JOIN Table1 ON Table3.[Part Number] = Table1.[Part Number]) INNER JOIN Table2 ON Table1.[Part Number] = Table2.[Part Number] 

SET Table3.Week = IIf([footage]=0,0,[Table1].[week]/[footage])

In both cases table 3 is updated.

 

[xenou]

yes, works. Yay.

Not clear why the OP's posted query doesn't work - it certainly shouldn't be limited to one part, and there's nothing in it that seems to have anything to do with only updating one part number at a time. Posts #1 and #2 don't seem to fit together.

 

[jcuoco78]

Thanks guys! Got last posted query to work. Not sure why my original didn't work and don't care now, but when I would run it I had to enter the part number and week value to get an output. It wasn't looping through the column and performing them all like I expected it would. I'm pretty new to sql so I didn't know what I was doing wrong.

Also I still am not sure why it is telling me that it will append 110 rows when I only have 44 rows of data to update but it is working so I won't question it until it's a problem!

 

[xenou]

Also I still am not sure why it is telling me that it will append 110 rows when I only have 44 rows of data to update but it is working so I won't question it until it's a problem!

You might want to be concerned about that now, since (if you are right about what should be updated), it means the query is incorrect.
You can turn your update query into a select query and see what is being updated that way.

also technically, APPEND is not the same as UPDATE, so read the message carefully. Appending is adding new rows. Updating is changing something in existing rows.