mysql - use Aggregate function with multiple table in Access -

i have 4 tables items,customer,invoice_summary,invoice_details. here want join these 4 tables , sum(invoice_details.item_quntity) , sum(invoice_details.price) specific item_code , specific date range . main columns follows:

invoice_summary :inv_num,inv_date,cus_id,total  items           :item_code,item_name,unit_price invoice_details :inv_num,item_code,item_qty,price customers       :cus_id,cus_name,route 

here have.this return more row(whole itemsnames) need specific item code.could explain going wrong.

select invoice_table.item_code, items.item_name,         (select sum(invoice_table.item_quntity)  (invoice_table inner join invoice on invoice_table.inv_num = invoice.inv_num) ((invoice_table.item_code=[?]) , invoice.inv_date between #3/4/2013# , #6/4/2013#) group invoice_table.item_code) quntity,         (select sum(invoice_table.price)  (invoice_table inner join invoice on invoice_table.inv_num = invoice.inv_num) ((invoice_table.item_code=[?]) , invoice.inv_date between #3/4/2013# , #6/4/2013#) group invoice_table.item_code) price  invoice_table  inner join items on invoice_table.item_code = items.item_code  group invoice_table.item_code, items.item_name; 

the sums you're interested in should given query.

select inv_num, item_code,         sum(item_qty) item_code_qty,         sum(price) item_code_price invoice_details group inv_num, item_code 

it looks date range should found in invoice summary table. looks invoice_summary , invoice_details should joinable on inv_num.

select inv_s.inv_num, inv_s.inv_date, inv_s.cus_id, inv_s.total,        inv_t.item_code_qty, inv_t.item_code_price invoice_summary inv_s inner join (select inv_num, item_code,                     sum(item_qty) item_code_qty,                     sum(price) item_code_price             invoice_details             group inv_num, item_code             ) inv_t         on inv_s.inv_num = inv_t.inv_num inv_s.inv_date between ? , ?; 

and round out, join other 2 tables on keys, , add of columns select clause.

select inv_s.inv_num, inv_s.inv_date, inv_s.cus_id, cus.cus_name, inv_s.total,        inv_t.item_code_qty, inv_t.item_code_price,        items.name invoice_summary inv_s inner join (select inv_num, item_code,                     sum(item_qty) item_code_qty,                     sum(price) item_code_price             invoice_details             group inv_num, item_code             ) inv_t         on inv_s.inv_num = inv_t.inv_num inner join items on items.item_code = inv_t.item_code inner join customers cus on cus.cus_id = inv_s.cus_id inv_s.inv_date between ? , ?;