HELLLLLPPPP PLEASE

[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.

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the Query Design Grid in a blank column you'd put something like this
in the Field: cell:

Remainder: Nz([QtyInStock],0)-Nz([QuantityOrdered],0)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSd9jeIechKqOuFEgEQLsSQCeOi9q7dIoPwVQ46Z+6lJY+JsYRbgAoNYK
YGS22PKDM/IULWat40bZoOYW
=atqD
-----END PGP SIGNATURE-----

 

[fredg]

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.

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.

 

[Stressed_fi]

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

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.

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.