Dlookup, please help

[Anthony88]

Hello,

I am new to Access and am very confused. I read about as much as I could on how access works, and have a pretty decent idea now. But I have spent too much time trying to teach myself access, because this will probably be the only time I need to use it.

Anyways.. I have two tables, "cableconductor" and "eMax Prices". I am trying to take an item number from cableconductor, look it up in eMax Prices and return with a price of the item and replace the current price in that cell in cableconductor. I wrote the following code:

dlookup("[Unit Cost]", "[eMax Prices]", "[Item No] =" & [eMax#])

I placed this expression in the query design section of access. It returned with the correct prices, but in query form. I'm guessing I am doing this in the wrong section, should I be using VBA? How can I use this code or similar code to actually replace the current contents of the cells with the updated prices?



Your help is very much appreciated. Thanks.

[jzwp11]

Anyways.. I have two tables, "cableconductor" and "eMax Prices". I am trying to take an item number from cableconductor, look it up in eMax Prices and return with a price of the item and replace the current price in that cell in cableconductor.

In Access, you would not have price data in 2 different tables but rather only 1. Now if an item can have many prices in eMax prices then the prices should only be in eMax prices which means you should remove the price field in the cableconductor table. Typically, you would have a date associated with each price for an item so that you can pick the most recent one. Having the other dates gives you a history of the prices for an item over time.

I guess we need to understand exactly what type of data is in eMax prices. Is it just a current price for an item? i.e. 1 price record for each item?

Once you update the price in the cableconductor table, what do you plan on doing with the eMax prices table?

[Anthony88]

There will only be one price in the eMax table per item. The eMax table will be updated every month, my plan was to just load the new table each month and replace the old one. In between updates, the table will be untouched. The cableconductor table is used in program which takes the prices from it and uses them. I don't want to have to mess with that program at all, so I'd like the cableconductor section to basically remain the same.

[jzwp11]

OK, if you want to update the value in a field, you have to use an UPDATE query not a SELECT query. Further, since it appears that the two tables have the item number in common, you would join the two tables via that field in the UPDATE query. The query would look something like this:

UPDATE cableconductor INNER JOIN [eMax Prices] ON cableconductor.[Item No] = [eMax Prices].eMaxNo SET cableconductor.currentprice = [eMax Prices].[UnitCost];

The query above assumes the following fields in the two tables

cableconductor
-Item No
-currentprice

eMax Prices
-eMaxNo (equivalent to Item No in cableconductor)
-UnitCost

Remember the update query will only update the records in cableconductor that have a matching record in eMax Prices. If you have new items in eMax Prices that are not in cableconductor, you will have to create and run an APPEND query to get those records into cable conductor.

The append query would look something like this:

INSERT INTO cableconductor ( [Item No], currentprice )
SELECT [eMax Prices].eMaxNo, [eMax Prices].UnitCost
FROM [eMax Prices] LEFT JOIN cableconductor ON [eMax Prices].[eMaxNo] = cableconductor.[Item No]
WHERE (((cableconductor.[Item No]) Is Null));

[Anthony88]

Okay that makes sense. However, I am still new to Access. I hate to be a pain but could you be more specific to where that query goes?

I know how to navigate to the "update query" section, and i'm assuming the first part of that query goes into the "Update to:" field, but besides that I'm lost.

[jzwp11]

The SQL text I showed would be what you would see if you changed from the query design grid view to the SQL view. SQL text is just easier to show in a forum post versus the design grid view and it is what is actually used to execute the query.

To do this in the design grid view, start with a SELECT query. Add both of the tables to the upper pane. Add the join line between the item number fields of both tables (if it is not already there). Select the price field of the cableconductor table in the lower pane. Change the query type from SELECT to UPDATE. Now for the "update to", you want to type in the tablename.fieldname that holds the values you want to use. In your case [eMax prices].[fieldname] (you have to use the square brackets since you have spaces in your table/field names).

You also could go to the SQL view and type the text in directly, but you have to make sure you spell everything correctly.

[Anthony88]

Ohhh wow, that worked beautifully. Thanks so much for all your help, it would have taken me days to figure all that out. Thanks again!

[jzwp11]

You're welcome

[Anthony88]

Last question, promise. Any idea why the first row would be the only one to not update?

[jzwp11]

The most likely cause is that there is no matching record in the emax prices table. The values in the item number field of the two tables must match exactly (no extra spaces or characters)