Update Table based on two tables

[hllary]

I'm trying to create an update query. The update will be based on the QTY, Price, and Unit.

In the code below I've done the first part of the formula. Inside the false section of the IIF statement I need to lookup the value of the Unit in another table then divide the (QTY*Price). The formula is (QTY*Price)/(Unit Multiplier).

UPDATE MasterList_tbl SET MasterList_tbl.[EXTENDED COST (UFC)] = IIf([MasterList_tbl]![QTY]="V","N/A",([MasterList_tbl]![QTY]*[MasterList_tbl]![FY14 AMDF PRICE ]));

So, if the unit is EA (2), then QTY*Price will be divided by 2.

If I did this in excel I would use VLOOKUP. What would I change the code to include the lookup the value in another table? If this operation would be better suited for a vba function, please point me in the direction of some code I could modify to meet my needs.

thanks

 

[bastanu]

dlookup("[Multiplier]","[tblMultiplier]","[Unit] = '" & [Unit] & "'") where tblMultiplier is your second table.
Cheers,
Vlad

 

[The_Doc_Man]

Establish a relationship between the first table's UNIT field and the second table's UNIT field. Then the multiplier is there if you need it and if you don't, who cares... it's just a query. Word of advice... names like EXTENDED COST (UFC) are trouble down the road because of the special characters as part of the name. And you will forever need brackets as long as you have those embedded spaces. Don't forget that when you display something using a query you can use the "AS xxxx" syntax to provide anything you want as the column header regardless of what you have as a field name.

In this query, I'm going to change your names to show you how simple this could look.

UPDATE MasterList_tbl INNER JOIN UnitsTbl ON MasterList_tbl.UNITS = UnitsTbl.UNITS
    SET MasterList_tbl.EXTCOST =
        IIf( MasterList_tbl.QTY = "V",
            "N/A",
             ( ( MasterList_tbl.QTY * MasterList_tbl.FY14AMDFPRICE  ) / Units.Multiplier ) );

By the way, that FY14 AMDF PRICE looks suspicious. Is there an FY15 etc. price as well? Or an FY16 price? Because if so then something isn't properly normalized.

 

[plog]

Lots of issues with this.

1. Dlookups have no business in queries. Instead, you JOIN tables together and then reference fields that way.

2. You don't typically store calculated values. Instead, you calculate them. This means you would not have an [EXTENDED COST (UFC)] field in MasterList_tbl. You would create a SELECT query and calculate that value there and reference that query when you needed to use that value.

3. You don't store values in field names. [FY14 AMDF PRICE] should not be a field. It contains at least 1 piece of information that instead belongs as a value in a field (FY14). You should have a field called [FiscalYear] and then the value 2014 in it to capture this data. Also, AMDF sounds like a type, which means it too would be a value in a field and not in the name.

4. Use the correct data types. If you want to do math with the QTY field it should be a numeric field. You shouldn't be using a character ('V') to represent anything in this field. All the values in this should be numbers and if you need another field to hold whatever "V" represents you should add it to accomodate that bit of data.

5. Use only alphanumeric characters in field names. No spaces, no parenthesis, no nothing but letters and numbers. This just makes coding and querying easier.

So, in conclusion; stop what you are working on, read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), fix the structure of your tables to accomodate data correctly (#2, #3 & #4 above), then read up on queries (https://www.w3schools.com/sql/). Then after all that, post back here any issues you have.

 

[Guus2005]

You shouldn't update your table with process data.
Similar to Excel you would use a VLOOKUP. A VLOOKUP is dynamic it stores a formula and not the actual data.
Use a query to display your proces data. That way the result is dynamic like a VLOOKUP in Excel.

Your table doesn't appear to be normalized since you have quantity and price in the same table.

But working with your data, your query would look something like this:

UPDATE Table1 INNER JOIN Table2 ON Table1.Unit = Table2.Unit SET Table1.EXTENDED_COST = [QTY]*[PRICE]/[table2].[Multiplier];

HTH

 

[hllary]

Establish a relationship between the first table's UNIT field and the second table's UNIT field. Then the multiplier is there if you need it and if you don't, who cares... it's just a query. Word of advice... names like EXTENDED COST (UFC) are trouble down the road because of the special characters as part of the name. And you will forever need brackets as long as you have those embedded spaces. Don't forget that when you display something using a query you can use the "AS xxxx" syntax to provide anything you want as the column header regardless of what you have as a field name.

In this query, I'm going to change your names to show you how simple this could look.

UPDATE MasterList_tbl INNER JOIN UnitsTbl ON MasterList_tbl.UNITS = UnitsTbl.UNITS
    SET MasterList_tbl.EXTCOST =
        IIf( MasterList_tbl.QTY = "V",
            "N/A",
             ( ( MasterList_tbl.QTY * MasterList_tbl.FY14AMDFPRICE  ) / Units.Multiplier ) );

By the way, that FY14 AMDF PRICE looks suspicious. Is there an FY15 etc. price as well? Or an FY16 price? Because if so then something isn't properly normalized.

Thank you for you help. I got it working.

 

[The_Doc_Man]

Glad to help. Don't ignore the comments made by others about structure. If you have not yet explored it, please examine "Database Normalization" if doing a general browser search, or "Normalization" if using the SEARCH on this forum at the top right of the page.