Retrieve data from another table based on criteria

[Guest]

Condition:
I have two tables: “Price†and “Transactionâ€
In the Price Table, I have these fields: Date, Price1, Price2
In the Transaction Table, I have these fields: TranNum, TranType, TranDate,
Price

When I update the Transaction table using Form, for the Price field, I'd
like to retrieve the value from Price Table based on the following criteria:
If TranType = “S†and TranDate=Price.Date, use Price.Price1
Else if TranType = “B†and TranDate=Price.Date use Price.Price2

Question:
Q1. Should I use Expression or VB code?
Q2. How to write the query?

 

[Michel Walsh]

Hi,



SWITCH( TranType = "S" and TranDate=Price.Date, Price.Price1,
TranType = "B" and TranDate=Price.Date , Price.Price2,
True, Null )



Note that I added a third case, "catch all else", which return Null.

You may have to prefix some field name by the table name if the field name
origin is ambiguous (from the two tables)

As far as the exact syntax, in the FROM clause, since you didn't mention
which record of Price is to be "match" with which record or Transaction, I
cannot supply the specific. I assume table Transaction has more than a
single record, so, which record has to be use with each of the record from
Price?



Hoping it may help,
Vanderghast, Access MVP