sum and do calculation at each change for 900000 rows

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

I have an access query that brings back over 900K rows. I need to group them
by date, activity, region, status, (Zero means there was no backorder, and 1
means there was a backorder) and stock number.
At each change in status I want to add up the number of orders without
backorders for a stock number in a month, then add up the number of orders
with backorders for a stock number in the same month. In the example below
there would be 320 orders (0 status) with 110 backorders for stock number
34565 in November 2008. I need the query to add up the 320 and 110 and give
me a total of 430.
After getting the totals for the two status' for each stock number, I need
to do a calculation with BO being the backorders and ORDERS being the total
number of orders for november 1-BO/Orders. 1-110/430 for the example below.
This gives me a percentage of how much of the orders were available to my
customers.


date activity region status orders
stock number
11-08 ORD MID 0 200
34565
11-08 ORD MID 0 120
34565
11-08 ORD MID 1 90
34565
11-08 ORD MID 1 20
34565
12-08 PhL NE 1 800
44039

I would appreciate any help I can get. I need to get this done when I get
back to work. Normally I do it in excel, but I don't have as many variables.
Instead of wanting the stock number and the region, they now want the stock
number, the region, the activity and the customer. When I bring all this into
the query, the results go from 5000 rows to 900000 rows. Even if it could
drop it into excel (we have 2003) doing that calculation by hand would be
unmanageable, and I will have to do this on a monthly basis.
 
I have an access query that brings back over 900K rows. I need to group them
by date, activity, region, status, (Zero means there was no backorder, and 1
means there was a backorder) and stock number.
At each change in status I want to add up the number of orders without
backorders for a stock number in a month, then add up the number of orders
with backorders for a stock number in the same month. In the example below
there would be 320 orders (0 status) with 110 backorders for stock number
34565 in November 2008. I need the query to add up the 320 and 110 and give
me a total of 430.
After getting the totals for the two status' for each stock number, I need
to do a calculation with BO being the backorders and ORDERS being the total
number of orders for november 1-BO/Orders. 1-110/430 for the example below.
This gives me a percentage of how much of the orders were available to my
customers.


date activity region status orders
stock number
11-08 ORD MID 0 200
34565
11-08 ORD MID 0 120
34565
11-08 ORD MID 1 90
34565
11-08 ORD MID 1 20
34565
12-08 PhL NE 1 800
44039

I would appreciate any help I can get. I need to get this done when I get
back to work. Normally I do it in excel, but I don't have as many variables.
Instead of wanting the stock number and the region, they now want the stock
number, the region, the activity and the customer. When I bring all this into
the query, the results go from 5000 rows to 900000 rows. Even if it could
drop it into excel (we have 2003) doing that calculation by hand would be
unmanageable, and I will have to do this on a monthly basis.
Click to expand...

A Totals query will do this. What's the datatype of the [Date] field? (Note:
Date is a reserved word for the today's-date function, and you should consider
changing the fieldname).

Try a Totals query. Create a query based on the table; add two calculated
fields by typing

BOCount: [Orders] * [Status]
and
InStockCount: [Orders] * (1 - [Status])

Change it to a Totals query by clicking the Greek Sigma icon in the toolbar;
Group By the fields that you want to group by, in the order you want them
grouped; select Sum as the totals operator for Orders, BOCount, and
InStockCount.

Base a Report on this query, and use its sorting and grouping feature to group
by the month. In the group footer put a textbox with an expression such as

Sum(SumOfBOCount) / Sum(SumOfOrders)

to calculate your percentages.
 
Back
Top