Help with using subtotals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wrightlife11 - 27 Jul 2005 20:31 GMT

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

Reply to this Message

Chris - 28 Jul 2005 15:03 GMT

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
[quoted text clipped - 23 lines]
>
> Matthew

Reply to this Message

Tim Ferguson - 28 Jul 2005 17:59 GMT

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

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

Reply to this Message