Sumif equivalent

[Martyh]

Is there a =Sumif (Excel) equivalent in access? for example, could i use dsum()?

Your help is needed!

 

[pbaldy]

DSum() would be the equivalent, yes. You can also use SQL if the situation warrants it.

 

[Banana]

Depends on how you want to sum things.

Few possible techniques:

1) Use GROUP BY to sum only for groups with a certain factor in common. (e.g. you may want to sum populations for every states; in this case you group by states and sum the populations)

2) Use WHERE clause to filter out the records. So using the example in #1, suppose we have entries for populations recorded on a certain day and we want to look at most current population data, we would use WHERE to ensure that only most recent population data is included in the summing.

Note: We can also accomplish similar by doing joins or performing UNION to express several different way of summing.

3) For conditional summing, we can nest a Iif() block in the Sum() function. Suppose we want to do a rounding off by 1,000 or 1,000,000 depending on population being given, we can do this:

Sum(Iif([Population] < 1000000, (Round([Population] * 0.001)*1000), (Round([Population] * 0.000001)*1000000)))

4) As an alternative to #2, we can use DSum(), which usually is great for a control on form or maybe inside a VBA procedure. It can be used inside a query but usually native SQL as indicated in #2 can be used to better effect.

It should be noted that while it's possible to use #3 technique in lieu of #1 and #2, it is not as efficient.

I hope this helps.

 

[Martyh]

Thanks guys... i need to start thinking in a database way again! (i've been on an excel project for a month) In my case the group by should do the trick!!

Thanks again.

Do you guys know the answer to my other question?

My problem is very similar to Vickiwells(vickiwells Posted:Subforms on tab control @ 03-14-2001, 03:17 PM)

However there is a one to many relationship in within the form ie The "One" side forms the Main part of the form, while the "many" side forms the subforms portion. Because there was too much information to place on the screen, I split the "many" side in two and tabbed the subforms.

What I need is for the two tabbed pages to show the same record, without having to tab down separately on the two pages.

I've had a brain cramp trying to figure this out!