Multiple Dlookup in the same query ??

[jeanulrich00]

Hi

I have a table containing 8 fields "allocation", "allocationCU"
"transport" "transportCU" "airfare" "airfareCU" "meal" and "mealCU"

"allocation" "transport" "airfare" and "meal" are fields with numbers
"allocationCU" "transportCU" "airfareCU" and "mealCU" are text field
and contains only 3 digit (name of currency) CAD (canadian dollar)
USD (us dollar) YEN (yen) , etc .....
I have a table "TblCurrencies" containing 2 fields "Currency" (USD,
CAD, YEN, etc ) and CurrencyRate (1.000 for USD, 1.400 for CAD, 5000
for YEN, etc...

I am building a query where I could add the 8 fields of the first
table + 4 others that would represent the currency rate for the 4
fields CU

So the result would look like this

allocation = 400 allocationCU = USD AllocationRate = 1.000
transport = 100 transportCU = CAD TransportRate = 1.400 airfare =
2000 airfareCU = YEN AirfareRate = 5000 meal = 300 mealCU = YEN
MealRate = 5000

The 400 for allocation, 100 for transport, 200 for airfare and 300 for
meal are just exemple

I am not sure if i can use dlookup, i have try but no result esch time
access tell me that it cannoy find the fiels ( in the criteria)

thanks for helping

 

[Ken Snell [MVP]]

First, your table structure is not normalized. You're using field names to
identify variations of a single data entity -- namely, expenses.

You need a table to hold the expenses for the parent records, and then
relate the child table to the parent table using a field in the child table
that contains the value of the primary key field that is the record in the
parent table to which that child record belongs.

The child table would have these fields:
ChildID ( autonumber ) Primary Key
ParentID ( same datatype as parent table's primary key)
ExpenseType
ExpenseCurrency
ExpenseAmount


Then, if you do that, the query that you use will be very simple, as you
just do a sum on the ExpenseAmount field for a given value of ParentID.

Otherwise, because you didn't show us info about the DLookup function
structure that you've tried or that you want to try to use, it's difficult
for us to understand what your question is and what the answer might be. Why
do you think DLookup will give you the info that you seek?