sumif array type function?!?!

[Guest]

Hi

I need help. I've been trying to get my brains around this one. I'm trying to use access for data-analysis and while I can work it out in excel, i'm not sure how i can resolve it in access (which is where i need it given the file sizes and i want to run it off a database).

I have the following information
Column A : Delivery Dat
Column B : Orde
Column C : Inventory Item Numbe
Column D : Quantity Issue

My problem is that for any one order and inventry item, there can be multiple deliveries, i want the accumultive total of these based on the delivery date

I want to calcuate : another Column 'E': which equals... Sum of Quantity Issued (Column D) for all incidents where Column B and C are the same and where Column A (Delivery Date) is less than or equal to Column A.

ie., My calculated Column 'E' is a cumulative total of Column D based on the date and matching criteria of Columns B ad C

How do i do this

Thank

 

[MacDermott]

Hello, Marcus!

You can do this using a Totals query -
you'll find an introductory explanation in the Access Help file.

If your fields are actually named [Column A], [Column B] etc, I'd suggest
giving them more meaningful names, but for now I'll use yours.
The SQL for your query should come out looking a bit like this:
SELECT [Column C], [Column B], SUM([Column D]) AS TotalQty FROM MyTable
GROUP BY [Column C], [Column B]

HTH
- Turtle

Hi,

I need help. I've been trying to get my brains around this one. I'm

trying to use access for data-analysis and while I can work it out in excel,
i'm not sure how i can resolve it in access (which is where i need it given
the file sizes and i want to run it off a database).

I have the following information:
Column A : Delivery Date
Column B : Order
Column C : Inventory Item Number
Column D : Quantity Issued

My problem is that for any one order and inventry item, there can be

multiple deliveries, i want the accumultive total of these based on the
delivery date.

I want to calcuate : another Column 'E': which equals... Sum of Quantity

Issued (Column D) for all incidents where Column B and C are the same and
where Column A (Delivery Date) is less than or equal to Column A.

ie., My calculated Column 'E' is a cumulative total of Column D based on

the date and matching criteria of Columns B ad C.

 

[Guest]

Thanks MacDermott

Yes, this will give me a grouping based on order number (Column B) but I wish to include the date as well and have a culmulative total up to that date. I'm not sure your query does this

ie., i wan

Delivery Date = 23/03/03, Order# = 1002, Inventory_Item = 001, Quantity = 3, Total Quanity =
Delivery Date = 25/03/03, Order# = 1002, Inventory_Item = 001, Quantity = 2, Total Quanity =
Delivery Date = 25/03/03, Order# = 1002, Inventory_Item = 002, Quantity = 10, Total Quanity = 1
Delivery Date = 28/03/03, Order# = 1002, Inventory_Item = 001, Quantity = 10, Total Quanity = 1

Where Total Quantity is the caluculated field, and in this instance all Order#'s are the same (this is not the normal case)

No my, columns are not named A,B C etc, but i tryed to make a similification of my problem

Any other ideas

Thanks
Marcu

----- MacDermott wrote: ----

Hello, Marcus

You can do this using a Totals query
you'll find an introductory explanation in the Access Help file

If your fields are actually named [Column A], [Column B] etc, I'd sugges
giving them more meaningful names, but for now I'll use yours
The SQL for your query should come out looking a bit like this
SELECT [Column C], [Column B], SUM([Column D]) AS TotalQty FROM MyTabl
GROUP BY [Column C], [Column B

HT
- Turtl

Hi

trying to use access for data-analysis and while I can work it out in excel
i'm not sure how i can resolve it in access (which is where i need it give
the file sizes and i want to run it off a database)

Column A : Delivery Dat
Column B : Orde
Column C : Inventory Item Numbe
Column D : Quantity Issue

multiple deliveries, i want the accumultive total of these based on th
delivery dateIssued (Column D) for all incidents where Column B and C are the same an
where Column A (Delivery Date) is less than or equal to Column A

 

[MacDermott]

What you are asking is rather easily accomplished in a report, using Sorting
and Grouping, and the RunningSum property of your [Delivery Date] textbox.

Since the results of such a query will not be updatable in any case, why not
go with a report?

- Turtle

Thanks MacDermott,

Yes, this will give me a grouping based on order number (Column B) but I

wish to include the date as well and have a culmulative total up to that
date. I'm not sure your query does this.

ie., i want

Delivery Date = 23/03/03, Order# = 1002, Inventory_Item = 001, Quantity = 3, Total Quanity = 3
Delivery Date = 25/03/03, Order# = 1002, Inventory_Item = 001, Quantity = 2, Total Quanity = 5
Delivery Date = 25/03/03, Order# = 1002, Inventory_Item = 002, Quantity = 10, Total Quanity = 10
Delivery Date = 28/03/03, Order# = 1002, Inventory_Item = 001, Quantity = 10, Total Quanity = 15

Where Total Quantity is the caluculated field, and in this instance all

Order#'s are the same (this is not the normal case).

No my, columns are not named A,B C etc, but i tryed to make a similification of my problem.

Any other ideas?

Thanks,
Marcus


----- MacDermott wrote: -----

Hello, Marcus!

You can do this using a Totals query -
you'll find an introductory explanation in the Access Help file.

If your fields are actually named [Column A], [Column B] etc, I'd suggest
giving them more meaningful names, but for now I'll use yours.
The SQL for your query should come out looking a bit like this:
SELECT [Column C], [Column B], SUM([Column D]) AS TotalQty FROM MyTable
GROUP BY [Column C], [Column B]

HTH
- Turtle

Hi,

I'm
trying to use access for data-analysis and while I can work it out in excel,
i'm not sure how i can resolve it in access (which is where i need it given
the file sizes and i want to run it off a database).

Column A : Delivery Date
Column B : Order
Column C : Inventory Item Number
Column D : Quantity Issued

be
multiple deliveries, i want the accumultive total of these based on the
delivery date. Quantity
Issued (Column D) for all incidents where Column B and C are the same and
where Column A (Delivery Date) is less than or equal to Column A. based on
the date and matching criteria of Columns B ad C.