Inventory by Account
I have 3 tables: Accounts, Transactions, Products.
Accounts has one field: AccountNumber
Products has 2 fields: ProductName and a calculation field= sum(Transactions::Qtty)
Transactions has 4 fields: Tr_acct, Tr_prod, Tr_Date, Tr_qtty (Tr_qtty can be negative or positive depending on whether it's a buy or a sell). The three tables are linked with Accounts::AccountNumber=Transactions::Tr_acct and Tr_prod=Products_ProductName.
I would like to have a layout on which I can select an account number, select a date and have a Portal display the "inventory" for that particular account on that date. I do not want to see a product in the portal for which the balance is zero.
I know this must be very simple, but I just can't seem to get it to work.