I am in the process of trying to create an outside inventory management system and am getting stuck on how to join two sets of data to create a full holdings view. Below is a description of the situation, any help would be appreciated!
Our client account data is not updated via a live feed, so we rely on regular but varying updates of holdings information by account, which we upload into a database as (Table="Known_Holdings") - CustAcct#, ProductID, Known_QTY, Known_Date
We also are purchasing things constantly, so in order to create a (presumed but not guaranteed accurate) "Live Holdings" dataset, I would like to combine those known positions with activity in each account since the Known_Date from my firms transaction history (Table="TDH" - CustAcct#, ProductID, QTY, TransDate)
I believe the best way to do this would be to create a query that pulls the most recent set of records for each account from "Known Holdings" and then adds the "TDH" records for each account IF the TransDate is more recent than the Known_Date.
A.) Is this line of thinking correct? B.)Is there a best way to go about this? I seem to be hitting a wall on how to start here.
Our client account data is not updated via a live feed, so we rely on regular but varying updates of holdings information by account, which we upload into a database as (Table="Known_Holdings") - CustAcct#, ProductID, Known_QTY, Known_Date
We also are purchasing things constantly, so in order to create a (presumed but not guaranteed accurate) "Live Holdings" dataset, I would like to combine those known positions with activity in each account since the Known_Date from my firms transaction history (Table="TDH" - CustAcct#, ProductID, QTY, TransDate)
I believe the best way to do this would be to create a query that pulls the most recent set of records for each account from "Known Holdings" and then adds the "TDH" records for each account IF the TransDate is more recent than the Known_Date.
A.) Is this line of thinking correct? B.)Is there a best way to go about this? I seem to be hitting a wall on how to start here.