How to make an expression look into the appropriate value

[clippertm]

Hello,

Let's say there is one simple table, table1 like the below, that stores the type of fruit and their price:

type ; price
apple ; 10
orange ; 5
pear ; 15

Then there is another table, table2, that stores simple transactions (this table looks into table1 to get what type of fruit it is through a combo box):

fruit ; quantity sold
apple ; 7
orange ; 10
apple ; 6
pear ; 2

Now I want to make a query that shows the fruit, the quantity sold, and also the profit, which is: price * quantity sold. My question is: how can I make an expression look for the right price? I would use the lookup function in Excel, but in Access I don't know what would be the most efficient way.

Something like:

IF fruit = X then look for X's price in table1 and multiply it by quantity sold from table2.

The query should look like this:

fruit ; quantity sold ; profit
apple ; 7 ; 70
orange ; 10 ; 50
apple ; 6 ; 60
pear ; 2 ; 30

Do you have any idea?

Many thanks,

 

[Galaxiom]

Don't use an expression to get the price.
Just join the tables on the fruit fields in the query and the price used will be the right one.

 

[boblarson]

And do NOT use lookups at table level like you are doing. See here for why.

Lookups should be done on Forms and not in tables.

 

[G81]

If you decide to continue down the route you suggested, create a query in design view and join your "type" and "fruit" fields from each table. Then group by "type" on your query, and select sum for "qty" and "total price" (price * qty).

This will give you:

type - qty - total
apple - 13 - 1.30
orange - 10 - 0.50
pear - 2 - 0.30


the sql is something like this:

SELECT tblprice.type, Sum(tbltransaction.qty) AS SumOfqty, Sum([price]*[qty]) AS total
FROM tblprice INNER JOIN tbltransaction ON tblprice.type = tbltransaction.fruit
GROUP BY tblprice.type;

 

[clippertm]

Thank you all for your replies, you are very helpful.

I am pausing this JOIN work as I read The Evils of Lookup Fields in Tables and I need to redesign the database.

 

[Galaxiom]

Don't panic about the table level lookups. They are easily removed later without affecting the design of anything else. Evil is too strong a word for table level lookups and that particular article can induce unnecessary panic.

I actually set them up during the early part of the design because they automatically create combos with the right setting when the field is added to a form. I usually delete the table lookup when I split the database.

Once the basic forms are in place it is easier to copy and paste controls from one form to another because this bring the whole formatting of the control.