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?