Help with using subtotals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help with the following set of information. I am able to get what I
want from excel, but need to do it in Access. IN Excel I used the if
statement in the subtotal field to create a conditional statement. I need to
do the same in Access, but I think it will require a count and for each
statement, which I have not been able to successfully create. Can you help
me? I need to use the subtotal for the math in Access so I can determin
Which orders I do not have enough material to fill so I can create purchases
for the needed material.

Order Material Reqmts qty W/D qty On Hand Subtotal
20390995 8903160 2 0 0 =IF(B2=B1,F1-(C2-D2),E2-(C2-D2))
20382609 15210882 9 3 2 -4
20387351 15327124 110 0 440 330
20387352 15327124 110 0 440 220
20387353 15327124 110 0 440 110
20387354 15327124 110 0 440 0
20387356 15327124 110 0 440 -110
20390995 8913952 6 1 2 -3
20390995 8903020 8 0 0 -8
20395339 15218915 2 0 0 -2
20395339 15218915 6 0 0 -8
20399029 15208714 6 0 5 -1
20398162 8648695 8 0 4 -4


Thanks

Matthew
 
I think you might be able to solve this with a query, selecting all fields
into the query, and then having an additional last column (subtotal) with the
formula IIf(B2=B1,F1-(C2-D2),E2-(C2-D2)) - and yes, that's two "I" in the
statement.

HTH

Chris
 
I need help with the following set of information. I am able to get
what I want from excel, but need to do it in Access. IN Excel I used
the if statement in the subtotal field to create a conditional
statement.
Click to expand...

You'll have to change the procedure for what you want quite radically: a
spreadsheet like Excel works on a very different basis from a database
like Access.

Working sequentially in databases can be done but it's not very easy:
even numbering rows in a dataset takes a nested subquery and runs like
treacle.

On the other hand, working with subgroups is very easy -- much more so
than in Excel.

I don't quite know what your formula is trying to do and I think there
are errors at least in what you have provided here. Still, the following
might help:

Start with a GROUP BY query in order to get the SUM() functions producing
the results you want. If you need sub-sub totals then it starts to get a
lot more complex (e.g. :-

sum of reds = 32
sum of blues = 67
sum of colours = 99
sum of ones = 12
sum of twos = 45
sum of numbers = 57


)

Some more processing can be done within the report: you can write
functions to keep track of totals and numbers on a per-line basis, and
write them back into controls. I don't do a lot of that, but you can get
a lot more help in the m.p.a.reports newsgroup.

Hope that helps


Tim F
 
Back
Top