Access Crah on a query,

[MArc Robillard]

Hi all,
I'm new to taht forum so be kind please !

What I need to do is to popullate a list of Order with the total off it.

Some order MAY not have any details.

Sum() is ignoring Null value so I tried this :

SELECT distinct orders.Ord_id, orders.Cst_id,
(select sum(detcmd_qty * detcmd_price) as total from detOrders right join
Orders on Orders.ord_id = detOrders.ord_id) as OrderTotal
FROM orders LEFT JOIN detOrders ON orders.Ord_id = detOrders.Ord_id
WHERE (orders.Cst_id=1)
GROUP BY orders.Ord_id, detOrders.detCmd_id, orders.Cst_id
ORDER BY orders.Ord_id;

the inner select works just fine, and return a total of a specific Order
went I put an ord_id clause.

I need the OrderTotal to show in the list even IF It as no detailOrders.

Any help here would be great !
Marc.
Please responde to NewGroup.

 

[pietlinden]

Hi all,
I'm new to taht forum so be kind please !

What I need to do is to popullate a list of Order with the total off it.

Some order MAY not have any details.

Sum() is ignoring Null value so I tried this :

SELECT distinct orders.Ord_id, orders.Cst_id,
(select sum(detcmd_qty * detcmd_price) as total from detOrders right join
Orders on Orders.ord_id = detOrders.ord_id) as OrderTotal
FROM orders LEFT JOIN detOrders ON orders.Ord_id = detOrders.Ord_id
WHERE (orders.Cst_id=1)
GROUP BY orders.Ord_id, detOrders.detCmd_id, orders.Cst_id
ORDER BY orders.Ord_id;

the inner select works just fine, and return a total of a specific Order
went I put an ord_id clause.

I need the OrderTotal to show in the list even IF It as no detailOrders.

Any help here would be great !
Marc.
Please responde to NewGroup.

Why not just do a totals query with a left join instead of an inner
join? And whats the DISTINCT clause in there for? That woul make
your query TANK!!!

 

[MArc Robillard]

That is what I did, (finially got it to work)
For the distinct, I was Trying about any thing ! ..

the actual solution was :

SELECT Orders.Ord_id,
format(Sum(detOrders.detCmd_Qte*detOrders.detCmd_Price),'standard') AS
TotalGross,
format(Sum((detOrders.detCmd_Qte*detOrders.detCmd_Price)*(100-detcmd_discount)/100),'standard')
AS TotalNet,
Orders.Cst_id, Orders.Ord_Date, Orders.Ord_Accepted, Orders.Ord_Printed,
Orders.Ord_Proforma, Orders.Ord_ProformaPrinted, Orders.Devise_id,
Orders.Taxes_id, Orders.Ord_Comments, Orders.Ord_PrintComment,
Orders.Ord_PaymentTerms, Orders.ord_ShippingFeeBase,
Orders.ord_ShippingFeeExtra
FROM detOrders
RIGHT JOIN Orders ON detOrders.Ord_id=Orders.Ord_id
WHERE(Orders.cst_ID = 1)
GROUP BY Orders.Ord_id, Orders.Ord_Date, Orders.Ord_Accepted,
Orders.Ord_Printed, Orders.Ord_Proforma, Orders.Ord_ProformaPrinted,
Orders.Ord_Comments, Orders.ord_ShippingFeeBase,
Orders.ord_ShippingFeeExtra;