dsum issue

[Brian P.]

Hi there - I have a rather long qry involving inventory management.
I'm playing around with a second qry, extracting three fields:
Transaction Type
(buy/sell), Item Quantity (+/-), and Item Lot (LotID, wihch
corresponds to a
date stored on a different table).

Is there some kind of Group By or DSum function that can sum up
quantities of
identical LotID's? So far this has failed:

LotQty: DSum("[ItemQuantity]","[qryActivity]","[TransType]"<>2) (2
is an internal transfer)

The end goal is to say LotID 1 has a net quantity of 4, LotID 2 " " of
zero, etc... where one item from the original qry, say bread, has
multiple LotID's.


Thanks,
Brian

 

[MGFoster]

Hi there - I have a rather long qry involving inventory management.
I'm playing around with a second qry, extracting three fields:
Transaction Type
(buy/sell), Item Quantity (+/-), and Item Lot (LotID, wihch
corresponds to a
date stored on a different table).

Is there some kind of Group By or DSum function that can sum up
quantities of
identical LotID's? So far this has failed:

LotQty: DSum("[ItemQuantity]","[qryActivity]","[TransType]"<>2) (2
is an internal transfer)

The end goal is to say LotID 1 has a net quantity of 4, LotID 2 " " of
zero, etc... where one item from the original qry, say bread, has
multiple LotID's.

DSum() only returns one value. Obviously, you are trying to do
something else. Try posting your query's complete SQL so we can
determine a better solution.

 

[Tom van Stiphout]

On Thu, 11 Jun 2009 08:57:37 -0700 (PDT), "Brian P."

That's an invalid DSum statement, or at least an unintended one. You
probably meant:
LotQty: DSum("ItemQuantity","qryActivity","TransType<>2")

(no need to use brackets for non-funny names)

-Tom.
Microsoft Access MVP