Sumifs for access

[MrJukas]

Hello guys,
I wonder how to do a sumifs on access.

I noticed dsum() could be a good solution. Do you agree? Any other suggestions?

What I need to do is this:
I have a list of items, quantities and dates. I want to add a field that calculates at every row the sum of the quantities for the same items, with the dates older than the one showed.

Any suggestions is welcome. Thanks

[Perceptus]

Sounds like Dsum is what you need.

[InsuranceGuy]

Sounds like Dsum is what you need.

The only caution is that DSUM can be very slow in some situations. If it becomes a problem, you'll want to look into using queries that use an approach of:

OutputFieldName: SUM(IIF(X=Y,[SourceField],0))

[MrJukas]

What about calculating time?

I tried to run it, but in takes for ages!
I have 20'000 lines initially, but because I have some criterias it goes down to 7000.
I might have to break it down in two queries and then doing the dsum on the second query.

Any comment? Dsum on 20000 lines, does it take a lot of time?

[MrJukas]

Thank you InsureancrGuy,
Would you mind be more specific on your formulas?
Sum(iif([item]=[item] and [Dates]>[dates ], [quantity ],0))
I am not sure it will work. Does this formula look at every line and sum the quantities with the same items and dates older than the reference date?

[Perceptus]

I performed a Dsum on 32000 Records using a linked table across a network. Took about 3 seconds.

[MrJukas]

Ok, weird. It took me a lot of time.

[MrJukas]

Hi, dsum() works. But as soon as I add a criteria it slows down.
I broke the query down and it works faster.

Thanks