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!