Cumulative Total

[Guest]

I'm needing to write a cumulative total in a query. Tried reading earlier
threads and just got more confused. I need to do the following:

Inventory on hand - Inventory needed - Balance
Balance - Inventory needed - new balance

Can anyone help? Thanks, Sherry

 

[David S via AccessMonster.com]

Hi Sherry,

What did you find confusing about them? Try this one:
http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/17623/Cumulative-total


You should be able to apply that to your tables - it would help if you could
post the field names of your tables so we could have a go at it for you.

Inventory on hand - Inventory needed - Balance
Balance - Inventory needed - new balance

Are these table names or field names? Is there a date or product ID involved
somewhere?

 

[Guest]

The name of my query is "Components Needed"

Field Names are as follows with the results in the Balance Column of what I
would like to see:

Component Item ID Amount Needed Quantity On Hand Balance
10 50 100
50
10 50 100
0
10 50 100
-50

Thanks for your help. I am new at this if you can't already tell. Sherry

 

[David S via AccessMonster.com]

Ok, that helps because you've outlined the output of what you want to see -
what are the inputs ie. what are the source tables and fields that this query
runs off?

I have to say I'm a bit confused myself - what distinguishes one line from
another here? Is there some sort of date field involved, or Master Product ID
or something?

 

[Guest]

Component Item ID is the Master Product ID. Base information comes from two
separate tables the amount needed is from a linked table named work ticket
and the quantity on hand (which is a static amount) is from another linked
table named item new.

 

[David S via AccessMonster.com]

It sounds like you don't actually want a "cumulative total", but really just
want to know how much of each product you have on hand, how much you'll need,
and therefore what the left over balance is - unless you want a break down by
work ticket?

Try this - first, get how much of each product you're going to need from the
work ticket table in a query that we'll call [Amount Needed Query]:
SELECT [Work Ticket Table].[Component Item ID], Sum([Work Ticket Table].
[Amount Needed]) AS [SumOfAmount Needed]
FROM [Work Ticket Table]
GROUP BY [Work Ticket Table].[Component Item ID];

This will total how much you need for each Product. Then, you can link this
with your quantity on hand table to get a total for each product:
SELECT [Amount Needed Query].[Component Item ID], [Amount Needed Query].
[SumOfAmount Needed] AS [Amount Needed], [Quantity On Hand Table].[Quantity
On Hand], [Quantity On Hand]-[SumOfAmount Needed] AS Balance
FROM [Amount Needed Query] INNER JOIN [Quantity On Hand Table] ON [Amount
Needed Query].[Component Item ID] = [Quantity On Hand Table].[Component Item
ID];

If you want to break this down by Work Ticket and display a Cumulative
Balance for each ticket, that's a lot harder, and is where that thread I
first mentioned starts to come into things...