Circular Reference

[SiGill]

I have data in a table in Access. I need to show this data in a summary document but keep getting a circular reference. Is there a way I can do this, using just one query, without creating a circular reference?

See example in Excel.
My data in the the Main Info Tab. I have Weeks, Qty, and an Area (Arrivals and Orders) This is then fed to the Summary tab.
In the Summary tab I have an opening stock, it then Adds on any Arrivals, Minuses off the Orders to give a closing stock. That closing stock is the opening stock for the following week.

Easy enough to do in Excel, but how can it be done in Access? I have been racking my brains but I just cannot think of how it can be done with out creating a few make table queries then getting a macro to run them all in the background.

 

[Minty]

You are after a running sum, this is easy to achieve in a report. Although the layout you require would require a bit of work.

Initially I would look at a cross-tab query. Have a play with that first.

 

[The_Doc_Man]

This problem would be solved by not thinking in Excel terms. The typical way this is done in Access is that you have the individual transactions for each arrival and each order. Then you can create a query that sums all transactions (where orders have the opposite sign from arrivals). The summation query then tells you at any point in time exactly how much you have in stock.

To get this for a specific date (i.e. for a report that wants to know something about the beginning of the week), the individual transactions need to include their "effective date." Then generate the sum for all transactions where the date is less than or equal to the date of the beginning of the week.

Your transactions can include arrivals, orders, shrinkage, order returns, and adjustments based on sight inventories. Just include the appropriate sign as adding or removing stock for the stock item.