So I figured out how to do this with a crazy excel formula, which
references
the cells above it. Is there a way to reference cells above in Query?
=IF(E1="Over","Over",IF(AND(C2>=46,E1="No"),"Yes",IF(AND(C2>=46,E1="Yes"),"Over","No")))
I can then determine to capture the boxes or not. For instance if the
running total is not above 46 it will say “Noâ€, no calculation of boxes
is
necessary. If the total hits 46 on that day (only one order per day),
it
will
say “Yesâ€. I can then use some more crazy formulas to identify if it
was
over
on the small or large boxes. Going forward for each day after, it
recognizes
the day before had “Yesâ€, so it knows all boxes are to be calculated.
I can use this…but just am curious if it is possible in Query for
better
quality control. I’m out for the day. Thanks for all your help!
:
Okay: so small boxes have priority over large ones here.
Since you have the progressive total, you can quite easily get the
count
of
boxes over 46: that's just:
IIf([ProgTotal] > 46, [ProgTotal] - 46, 0)
where ProgTotal represents your progressive total expression.
If you want to know the count of small boxes over 46 and also the
count
of
large boxes over 46, you will need to use 2 subqueries: one to give
the
progressive total of small boxes, and the other to give the
progressive
total of large boxes. You can then sum these 2 to see if the total is
over
46, using an IIf() expression. If so you can determine whether the
previous
total was over 46 without the large boxes. If not add the difference
for
small boxes only; if so add the appropriate difference for each of
them
separately so these new fields for Over46Large and Over46Small give
you
the
right numbers in each record.
It is going to take you some effort to do that, but you shold be able
to
do
it in the query with nested IIF() expressions (without having to call
a
VBA
function to calculate it.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I have a date associated with each. And you have to buy a small box
before
you can purchase a large box. So if the total goes over 46, you know
first
to
look at if it went over on the small boxes and if not look to the
large
boxes. I can get a progressive total....the tricky part is how to
determine
the split of boxes when it goes over. And then continue to count
going
forward....
:
So you want a way to get a progressive total for the day?
There has to be some way to know which orders came first, so I will
assume
you have fields like this:
- OrderID AutoNumber primary key
- OrderDate Date/Time the day
- SmallQty Number how many small boxes in this order
- LargeQty Number how many large boxes in this order.
Create a query, and type an expression like this into a blank
column
in
the
Field row in query design:
(SELECT [SmallQty] + [LargeQty] AS HowMany
FROM Table1 AS Dupe
WHERE Dupe.OrderDate = Table1.OrderDate
AND Dupe.OrderID <= Table1.OrderID)
You now have a progressive total for the day, so you can see when
the
total
passes 46.
I'm not sure exactly how you will calculate the number of
additional
small
and large boxes: because one order contains both small and large
ones,
there
is no way to determine which of those boxes are the one that should
be
considered over 46 when you hit that row.
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
So, I can't figure this one out...
I have small boxes and large boxes. Each day both small boxes and
large
boxes might be sold. Once 46 boxes are sold, I want to know the
additional
small and large boxes sold. Here is an example:
Small Box Large Box Total
8 4 12
8 2 22
6 0 28
8 6 42
8 2 52 <-- Passes 46
4 0 56
So, on the 46th box sold is a small box in a group of 8. I would
then
start
to calculate after that point. In this case after the 46th box
was
sold,
there would be an additional 8 small boxes and 2 large boxes for
a
total
of
10 extra boxes. Which matches the total of 56.
Can't figure out how to do that in a query.... Help me!
