How do you do a sumif in Access, similar to excel?

[psu_1995]

I'm trying to add a column to my make-table query that will give me the sum total of all rows in the source table which have a matching property.

For example all my orders have multiple lines and I'd like to have this column search the entire table for all the matching orders and then put the same total $$ amount in that column for all rows with the same order number. This is very easy to do in excel using a sumif command and I have played around with the crosstab query in access to no avail for this purpose.

If anyone has some advice to point me in the right direction, I'd appreciate it.

Thanks,

Rob

 

[pbaldy]

The question isn't really "how" but "should". In Access you generally should not store a value that can be calculated from other stored values. You simply calculate it as needed. This relieves you of the task of trying to keep it updated as the other values change.

 

[psu_1995]

Sure - understood. My make-table query exports the new table to excel for the end-user whenever they execute my VBA code (on an as needed basis). I simply want to reduce the amount of excel work they're doing.

What then is the best way to calculate that "as needed" in Access then?

 

[pbaldy]

Best depends on the situation. Using a DSum would work if you just need one. To do them all this query should work:

SELECT OrderNumber, Sum(AmountField) AS TotalOrder
FROM TableName
GROUP BY OrderNumber

 

[psu_1995]

Thanks for the SQL. I've used what you wrote and notice that there's no way to add that to my existing query since by doing so expands all the orders onto multiple lines making the sum do nothing.

It appears I'll have to create another query and have the results of this sum query feed back into a second query to list the total order amount next to every line in an order in order to get the true equivalent of an excel sumif - unless there's a cleaner way?

 

[pbaldy]

You should be able to add it by joining on order number. In other words, that query would stand alone, then you'd add it to other queries (like a table) with a join on order number, which should let you see the total. You could also use a subquery or DSum within your existing query, but this would probably perform better on large datasets.

 

[psu_1995]

Exactly what I ended up doing. Thanks for the feedback and for helping me get out of the Excel mode of thinking!

Rob