how can I do a sumif array type function?

[Guest]

Hi

I need help. I've been trying to get my brains around this one. Its inheriently simple in my head, but i cant seem to write a query. 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

 

[Tom Ellison]

Dear Marcus:

In the SELECT portion of the query, calculate this value (your new
Column 'E' using a subquery:

E: (SELECT SUM([Quantity Issued]) FROM YourTable T1 WHERE T1.[Delivery
Date] <= T.DeliveryDate AND T1.Order = T.Order AND T1.[Inventory Item
Number] = T.[Inventory Item Number])

In the above, change YourTable to the actual name of the table.

You must also alias YourTable in the main table to be T.

If you have any trouble putting this together to make it work, please
post your existing query omitting the now column E and I will try to
add that to your SQL.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Thanks Tom

Yes I still need your help, I can read your query, and it looks like the right query, but I’m a little lost with T1 and setting up an alias for T. Am I referring to the original table in two different ways or is T referencing this query? How do I set up an alias? I’m assuming that by stating “FROM YourTable T1 WHERE†is renaming ‘YourTable†as T1 in the remainder of the statement. I haven’t done that before

Ok, names are slightly different, the SQL I have for the first four columns is

SELECT DISTINCTROW [DataExtract01].creation_date, [DataExtract01].issue_req_no_s, [DataExtract01].stock_code_s, [DataExtract01].quantity_iss_
FROM [DataExtract01
GROUP BY [DataExtract01].creation_date, [DataExtract01].issue_req_no_s, [DataExtract01].stock_code_s, [DataExtract01].quantity_iss_s

Where the names can be described as

Table Query Runs off is called : DataExtract0
creation_date = delivery date (Column A
issue_req_no_s = Order (Column B
stock_code_s = Inventory Item Number (Column C
quantity_iss_s = Quantity Issued (Column D

Name of Query is : Query

The outcome I want is something like

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)

Thanks again

Marcus

----- Tom Ellison wrote: ----

Dear Marcus

In the SELECT portion of the query, calculate this value (your ne
Column 'E' using a subquery

E: (SELECT SUM([Quantity Issued]) FROM YourTable T1 WHERE T1.[Deliver
Date] <= T.DeliveryDate AND T1.Order = T.Order AND T1.[Inventory Ite
Number] = T.[Inventory Item Number]

In the above, change YourTable to the actual name of the table

You must also alias YourTable in the main table to be T

If you have any trouble putting this together to make it work, pleas
post your existing query omitting the now column E and I will try t
add that to your SQL

Tom Elliso
Microsoft Access MV
Ellison Enterprises - Your One Stop IT Expert


On Mon, 1 Mar 2004 18:16:05 -0800, marcu

 

[Guest]

OK
I now have
SELECT DISTINCTROW T.creation_date, T.issue_req_no_s, T.stock_code_s, T.quantity_iss_s, (SELECT SUM(quantity_iss_s) FROM [Data_Extract] T1 WHERE T1.[creation_date] <= T.creation_date AND T1.issue_req_no_s = T.issue_req_no_s AND T1.[stock_code_s] = T.[stock_code_s]) AS
FROM [Data_Extract] AS T INNER JOIN [Data_Extract] AS T1 ON (T.stock_code_s = T1.stock_code_s) AND (T.issue_req_no_s = T1.issue_req_no_s) AND (T.creation_date = T1.creation_date
GROUP BY T.creation_date, T.issue_req_no_s, T.stock_code_s, T.quantity_iss_s

I'm trying to see if this works... but it is taking forever to calculate. I suspect, i may have made things too complicated. Are my joins making sense

Thanks
Marcus

 

[Tom Ellison]

Dear Marcus:

I'm going to omit the DISTINCTROW for now. You may add it back if you
find you need it. Similarly, I didn't see a need for any GROUPing,
and removed that. Here's what I come up with:

SELECT creation_date, issue_req_no_s, stock_code_s, quantity_iss_s,
(SELECT SUM(quantity_iss_s) FROM YourTable T1
WHERE T1.creation_date <= T.creation_date
AND T1.issue_req_no_s = T.issue_req_no_s
AND T1.stock_code_s = T.stock_code_s)
FROM DataExtract01 T
ORDER BY creation_date, issue_req_no_s, stock_code_s;

Please let me know how this works for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Thanks Tom,

Yes I still need your help, I can read your query, and it looks like the right query, but I’m a little lost with T1 and setting up an alias for T. Am I referring to the original table in two different ways or is T referencing this query? How do I set up an alias? I’m assuming that by stating “FROM YourTable T1 WHERE” is renaming ‘YourTable” as T1 in the remainder of the statement. I haven’t done that before.

Ok, names are slightly different, the SQL I have for the first four columns is:


SELECT DISTINCTROW [DataExtract01].creation_date, [DataExtract01].issue_req_no_s, [DataExtract01].stock_code_s, [DataExtract01].quantity_iss_s
FROM [DataExtract01]
GROUP BY [DataExtract01].creation_date, [DataExtract01].issue_req_no_s, [DataExtract01].stock_code_s, [DataExtract01].quantity_iss_s;


Where the names can be described as:

Table Query Runs off is called : DataExtract01
creation_date = delivery date (Column A)
issue_req_no_s = Order (Column B)
stock_code_s = Inventory Item Number (Column C)
quantity_iss_s = Quantity Issued (Column D)

Name of Query is : Query1


The outcome I want is something like:

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).

Thanks again,

Marcus.

----- Tom Ellison wrote: -----

Dear Marcus:

In the SELECT portion of the query, calculate this value (your new
Column 'E' using a subquery:

E: (SELECT SUM([Quantity Issued]) FROM YourTable T1 WHERE T1.[Delivery
Date] <= T.DeliveryDate AND T1.Order = T.Order AND T1.[Inventory Item
Number] = T.[Inventory Item Number])

In the above, change YourTable to the actual name of the table.

You must also alias YourTable in the main table to be T.

If you have any trouble putting this together to make it work, please
post your existing query omitting the now column E and I will try to
add that to your SQL.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

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

 

[Guest]

I like your version it looks much cleaner than mine and still seems to work. But i still have problems with the generation time. I'm trying to work out how to reduce this. I have an extract from the server for one months data, i will need to process more when the query is ready. However I note that I have 172272 records for the selected month, of which only 382 have mulitple issues created. So this function really only needs to operate for these 382 records. Should I be using a count function (based on grouping of issue_req_no_s & stock_code_S and excluding creation_date) first and then doing some kind of iff statement if count > 1

I only know how to do this by creatin multiple queries, i'm not sure it would give the speed advantage required and certainly makes the database a lot more complicated. Can this be done through SQL instead

The speed is an issue, it does not seem possible to say see the last record in the set with out the machine grinding away forever and im' not sure it is makin any process. Just flicking with the page down button takes a few minutes to generate. The data base is on my hard drive, i have heaps of RAM (2 gb) and good processor, so its not taht

Thanks for your continued help
Marcus

 

[Tom Ellison]

Dear Marcus:

I'm glad we've got this working.

Performance depends much on matching the query with judicious
indexing. Looking at this issue, I would suggest first slightly
rearranging the query:

SELECT creation_date, issue_req_no_s, stock_code_s, quantity_iss_s,
(SELECT SUM(quantity_iss_s) FROM YourTable T1
WHERE T1.issue_req_no_s = T.issue_req_no_s
AND T1.stock_code_s = T.stock_code_s
AND T1.creation_date <= T.creation_date)
FROM DataExtract01 T
ORDER BY creation_date, issue_req_no_s, stock_code_s;

Make an index (if there isn't already one) on DataExtract01 with these
columns:

issue_req_no_s
stock_code_s
creation_date

It may help to do something I think you're already suggesting, which
is to limit the data to only those "issues" which have more than one
row within a specified period of time. You could do this by writing a
query that does that for you, and change the above query to reference
that query instead of the table. Just how that may work out for you
is not certain, but it would be worth a try.

The day I started to need correlated subqueries on a regular basis was
also the day I started to need to move from Jet to MSDE. This is not
a trivial shift, and I'm not saying you should make the jump - it's a
long jump. A query on your data volume and with the complexity you
require would be much faster that way. I say this just to plant a
seed in your mind, not to get you to make any change for now. But,
the change is a great one when you need the performance - not that I'm
convinced that you need to do so now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

The indexing does it! It now works a million times faster... i'm not sure why. Previously, I just had a access generated primvary key number. But i have removed that and did the index as you described (a new thing for me!) It works! It at least processes the data now and doesn't look like it is going to crash as I wait. Speed isn't really an issue, but i need to know that it will be processed .... however, it seems to take 30-40 seconds now, i can live with that

One question, in your SQL, you still describe 'FROM YourTable' . ... i substitute this with the table name DataExtract01 (as per bottom of SQL query).... i'm assuming this is what you meant

I'll keep working with the rest of the calculations and see if its doing the right things

Thanks again for all your help

I have much to learn, but i'm getting there

thanks
marcus

 

[Tom Ellison]

Dear Marcus:

You don't have to remove the existing primary key in order to add an
index. You can have more than one index on a table.

Indexing by the 3 columns on which you must use in the criteria of the
subquery provides a much faster way for the query engine to find the
rows referenced there.

30 seconds isn't all that great, but it's returning a moderately large
amount of information. When I write similar queries for MSDE, I
expect performance about 10 times that fast, given a good server.

Sorry I didn't catch on to using your actual table name more quickly.
We don't want it to be TOO easy for you. You should have to work at
it a little bit, no? <grin>

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Thanks for your help

Don't worry, this was only one small part of a large query and analysis... so I'll have plenty to work out for myself

Thanks again
Marcus