database - Find Cost from Product ID in MS Access -

i'm trying create calculated field "total cost" of order in ms access. tables related below:

so want able set orders: cost calculate field takes associated each of items 1,2,3,4 , 5 , sums them together.

also, there more flexible way add multiple products each order without having multiple item columns?

thanks in advance!

(i'm new this, apologies if i've missed out critical)

as others have highlighted, there fundamental problem way tables structured. in order answer question suggest adjust table schema first.

i'll therefore answer second question, first:

is there more flexible way add multiple products each order without having multiple item columns?

yes. you'll want use second table store order items in. type of table typically called 'bridge table' , allow store unlimited number of items each order. let's call table order items.

as aside, i'm assuming tables products, products_1, products_2, products_3 , products_4 interfaces same table? rather being 5 tables containing duplicates of product data. i'll come later.

let's restructure tables this:

notice our new bridge table order items. store order items in here, create record each product order contains, making reference product id , associated order id.

an example order

data

orders table:

+----------+------------+------+ | order id | order date | cost | +----------+------------+------+ |        1 | 2015-04-25 | —    | +----------+------------+------+ 

note: ignore cost field now. i'll in bit.

order items table:

+----------+------------+ | order id | product id | +----------+------------+ |        1 |          2 | |        1 |          3 | |        1 |          5 | +----------+------------+ 

products table:

+------------+---------+------+-------+ | product id | product | type | price | +------------+---------+------+-------+ |          1 | hat     | —    |     5 | |          2 | scarf   | —    |    10 | |          3 | gloves  | —    |    12 | |          4 | coat    | —    |    50 | |          5 | boots   | —    |    25 | +------------+---------+------+-------+ 

explanation

in above example data, order placed on 25th april 2015. order 3 products:

• scarf
• gloves
• boots

by utilising order items bridge table, we're able store information products ordered in more flexible format. big advantage of on original database schema you're no longer limited 5 items per order.

now if earlier assumption multiple product tables wrong, new schema give added advantage since you'll no longer need duplicate product data across 5 tables. should never have duplicate data in database - goal have 'single source of truth'. product data should ever stored in 1 place, , never duplicated multiple tables.

this you'd call 'normalised' database schema.

so how cost calculation?

well have admit, i'm no expert ms access. have more experience mysql. i'll try , as possible.

firstly - here based on new table structure proposed above. cost calculation original table structure different (and more complicated!)

one of advantages of using database can dynamically query , perform calculations against data. 1 such calculation total order cost. ms access provide function dynamically calculate total cost order based on prices of associated products. since can calculated dynamically, there no need store value in database.

remember said earlier having 'single source of truth' in database? applies here too. there's no need store calculated total of order, because you'll end having re-calculate every time order item changed. redundant because can calculated dynamically ms access.

an example sql query containing order total this:

select     `orders`.*, sum(`products`.`price`) `cost`     `orders` left join `order items` on (     `orders`.`order id` = `order items`.`order id` ) left join `products` on (     `order items`.`product id` = `products`.`product id` ) group     `orders`.`order id` 

here i'm using sum() function calculate total of product prices associated order. group by operator used lump products associated individual order. , of course it's tied 2 joins: orders -> order items , order items -> products.

in summary - although you're on right track, need change database structure slightly. new database structure normalised , allow perform lookups , calculate total order costs.

hope helps.

---

next steps (optional)

once you've conquered basic problem, might interested in taking things step further.

one thing may have overlooked people can order multiple of same product. taking above example, if wanted 2 scarves in our order rather 1? there's no way store quantity against each product in order.

what you'll want add quantity field order items table. therefore, our new example order items table this:

order items table:

+----------+------------+----------+ | order id | product id | quantity | +----------+------------+----------+ |        1 |          2 |        2 | |        1 |          3 |        1 | |        1 |          5 |        1 | +----------+------------+----------+ 

from can see order contains 2 scarves, 1 pair of gloves, , 1 pair of boots.

calculating total quantity

this new quantity field require change in way calculate total order cost. need quantity × product price, , sum them order total.

this post may of this. you'll want use following (untested!):

select     `orders`.*, sum(`products`.`price` * `order items`.`quantity`) `cost`     `orders` left join `order items` on (     `orders`.`order id` = `order items`.`order id` ) left join `products` on (     `order items`.`product id` = `products`.`product id` ) group     `orders`.`order id` 

notice multiplying price , quantity in order calculate cost field.

changes in product price

another thing consider future whether product prices change. when example order placed, gloves cost 12 ($/£/etc). if product price increases after order placed, no longer have record of price paid @ time order placed.

i feel i'm jumping way ahead now, i'll leave think about. if need should post new question.