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]
[Products].[ID] -> [ProductChanges].[PName]
[Products].[CurrManager] -> [Managers].[ID]
[ProductChanges].[CurrManager] -> [Managers].[ID]
1/1/2016 | 1/8/2016 | 1/15/2016
Smith, J 10 12 9
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;
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.