DSum?

[stapuff]

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

 

[Norie]

Kurt

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

Sum Shipped and group by the other fields.

 

[stapuff]

How do you get it by the month of TRANSACTION_DATE

 

[Norie]

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]

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]

I got it solved. I appreciate your guidance Norie. You are doing an excellent service.

 

[NumbersMax]

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]

NumbersMax -

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

Kurt