Inventory usage

[txrules]

I have built several access databases, but I do not write VB. I have edited some VB and made things work for my needs. I get by using multiple queries and canned macros. Things that would make you pros cringe.
As for changing my thinking, no problem. I am open to taking a different approach. I am building from the ground up so it can be anyway needed.
As for the task I want to accomplish, it is not a normal inventory database. It needs to work backwards and I need to calculate the daily chemical usage. I do not need to invoice anything. We have over 1000 sites and we must report the daily and monthly usage at each site.
The data we have to work with is a delivery report and a physical inventory. We currently use an Excel sheet with the following columns: StartingInventory, Deliveries, EndingInventory, MonthlyUsage, DailyRate. MonthlyUsage = Starting + Deliveries - Ending. DailyRate= MonthlyUsage/DaysInTheMonth.


The problems are: we have to make a new spreadsheet each month and move the ending numbers to the beginning numbers, deliveries are summed up elsewhere, data doesn't share well, physical inventories have to be made at the end of each month.
I was hoping to fix it by making a database that will fit our needs. I think I have the bulk of it figured out but I could change if needed.
If someone could show me how to get access to make the similar calculation we get in Excel, I think I could get this thing going.

[ajetrumpet]

tx,

does 03 version have an inventory template? At this point, I can't remember. if it does, I would examine it and find the location of the calculations you need. I'm sure it's similar to what you're doing.

But Excel can have hundreds of thousands of rows, can't it? I believe at one time it was limited to 65535 like the gold 'ol 8-bit nintendo, but that's not the case anymore.

IMO, this can be done so much easier in Excel. Why doesn't it share well? From what I've experienced, xls can be shared just as easily as accdb's or mdb's can. And as far as physical inventories are concerned, I doubt that has anything to do with Access. Physicals will always have to be take to guarantee the shrink rate, regardless of what any program says.

As far as the ease of calcuating and viewing data, I'm also positive that you guys can use one sheet per year, instead of one per month, unless you're making more than say 10K - 20K inventory entries a month on a sheet (which I would not see happening).

Excel can do a heck of a lot more with inventory than Access can, IMO, because it is a flat file. That makes, for one thing, calculations run much quicker if you're doing it correctly. And don't forget the locking ability! Can be a pain in the butt, but it sure helps sometimes!

And to give you a sample of the automation excel is capable of, say you want to create a new sheet for the new year tomorrow. Put a little button on the current sheet and write something like:

Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = CStr(Year(Date) + 1)
Sheets(CStr(Year(Date) + 1)).Range("a2") = Sheets(CStr(Year(Date))).Range("a1").End(xlDown)
   MsgBox "Sheet for new year created."

Which of course copies over the old inventory level to the new year, given the column for that is in ''A''.