MS Access product manager capacity timeline query/report
This may take a bit of explaining, but I'm trying to figure out if this is a query I haven't figured out how to write, or if this would be better solved with a change in the current structure of the MS Access database I've built. So with that said...
I have an MS Access database with 3 main tables for this issue:
[Products]
[ProductChanges]
[Managers]
They are linked like this:
[Products].[ID] -> [ProductChanges].[PName]
[Products].[CurrManager] -> [Managers].[ID]
[ProductChanges].[CurrManager] -> [Managers].[ID]
The Products table lists all current, past, and future products we service (or will service), and the ProductChanges table contains all changes to that product (movement between managers, status updates, etc.) I've gotten it to look really nice from a navigation view, each product's form shows its current status, manager, and a subform shows its past and upcoming changes (from the ProductChanges table).
I've also written an update query that runs every time the database is opened that updates the CurrManager field in the Products table based on the dates of the changes in the ProductChanges table, and updates it to the value of the CurrManager field in the ProductChanges table. Maybe I should change one of those field names, it sounds confusing now that it's typed out.
Here's the tricky part: as new products come our way, we need to know which managers can take on more (they have a max capacity). Current capacity is easy, querying the Products table's field 'CurrManager' and counting the ID's of the products assigned to them as of right now, but we need to see who will have capacity in the future, preferably in a timeline format. Since products change hands a lot (and some finish and move on to somewhere else), the number of products each manager is overseeing will change as the database updates itself based on the dates in the ProductChanges table.
So an example of a final output ideally would be something like:
1/1/2016 | 1/8/2016 | 1/15/2016
Smith, J 10 12 9
Depending on how many products were scheduled to either be assigned to J. Smith or move on from J. Smith between those dates. A "Capacity by Week" essentially. I've made some progress but have been hung up for a while. This query will get me a basic timeline of history, but I can't seem to get it to sum up a portfolio for date ranges:
SELECT Managers.LastName
,Managers.FirstName
,ProductChanges.Effectivedate
,[ProductChanges].[EffectiveDate] - Weekday([ProductChanges].[EffectiveDate]) + 1 AS [Week Of]
,Count(Products.Name) AS NumProducts
FROM Managers
RIGHT JOIN (
Products INNER JOIN (
ProductChanges LEFT JOIN Managers AS Managers_1 ON ProductChanges.CurrManager = Managers_1.ID
) ON Products.ID = ProductChanges.InnCode
) ON Managers.ID = Products.CurrManager
,Managers.FirstName
,ProductChanges.Effectivedate
,[ProductChanges].[EffectiveDate] - Weekday([ProductChanges].[EffectiveDate]) + 1
HAVING (
((Managers.LastName) IS NOT NULL)
AND ((ProductChanges.Effectivedate) > 0)
)
ORDER BY Managers.LastName;
It's a mess, I know, so if (and when) clarification is needed, I'll be watching this one pretty closely.
I've got it! It ended up being a 2 step process to get what I needed it to do, but so long as the records are up to date for the product changes, this shows me an actual timeline of a manager gaining and losing products.
The main reason I had to do it this way is that the user has the ability to change a product's past history in the case there is something missing, which means that the record ID's in the ProductChanges table can be considered essentially random in relation to the EffectiveDate field, making a running total query on just the ProductChanges table almost but not quite work for managers whose product change histories had been changed. I wrote a quick script to build a table specifically for ordering the ID's the way I needed:
Dim db As Database
Set db = CurrentDB
db.Execute "DROP TABLE GainLoss;"
db.Execute "CREATE TABLE GainLoss(AutoID AutoIncrement, HCID Long, MgrID Long, LastName varchar(100), FirstName varchar(100), HCDate Date, GainLoss Long)"
db.Execute "INSERT INTO GainLoss(HCID, MgrID, LastName, FirstName, HCDate, GainLoss) SELECT hc.ID, s.ID, s.LastName, s.FirstName, hc.EffectiveDate, iif(s.id = hc.currmanager,1,-1) FROM Managers s INNER JOIN ProductChanges hc ON (s.ID = hc.currmanager OR s.ID = hc.prevmanager) ORDER BY LastName, FirstName, EffectiveDate;"
Which also gives me a plus one for each product that a manager gets, and a minus one for each one they pass on. Throw that through this query:
SELECT A.AutoID, A.MgrID, A.LastName, A.FirstName, A.HCDate, Sum(B.GainLoss) AS Capacity
FROM GainLoss AS A LEFT JOIN GainLoss AS B ON (A.AutoID >= B.AutoID) AND (A.MgrID = B.MgrID) AND (A.HCDate >= B.HCDate)
GROUP BY A.LastName, A.FirstName, A.MgrID, A.HCDate, A.AutoID;
And you've got a timeline showing every change either adding or subtracting a product to a manager's portfolio.