DSum?

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
I have a query with the following fields:

WAREHOUSE
PART_NUMBER
Shipped
TRANSACTION_DATE

I am looking for the following -
sum of "Shipped" by WAREHOUSE,PART_NUMBER, & month of TRANSACTION_DATE.

I thought Dsum would do the trick, however, I am very new to Access and really trying to get a grasp of it. Looking for some suggestions.

Thanks,

Kurt
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Kurt

Couldn't you just use an aggregrate query (View>Totals)?

Sum Shipped and group by the other fields.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Well for that you would need to add an expression that extracts the month from the date.

TheMonth:Month([TRANSACTION_DATE])

Note this will extract the month number eg 1 for January.

There is another function in Access, MonthName, which will extract the month text.

But one problem with using that might be sorting.
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
Norie -

I have already added the 5th column expression which gives me the month #.
I now have the original 4 columns I posted + the month column I added. All are "group by" except for the Shipped which is "sum".

I am still getting multiple rows for the same part # in the same month.

Thanks,

Kurt
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
I got it solved. I appreciate your guidance Norie. You are doing an excellent service.
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
It is probably the Transaction Date field being group by. If you have more than one ship date in the same month for a part, you will have the same number of entries for that part as shipped that month.

To Group by Warehouse, Part # and month shipped and Sum the shipped quantity, you must remove the Transaction date column or at least change it to Where (which unchecks the show checkbox)

Max
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
NumbersMax -

You hit the nail on the head. That is exactly what I did.

Kurt
 

Forum statistics

Threads
1,181,659
Messages
5,931,273
Members
436,786
Latest member
Deniel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top