AnsweredAssumed Answered

Creating table made up of data from other tables

Question asked by SamRich on Apr 20, 2014
Latest reply on Apr 22, 2014 by philmodjunk


Creating table made up of data from other tables


     I have 2 tables related through a third table. The linking table is called Orders, and the two others are Order Details and Payments.

     There are sometimes multiple order details per order; and multiple payments per order. I have since also linked the Order Details to Payments - so I know which payment is for which item. 

     But at the moment I don't have a table which shows all order details and their related payments. First of all I guess I should create this - but I'm not sure how to. Basically this wouldn't have any of it's own data in it - it would have all the order details data listed, and all the payments data listed. 

     Then I want to create a graph that will show quantity of stock of item x going out minus payments for item x. I want to see how much is the gap between stock out and payments and see whether the gap is closing or growing over time. I imagine I would create summary fields of running totals of each and then minus one from the other to find the gap. I would then do a monthly sort so the data comes out in a graph. 

     One other complication is that I would be sorting the Order Details data by the date of the order, and the Payments data by the date of the payment. I assume that to do this I would have to create a new field and work out of a way of grabbing the dates from their respective tables into one column. 

     Any help much appreciated!