Query Totals (1 Viewer)

Hi,

I will try my best to keep this as simple as possible but I find this very hard to explain!

I am trying to create a database to control stock in my wife's shoe shop. I would like to be able to calculate the current number of each of unique item in stock but I am having issues with working out how to do this correctly.

There are two tables working together that are the subjects of this question, tblStock and tblSales. In tblStock is a field for the total number of a stock item purchased by the store (for example year one - 10 purchased, year two - 10 purchased = 20 total), and in tblSales is a field for the number sold in each transaction. The way it works is, when there is a sale, the record is stored in tblSales and linked to the item in tblStock. This means that over time, there will be multiple records in tblSales that relate to one item in tblStock.

My problem is then calculating the resultant number in stock. I would like another field that calculates the current number in stock by subtracting the number of each unique item sold over all transactions from the the total number of historic stock.

I tried a query with a field:

NumberInStock: ([TotalPurchaseQuantity]-[AmountSold])

But this understandably only works for individual transaction, and doesn't give a running total.

I also tried this, but this threw up an error:

NumberInStock: Sum([TotalPurchaseQuantity]-[AmountSold])


I think ideally I would like a column in a query that could calculate the total number sold of each individual item, by adding up all of the individual transaction amounts.

I know that probably isn't the best explanation, but any help would be greatly appreciated.

I can share the database with anyone willing to take a look into this.

Thanks in advance!

Alex.

I don't think your database is set up correctly. When you want a balance of anything, you should store credits and debits in the same table and same field. That way getting the balance is as easy as just doing this:

Balance: SUM(TransactionQuantity)

For you, that means instead of storing credits (TotalPurchaseQuantity) in one table and debits (AmountSold) in another you bring them together in a table that is structred like so:

Transactions
TransactionID, autonumber, primary key
TransactionStockCode, number, links to another table to identify exactly what was bought/sold
TransactionQuantity, number, amount of items in transaction


Then, as I said above, calculating balance just means summing that one field. Negative TransactionQuantity values represents sales from inventory and positive values represent additions to inventory. That is how balance is achieved in databases.

Thanks for your quick response. I think I understand what you are saying.

My issue is that each transaction can be different due to final sale prices potentially being different according to the amount of discount etc.

I have shared an image of this to help understand it. I use the dates and times to obtain reports of sales performances and it keeps track of everything.

In my head, if I go with your suggestion, I will lose all of this functionality and end up with a table that only gives me a very basic total.

I hope i'm not missing your point here!

I have simplified the database to leave only the tables and one query. It is attached to this post.

I hope this can explain my thinking better.

My issue is that each transaction can be different due to final sale prices potentially being different according to the amount of discount etc.

Click to expand...

Don't confuse "number of items sold" with "sales price." Your original question was about getting a count of each unique item in stock. You added the question regarding reports of sales performance. PLog's answer is actually the way you handle YOUR question, too.

I'm going to go into more detail in order for you to see how this might work. This is ONE person's viewpoint and not guaranteed to be right for your specific needs. I'm going to suggest some items to research online, at least in passing.

In general, if you want to track the inventory amount of a unique item, you have two tables as PLog told you.

One table is the description of each unique item, and that description MIGHT (or might not) include "Suggested Price." If the items in question can vary in estimated price then the field in question might be elsewhere. But the item table will ALWAYS have a unique identifying key to go with the unique item. Whether this is your SKU (as some places call it) or a catalog number or just an arbitrary number created via the Autonumber feature of Access, the point is that the given number points to the given description and no other. Look up the term "PRIMARY KEY" and also look up "SYNTHETIC KEY" and "NATURAL KEY" - which you can do on this forum using the SEARCH feature in the thin blue ribbon near the top of the page.

Your second table is a transaction table. In it, you have the item number for the item in question. You have transactions in the table. One might be when your supplier delivers stock. You would list the date, number, and maybe an ID for whoever in your store put the items into stock. (That's optional.) You could also include the base price paid for the item if you wish. The other thing you have is the code that says "shipment recieved" and we'll talk about that in a moment. The ID of the unique item in THIS table is what we call a "FOREIGN KEY" - another reading item. And you link this table to the item descriptions using a "RELATIONSHIP" - more reading. You establish what is called a "ONE TO MANY" relation from the item to the transaction on the item's primary key and the transaction's foreign key using a "JOIN" query. Yep, read up on that, too.

You have another transaction for a sale of one or more of the items. Again, you have the foreign key, a date, perhaps the sales price, but always the number of items sold and a code that says "Sold." You can put the salesperson ID in this transaction.

You can have other transactions such as "Customer return" (if that is in your business model), "Shrinkage" (if inspection shows damaged goods), "Inventory Correction" (if you do a total inventory and find errors). In each case, you have a code in the transaction that will tell you how to treat the transaction - as increasing or decreasing stock.

How do you do that? Create a third table that contains the possible inventory codes, a name for what they represent, and a direction of +1 or -1. The transaction code in the transaction table becomes a foreign key and the transaction-code table has that same code as it's primary key. Here, the relationship is "MANY-TO-ONE" from the transaction table foreign key to the code table's primary key.

Now, to find items available, you build a query that joins the three tables on the keys as I noted. In fact, with Access, if you have established the formal relationships I described earlier, the query builders will recognize the presence of those relationships and will automatically incorporate them.

For listing all transactions in excruciating detail, you join the item table to the transaction table to get item descriptions. You also join the transaction table to the code table and pull together the item number, transaction amount, the transaction description from the code table, and whether this transaction was an add or a remove of inventory (+1 or -1).

For getting inventory totals, you take the previous query and use the SUM (an "AGGREGATE FUNCTION") of the transaction amount times the transaction direction. You then "GROUP BY" the item number. The query field with that SUM aggregate will be your stock on hand.

Remember I said, you COULD include the salesperson ID? To get your sales performance, you MIGHT be able to do a query of that same basic three-way join but this time tell it to GROUP BY the salesperson ID, "ORDER BY" date, and only look at sales transactions based on the "sales" transaction code. From there, your choices go all over the place.

The SEARCH feature of this forum is quite good. Use it to look up other articles that have been published on "INVENTORY" as a topic. If you don't see three or four pages full of such articles, I would be greatly surprised.

alexxmf said:

I have simplified the database to leave only the tables and one query. It is attached to this post.

I hope this can explain my thinking better.

Click to expand...

Hope you required this "Sales of each item regard less discount"

Have a look at the updated file, look at the current stock query.

Cheers,
Vlad

bastanu said:

Have a look at the updated file, look at the current stock query.

Cheers,
Vlad

Click to expand...

bastanu,

TotalStockSold needs to be Sum not Group By ?

you're right, sorry!

Here it is corrected.

Sorry again,
Vlad

bastanu said:

Here it is corrected.

Sorry again,
Vlad

Click to expand...

Easily done, but a little hard to find if one did not write the query themselves, which is the only reason i mentioned it.