Add 1 running total for DB not designed for running totals

[sulven]

Hi all, I'm of beginner/intermediate Access design experience with a tiny bit of VBA & SQL knowledge. This is a work issue and for many reasons (mostly internet restrictions) I probably won't be able to copy code or provide all details in follow-ups, but I'll do what I can.

Short version: I need to add a running total to 1 item in a database not designed for running totals.

Long version: I have a database at work I inherited maintenance responsibilities from someone who left. It was designed as Dashboard for our group’s many processes and sub-process and supervisors would update their daily inventory totals based on reports from analysts by New, Outstanding, and Completed. These are some of the columns on the Dashboard reports (2 reports serving similar functions) There are no running totals, they are date-stamped and the entry forms have editing functions, and each sub-process has one line per day on the table. One report (Daily Inventory) has columns for New and Outstanding for today's date (info from 10pm previous business day), and another section of columns for New and Completed (info from 2 business days ago). The other report is a dashboard with date range options and I think only shows New and Completed, and various other things not related. I think I'm only concerned with the 1st Daily Inventory report for now.

The 2 reports are grouped by process, and each process has around 4 sub-processes. One supervisor has a process that was previously had only 1 sub-process, but now wants it to be 4. In all actuality, these new rows (sub-processes) take the function of the report columns, but that’s the way they want it while still keeping the columns and not affecting the function for other processes.

· Process Header (Totals)


o New Orders
o Orders Pending (research complete, ready to order)
o Completed Orders
o Outstanding Orders (I think they want this as running total of New Orders not yet researched).

They typically only have New Orders once a month and Orders Pending and Completed Orders are infrequent. The goal is to not have to enter anything if nothing has changed and when there is a New Order, it’d add to Outstanding Orders, and when there is a Completed Order, it’d subtract from Outstanding Orders.
This database has over 30 forms, queries (almost all SQL), reports, tables and macros, though still fairly simple. At first, I thought about adding their own Detail section on the reports with custom logic, but if I do that, I think I might have to practically duplicate and modify all the forms, queries, tables and macros just for that one process.

I then thought perhaps a SQL query, or a few of them, might do the trick, but I’m only fluent enough in SQL to edit or work my way through it with online help, not in knowing what its full power is.

I’ve looked online for weeks, but my biggest struggle is probably finding the appropriate search terms for what I’m trying to do. I’ve found many articles that are close (“running totals” search here seems promising), but not quite or wasn’t sure if it’d apply to me. Any advice or links to help lead me to the easiest way of solving this issue? Is SQL the best route? Should I make a new Detail for that process on the reports?

[June7]

I can't even begin to visualize what you describe. Pictures might help. Providing db for analysis certainly would. Don't know what you mean by 'adding their own Detail section' - AFAIK, a report can have only one Detail section.

Not sure these links are relevant to your situation but might be worth reviewing anyway:
http://allenbrowne.com/AppInventory.html
http://allenbrowne.com/ranking.html

[sulven]

You're right, my explanation isn't good at all. I'll try to think of some kind of mock-up to post later. If "a report can have only one Detail section" is true, that is very helpful and rules out some options. You're links are good and are sort of in the direction I think I need to take. I need to find a way to do this for only a specific process group without affecting the other process groups which basically just calls in the numbers entered for the day.

For now, the report looks very similar to this Inventory DB I found on some other forum (sorry for lack of credit, EDIT: found it here), except the Detail is grouped (parent process and sub-processes). Then add a column set for today's date with New and Outstanding, and another column set for previous business day with New and Completed.

[peiyezhu]

I have download the mdb file. but i still can not follow you.
can you give your original table and the outcome report in excel manually？
so we can know what You want to do.

[sulven]

I made a mock-up to hopefully help in Excel with a few notes. Thanks for looking!

InvSample.zip

[June7]

Looks like want to de-normalize data for the output - arrange the data horizontally by date - this often involves a CROSSTAB query. If the output requirement is too complex for a single CROSSTAB, other methods:

1. multiple CROSSTABs

2. emulate CROSSTAB with expressions in query http://www.datapigtechnologies.com/f.../crosstab.html

2. VBA and writing records to temp table

[sulven]

Thanks all! I'll look into a temp table. CROSSTAB may also be helpful and did spawn some ideas to try out.