HELP!! Query driving me mad!!

[Saj]

Hi

If anyone can help me with this I will REALLY appreciate it!!!

I have two tables:
Orderline_subform which contains(orderNo, orderlineNo, stockNo, quantityOrd)
SupplierOrderline which contains(supOrdlineNo, supplierOrd, stockNo,
stockRcdQty)

I need queries that will show me the total sum for:
Orderline_subform fields: stockNo, sum(quantityOrd)
SupplierOrderline fields: stockNo, sum(stockRcdQty)

Then I want to have a combo box that lists the stockNo and the remaining
stock which would be: sum(stockRcdQty) - sum(quantityOrd)
or a field that looks at the value of a combo box already in the subform for
orderline which lists all the stockNo, and when the user selects it, it runs
this query and shows in another field on the form the remaining stock.

I have done two separate queries for the sum for orderline and
supplierorderline but when combined the new query does not show all the
stockNo yet they do have some stock, but may not have ords yet.

REALLY REALLY struggling...and need some help...pleeeeeease!!!!

Thanks in advance

 

[Saj]

THANK YOU!!!!!

Never knew you could do that with those lines in design view... keep
learning new things in this discussion forum...

anyway.. the query works now so going to try it out in the form... fingers
and things crossed and hope it works.. ;o)

Thanks again.. you're a star!

 

[Saj]

It works great in the form.

Just have another question now not sure if you may be able to answer this so
will put as separate thread also.

The subform now uses this query that lists in its columns the stockno and
the remaining stock as well as the other bits to it but focusing on first two
in my combo.

Anyway, when I have selected a stock item, I can now query the remaining
stock, in the field quantity being ordered if its greater than the remaining
stock it gives a msg else it creates that orderline and moves to the next
line for a new stockno.

It at this point, when it moves to the next line to the stockno, when I
select the stockno, I want it to have updated the remaining stock.

eg. if the stock item I ordered had 10 remaining stock and I ordered 5, when
i go to the next orderline, if I select that item again it should have 5
remaining stock not 10.

Can anyone help me with this? tried something called the stockNo.requery in
code but did nothing, so not sure if this is what I wanted or whether I did
it wrong.

Please help if possible...

Thanks in advance