Inventory Storage Calculating System

[ryancgarrett]

I own a business that (gross oversimplification) receives pallets of goods into our warehouse, stores them for a time, and ships them out. We do not own the goods, we're just a storage facility. We have lots of other software that handles orders and accounting and everything that makes our business run, but inventory is all calculated manually. Because of the current system (and limitations), we lose between $10k and 20k per month on inventory storage costs that we aren't billing.

Today I started creating a database and I'm a little stuck (it's been about 5 years since I've done anything meaningful in Access). I'd like to keep this db very simple. Here are the tables I have created thus far:

• tblCustomers
• tblOrders
• tblProducts
• tblCheckIn
• tblCheckOut
• tblStorageRates

Functionality
Basically, I'd like to have 2 forms that my warehouse team uses, Check In and Check Out.

I'd like to be able to run a report at the end of the month, detailing how many pallets each customer stored, so accounting can properly invoice customers.

I'd like the same report to be run when a customer checks out the last of their inventory, even if it is mid-month.

Nuances
Customers don't always check out all inventory at once. A lot of times they will send 100 pallets of product A on 1/1. They will ask us to ship out 30 pallets of product A on 3/15. We will receive 200 more pallets of product A on 3/25. We will ship out 250 pallets on 4/1, and the remaining 20 pallets on 4/17.

In this scenario, I need a report on 1/31, 2/28, 3/31, and 4/17 for this customer.

Storage is charged on a daily basis, based on the number of pallets at the end of the day.

Questions

• Am I over-simplifying this?
• Can this be done with the tables I have, or do I need more?
• Suggestions/offers to help for a fee?

 

[pbaldy]

I lean towards fewer tables, not more. I'd have a single table for transactions, not two. Check ins would be positives, check outs would be negatives. You could easily sum the quantity at any given point in time. With two tables, you have to combine them to get a balance anyway.

 

[jdraw]

I agree with Paul re the Transactions.
As for

Am I over-simplifying this?

, it's hard to say.
If Customers just ask for pallets to be shipped, or specific pallets to be shipped that seems straight forward. If however, you are responsible for shipping based on First In First Out (FIFO) or some other means then that could get complicated.

I recommend you create a model based on your tables and understanding of your business and test it with some test data and "probable" scenarios. You can do this with pencil and paper, or index cards etc. Mock up some inventory and some customer requests and work these against your model. Reconcile every anomaly you encounter and adjust whatever needs adjusting (tables, fields,relationships, scenario...) and retest until it works on paper. Now you have a tested design for your database and can move forward.

Don't be too quick to jump into physical Access. Work from a plan and design.

Good luck.

 

[ryancgarrett]

I lean towards fewer tables, not more. I'd have a single table for transactions, not two. Check ins would be positives, check outs would be negatives. You could easily sum the quantity at any given point in time. With two tables, you have to combine them to get a balance anyway.

I like the idea of less tables. My question is, if I use a single table for inventory change records, what would a query look like to calculate storage between dates?

 

[Minty]

Assuming you have a qty field, a transaction date and a location, it will be as simple as something like;

SELECT tblMovements.PartNumber, Sum(tblMovements.Qty) AS SumOfQty, tblMovements.BinLocation
FROM tblMovements
WHERE (((tblMovements.TransactionDate)<Date()))
GROUP BY tblMovements.PartNumber, tblMovements.BinLocation;

This will add and subtract the positive and negative transactions and provide a current location and qty stored figure.

 

[jumbotrano]

Have fewer tables for sure.

 

[ryancgarrett]

Assuming you have a qty field, a transaction date and a location, it will be as simple as something like;

SELECT tblMovements.PartNumber, Sum(tblMovements.Qty) AS SumOfQty, tblMovements.BinLocation
FROM tblMovements
WHERE (((tblMovements.TransactionDate)<Date()))
GROUP BY tblMovements.PartNumber, tblMovements.BinLocation;

This will add and subtract the positive and negative transactions and provide a current location and qty stored figure.

Thanks for coming up with that. I actually came up with a query that shows current inventory status (or as of a date) like yours does, but yours is cleaner.

The part that I'm really struggling with is creating a report for accounting though. I need it to show how many pallets were stored during a time period, grouped by customer.

i.e. on Sep 1 customer A sends us 100 pallets, Sep 13 we ship out 50 pallets. On Sep 30 I need to create a report that shows Customer A stored 100 pallets for 12 days, and 50 pallets for 18 days, multiplied by customer A's daily pallet storage rate.

This is what I am currently struggling with, and thinking I need more tables

 

[Minty]

I think you should be able to do what you want with the basic information in the format above. It isn't super straight forward, just takes a little more thinking through.

Breaking it down into small steps will help you see where you need to get to.

Using your example as a starting point, Create a query that pulls in the "Opening Balance" for a customers inventory at a specific date. So that would be the same query as above but with the criteria like

