The Chicken or The Egg (1 Viewer)

I want to create subforms for two forms. Whatever works for one will work for the other since the parent forms represent Purchases and Sales (two sides of the same coin). I want the subforms to show Current Inventory by location. In order to do this I’m pretty sure I must create another table to capture Current Inventory.

Here’s the chicken or the egg part: I can choose which location to receive inventory into but cannot yet (successfully) choose from which location it is sold. In order to have current inventory in a subform, I have to develop the correct table. In order to get the correct table, I have to be able to choose from which location an item is sold.

I have seen this done on a Paradox database and I’d like to do it (and learn) on Access.

Rather than take too much space on the forum, I am attaching the conditions that exist in this database in the Word document attached.

Can some old (or young) pro help an old (but not so pro) out with this? I think that inventory by location must be used in many inventory databases.

Don't create a table for your inventory, use a query. Looking at your structure, I'd create a union query based on tblPurchases and tblSales with the qty field froms sales having a negative sign. Then making this a totals query will give you the current stock on hand (total purchases minus total sales).

To list by location, you need a control in your form that shows the location. Reference this control as a criterion in your union query so that only purchases and sales from your specified location are included in the query results.

Thank You Neil

I posted a question on Queries. I am trying to follow the steps in Access Help to try your suggestion but it's asking me to point to SQL Specific and I'm finding my only choice is an SQL button which, when I click on it, defaults to "SELECT".

When I clarify what I'm doing wrong and get to SQL Specific and can choose Union Query, I will try the following:

SELECT [ItemID],[LocationID]
FROM [tblPurchases]
WHERE (I don't know what this part should be)

UNION SELECT [ItemID],[LocationID]
FROM [tblSales]
WHERE (I don't know what this part should be)

I know I need help with WHERE but is this what you mean?

Yes, the WHERE bit is the criteria.

I suggest you build a select query first and look at the SQL that this gnereates. You will then be able to copy this into your union query. Whenever I need to create a complicated query, I build as much as I can in the query grid, and then switch to SQL. I'm not a trained programmer so this makes life easier for me.