Dlookup in query or another method??

[techtamer]

Hi everyone, I'm fairly new to Access, am stuck and hope someone can help me out. I have a query named "stock" that includes the fields "Pallet Type" and "Rate". I also have a table named "Pallet Types" (images of both are attached).

I want to create another field in the "stock" query that will display a value from the "Pallet Types" table based on the value in the "Pallet Type" and "Rate" fields of the query. eg If Pallet Type = Standard and Rate = Low the value displayed in the new field would be $0.540.

I believe I can use Dlookup to achieve this but havent been able to work out how to code it. If anyone can help with this or suggest another method it would be greatly appreciated. Hope this all makes sense

 

[Galaxiom]

A DLookup is like running a separate query for each record in the main query. Very slow. You need to join the tables on Pallet Type and Rate in the query to get good performance.

BTW Avoid spaces or special characters in field and table names.

Many developers also encode things like PalletType as a number and use a lookup table to display the name, usually in a combobox.

 

[techtamer]

Thanks for the info galaxiom. I was considering changing the $ values to just numbers so will go ahead and do that. Will also look into removing spaces in names

 

[Galaxiom]

I was considering changing the $ values to just numbers so will go ahead and do that.

That isn't what I was referring to. Dollar values should be stored as Currency data type. It isn't about simply displaying a currency symbol but about precision of the storage and calculation. Double and Single floating point datatypes can lose precision during certain arithmetic operations.

What I was trying to convey is that rather than storing the words "Standard" or "Low" these are represented by numbers. It is faster for the database to index and find all the 1 values then search a for the word "Standard".