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:
Code:
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''.