HELLLLLPPPP PLEASE

  • Thread starter Thread starter Stressed_fi
  • Start date Start date
S

Stressed_fi

I have to make a query, which has to make a calculation. does anyone know how
to do it in a query formatation?

the table is like

OrderNo Product No QtyInStock ReOrderLevel QuantityOrdered
1 1 30 10 2

I need to find how you would take away the qtyinstock by quantity ordered.
 
I have to make a query, which has to make a calculation. does anyone know how
to do it in a query formatation?

the table is like

OrderNo Product No QtyInStock ReOrderLevel QuantityOrdered
1 1 30 10 2

I need to find how you would take away the qtyinstock by quantity ordered.
Click to expand...

To display the Balance....
Add a new column to a query that includes all of the above fields.
Balance:[QtyInStock]-[QtyOrdered]
The above will not change the of the [QtyInStock] field but will show
what is left.
If you wish to change the value of the [QtyInStock] field you need to
run an Update query.
Here is the query SQL.

Update YourTableName Set YourTableName.[QtyInStock] = [QtyInStock] -
[QuantityOrdered]

Change the table name as needed.
 
Thank you.

I have done that now and it is working it out. but we have 3 different
customers ordering 10 of each product so in total it it reaches the stock
limit show below

Order number ProductNo QuanitityOrdered QtyInStock ReOrder
1 3 10 30
10
2 3 10 30
10
3 3 10 30
10

The query is recognising these as seperate enitities insted of one product.

Do you know how to get the query to update? or to stop this from happening?

Thank you

fredg said:
I have to make a query, which has to make a calculation. does anyone know how
to do it in a query formatation?

the table is like

OrderNo Product No QtyInStock ReOrderLevel QuantityOrdered
1 1 30 10 2

I need to find how you would take away the qtyinstock by quantity ordered.
Click to expand...

To display the Balance....
Add a new column to a query that includes all of the above fields.
Balance:[QtyInStock]-[QtyOrdered]
The above will not change the of the [QtyInStock] field but will show
what is left.
If you wish to change the value of the [QtyInStock] field you need to
run an Update query.
Here is the query SQL.

Update YourTableName Set YourTableName.[QtyInStock] = [QtyInStock] -
[QuantityOrdered]

Change the table name as needed.
Click to expand...
 
Back
Top