DLOOKUP help

[db123]

I have 2 tables:

Table1 : PriceList
Table2 : Sale

Table1 contains 2 fields: ProdID and UnitPrice
Table2 contains 1 field: ProductID


Table1:

ProdID------UNitPrice
A----------------1
B----------------2
C----------------3
D----------------4


E----------------5
F----------------6
G----------------7
H----------------8
I----------------9
J----------------10

Table2:

ProdID------UNitPrice
D---------------10
E---------------20
F---------------30
J---------------50
Z---------------60

Expected result in query:

ProdID------UnitSold------UnitPrice---------ExtPrice
D---------------10-------------4---------------$40
E---------------20-------------5---------------$100
F---------------30-------------6---------------$180
J---------------50-------------NA---------------NA
Z---------------60-------------10--------------$600


This is what I have for the DLOOKUP function in the ExtPrice field of the query and it's not working:

UnitPrice: DLookUp("[UnitPrice]","PriceList","UnitPrice=" & [UnitPrice])

Appreciate if someone can point out what I am doing wrong.

[ssanfu]

Actually, if you set up the relationship correctly, you don't need to use DLookup(). (see attached table)

Or, if the unitprice field in table Sale is entered, you would just multiply the unitsold and UnitPrice fields.

[db123]

ssanfu,

Appreciate your help. I understand that if I linked the tables together and establish the relationship, I could get the same result. However, I am trying to understand the DLOOKUP() function, and have not been successful. It's been very frustrating.
Could you help me build the DLOOKUP function in the UnitPrice field in the query? would greatly appreciate it.

[June7]

Did you mean table 2 has ProdID and UnitSold?

Wrong criteria in the DLookup().

Assuming ProdID is text field:

UnitPrice: DLookUp("[UnitPrice]","PriceList","ProdID='" & [ProdID] & "'")

[db123]

Jun7,

Appreciate your help. It works now. I have been stuck with this function for the last two days. What did I do wrong? Should the criteria be written differently if the field being looked up is a text field vs a numeric field?

[ssanfu]

The formula that June gave you works....

It is very confusing (to me) having the same field names it both tables. So I renamed some of the fields:
Maybe the attached file will help...

[db123]

ssanfu,
I am a newbie in Access so my approach may not make much sense to you. Maybe that's why it's confusing. I believe the way you set up the tables is much better. I mainly use Excel for everything, but it's becoming more and more difficult to manage as the file size gets larger. so I am trying to convert some of the large Excel files into Access and hoping that will resolve the Excel performance issue. It's been a pretty steep learning curve for me.
Thanks again for your help.

[ssanfu]

Aha... the light has pierced the void.

Converting from Excel to Access is at best a difficult journey. You have to forget anything you knew/know about Excel.
Here is a tutorial (one of many) on Access. http://www.rogersaccesslibrary.com/forum/forum46.html

Work through them. Then grab pencil and paper and try and convert what you have in Excel to Access tables.
Ask questions.
Post your table structure here for suggestions/ review...

Welcome to the forum and good luck with your project.

[June7]

Table2 had ProdID and you need to pull the UnitPrice from Table1 that corresponds to the ProdID. This means ProdID is the search criteria.

Parameters for text fields need apostrophe delimiters. Use # for date field parameters. Nothing for number fields.

[db123]

Thank you for the recommendation and guidance. I will go through the tutorial.

[db123]

June7-Thank you for the explanation.