How to join 2 sets of data for inventory control? (1 Viewer)

mbe

New member
Local time
Today, 15:17
Joined
Jan 15, 2019
Messages
2
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.
 

plog

Banishment Pending
Local time
Today, 14:17
Joined
May 11, 2011
Messages
11,611
Sounds logical to me. My only critique would be to put all the data in one table:

TDH
CustAcct, ProductID, Qty, QtyDate, QtyType

Where QtyType would be either "Known" or "Activity". Then you can do a simply Totals query to find the last known date of each Cust/Product:

Code:
SELECT CustAcct, ProductID, MAX(QtyDate) AS LastKnownDate
FROM TDH
WHERE QtyType="Known"
GROUP BY CustAcct, ProductID;

Call that "_Sub1". Then to get the data you want you bring in TDH and _Sub1, JOIN them via CustAcct and Product ID, SUM the Quantity field and place a condition that the QtyDate has to be greater than or equal to LastKnownDate.

Also, as I did above, remove the "#" from your field name. Only use alpha-numeric characters in table/field names.
 

mbe

New member
Local time
Today, 15:17
Joined
Jan 15, 2019
Messages
2
Since I already have these two tables coming in from their own macros, would there be a problem of using queries to create a new table that this code runs in?
 

plog

Banishment Pending
Local time
Today, 14:17
Joined
May 11, 2011
Messages
11,611
I wouldn't create a table to apply my specific method. If you want the tables seperate you can still accomplish this.
 

Users who are viewing this thread

Top Bottom