Inventory rolling totals

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

Guest

Our one facility is currently using a spreadsheet to show a 30-day running
total of inventory levels of finished products. The format is very basic
where "Current On-Hand" + "Boxed" - ("Ship" or "Requirements") = "New
On-Hand". The "New On-Hand" becomes the following day's "Current On-Hand".
I have the production control database established and have a field for each
"Current On-Hand", "Boxed", "Ship" and "Requirement", but I am at a loss as
how to create a report to recreate this information. The report will need to
be able to start and end at any given point in time.

Thank you,
Kathy
 
Some questions:
1. Does your table have a column for date, so you know when the data applies?
2. Will one finished product have multiple rows in the table within the same
date range?
3. If the answer to #2 is yes, would you show each row separately on the
report, or as combined in one row?
 
Answers:

1. Yes there is a date field
2. There could be more than one row in the table for the finished product
for a given date.
3. The report will show only totals.
 
Okay, thanks. Create a select totals query that will be the record source
for your report. Here is how you should set it up:
Date - Filter by a range of dates (You can reference controls on your form
for this, for the Aggregate function use Where
Criteria example:
= forms!MyFormName!FromDate and <= forms!MyFormName!ToDate
Click to expand...

Product Code - Use Group By for the Aggregate function
All Numeric fields - Use Sum for the Aggregate function
 
Thank you, but that doesn't move my "New On-Hand" to the "Current On-Hand" in
the next column. See Example:
8/12/05 8/13/05 8/14/05
Current - 1200 800 1300
Part 1 Boxed - 400 500 200
Shipped - 800 0 500
New - 800 1300 1000
They want to be able to start at any point in time and know what the
inventory level was that day before any adjustments were made.
 
Oh, that's right. Sorry, I was only thinking of a report. Let me think about
this a little, and I will post back.
 
Back
Top