WHERE tblMovements.TransactionDate < RepStartDate 'We'll determine this is #2017/09/01# for the purpose of this)

Now the tricky part is determining the transactions and dates in between. You will have to use Sub queries, or probably for ease of visibility create separate queries and save them, then link those back into your next query stage. You will end up then using these in a report - or possibly an excel export.

So you now create a list of transactions up to your end date.
Same query again but with some added fields (This is air code untested but it will prompt you for repStart and repEnd dates, Ultimately you would get these from a form to generate the report / export).

SELECT tblMovements.TransactionDate , tblMovements.PartNumber, Sum(tblMovements.Qty) AS SumOfQty, tblMovements.BinLocation , NoOfDays: DateDiff("d",RepStartDate,tblMovements.TransactionDate)
FROM tblMovements
WHERE tblMovements.TransactionDate >= RepStartDate AND tblMovements.TransactionDate <= RepEndDate
GROUP BY tblMovements.TransactionDate, tblMovements.PartNumber, tblMovements.BinLocation;

This will get you started. Depending on your business model you may well need to also get a running sum query going, I would have a look here for an example https://www.access-programmers.co.uk/forums/showthread.php?t=45434

 

[Pat Hartman]

It takes as many tables as it takes to properly hold normalized data - however many that is. The number of tables used isn't relevant at all.

Unless you calculate the balance every day, you can't use start of period and end of period as the basis of your count since that would exclude any shipment that came in and went out within the dates. So if you are calculating August and 100 pallets came in on the 2nd and went out on the 29th, they would never be counted.

I did this for a client of mine who was the one PAYING the storage charges rather than BILLING them. To do this accurately, you actually need to derive a daily balance. To derive a daily balance, you need to use a date table that contains the date of every day you want to bill for. If you are kind and don't charge for holidays or Sundays, don't include those dates in your date table. Then to get a balance for each day, you need to create a query that selects the dates from the date table for the period you want. Then a second query to join to that query using criteria that only selects dates that fall between the receive date and the ship date for an inventory transaction.

I suggest first limiting the dates from the date table because you will be doing this weekly or monthly and so you don't want to include dates outside that range. When you create a Cartesian Product (which this will be), you are taking the number of rows in table X and multiplying them by the number of rows in table Y so limiting table Y to 7 or 31 days will help significantly to reduce the overhead of this join.

 

[Mark_]

ryancgarrett,

One approach is to have a “Current Pallet Count” in your Billing records along with a from date, thru date and a bill number (Auto inc). This lets you know how many pallets said customer has at the beginning of a billing period and you know when you last billed them.
From there, I’d put together a report that pulls starting date and starting pallet count from the last Billing record. You will be saving “From date” (equal to your last billing date +1), “Thru date” (prompted from user), and pallet count. You then add a new Billing record (use new records “Billing number”) and start going through all of your transactions with a date equal or greater than your from date and equal to or less than your thru date.
Before processing default a user defined date variable (ProcessingDate) to be your beginning date. Also define ProcessingDays (used to count number of days pallets are stored) and BillingAmount
For each record,
1) Fill in the records “Billing No” (shows what bill it is paid on)
2) Add or subtract the number of pallets from the current count of pallets
3) If the date of the transaction is different than the Processing date
4) A. Print the line item (including BillingAmount) (Line item date is Transaction Date – 1)
5) B. ProcessingDays = TransactionDate – ProcessingDate
6) C. ProcessingDate = TransactionDate
7) D. BillingAmount += (Pallet Count * Rate * ProcessingDays)
8) Add BillingAmount to your Billing records “Amount Due”
Only tricky part is you need a fake “Line item” at the end of your report (in the report footer) that does the same calculations but using ProcessingDays = Thru Date – ProcessingDate that is used only if your did not receive or ship out anything on the last day of your billing cycle.

This should give you something that looks like this:
Start of billing cycle = 1 OCT 2017, Bill 100, Pallet Count = 100 rate
LINE ITEM 1) Charge 1 OCT 2017, 100 Pallets at 1p/day = £1
2 OCT 100 Pallets In; Current Count = 200
2 OCT 50 Pallets Out; Current Count = 150
LINE ITEM 2) Charge 3 OCT 2017, 150 Pallets at 1p/day = £3
4 OCT 150 Pallets In; Current Count = 300
LINE ITEM 3) Charge 8 OCT 2017, 300 Pallets at 1p/day = £15
9 OCT 300 Pallets out
LINE ITEM 4) Charge 15 OCT 2017, 0 Pallets at 1p/day = £0 (End of billing period)
BILLING SUMMARY: 15 days, total charge £19.

Starting with this in mind you can work backwards for what tables and data you will need. If you use multiple rates, store a rate code in each record and run this type of report for each rate code for that customer before doing your totals.