Query Problem

[Grumpy]

Can someone please help me. I have tables with the following fields:

stockId(PK)
ProductId(FK)
DeliveryDate
Notes
Quantity

ProductId(PK)
Description

OrderId(PK)
ProductId(FK)
Quantity

I am trying to run a query that adds up the two 'quantity' fields but
the numbers don't make sense. If I create a query and only use the 2
quantity fileds I get what I expect. A sum of the quantities for each
field. If I try and subtract one from the other using an expression the
figures dont make sense. Can someone please explain why?

 

[Evi]

Are there any nulls in either of those fields?
Evi

 

[Golfinray]

Try putting these two in a query by themselves and then go to a blank field
and type: My Total:sum[quantity 1]+[quantity 2]
By the way, if they are different quantities, you should rename them
something like q1 and q2 or quantity 1 and quantity 2 because access won't
know which is which. Afetr you do this query, bring just the sum into your
other you other query.

 

[Grumpy]

Are there any nulls in either of those fields?
Evi

Thanks for the reply - If you mean records without any values then yes

--

 

[John W. Vinson]

Thanks for the reply - If you mean records without any values then yes

JIn that case you must use the NZ() function to convert any NULL values to 0.
Any arithmatic expression involving a NULL returns NULL.

 

[Grumpy]

Try putting these two in a query by themselves and then go to a blank
field and type: My Total:sum[quantity 1]+[quantity 2]
By the way, if they are different quantities, you should rename them
something like q1 and q2 or quantity 1 and quantity 2 because access
won't know which is which. Afetr you do this query, bring just the
sum into your other you other query.

Can someone please help me. I have tables with the following fields:

stockId(PK)
ProductId(FK)
DeliveryDate
Notes
Quantity

ProductId(PK)
Description

OrderId(PK)
ProductId(FK)
Quantity

I am trying to run a query that adds up the two 'quantity' fields
but the numbers don't make sense. If I create a query and only use
the 2 quantity fileds I get what I expect. A sum of the quantities
for each field. If I try and subtract one from the other using an
expression the figures dont make sense. Can someone please explain
why?

Thanks for this. I still can't get it to work. When I try and combine
the two 'Quantities' all is Ok until another Order is added. For
example if I have 40 products and then sell i, the query reports the
stock as 79 when it should be 39. Its as if its counting the stock for
every order. Can't get my head round this at all. Can some one please
point me in the right direction. This should be simple.... should it
not?

--

 

[Evi]

Try putting these two in a query by themselves and then go to a blank
field and type: My Total:sum[quantity 1]+[quantity 2]
By the way, if they are different quantities, you should rename them
something like q1 and q2 or quantity 1 and quantity 2 because access
won't know which is which. Afetr you do this query, bring just the
sum into your other you other query.

Can someone please help me. I have tables with the following fields:

stockId(PK)
ProductId(FK)
DeliveryDate
Notes
Quantity

ProductId(PK)
Description

OrderId(PK)
ProductId(FK)
Quantity

I am trying to run a query that adds up the two 'quantity' fields
but the numbers don't make sense. If I create a query and only use
the 2 quantity fileds I get what I expect. A sum of the quantities
for each field. If I try and subtract one from the other using an
expression the figures dont make sense. Can someone please explain
why?

Thanks for this. I still can't get it to work. When I try and combine
the two 'Quantities' all is Ok until another Order is added. For
example if I have 40 products and then sell i, the query reports the
stock as 79 when it should be 39. Its as if its counting the stock for
every order. Can't get my head round this at all. Can some one please
point me in the right direction. This should be simple.... should it
not?

--

Hi Grumps
It is a typical 'null' problem. You may have to not only have to use NZ but
also Val to ensure that you really have got a number.

Val(NZ(YourField,0))


Evi