Weighted-average inventory costing.

[evander]

Does anybody know how to create a merchandise inventory database containing a query (or queries) that calculates weighted average cost? I have read Joe Celko's solution, but that doesn't really the way weighted average works.

I have tried weighted average (and even perpetual average) costing in another database (SQL Server) but I ended up using cursors. I know how to create some workarounds, but I am at the risk of suffering from data inconsistencies.

I was wondering if you guys can help me out with this.



Thanks a lot!

joe

[pkstormy]

Keep in mind that you don't 'have' to abide by the 'golden' rule of never storing the summed value in the table. Sometimes it's easier to actually add a field to the table which stores the summed value of other fields (I would do this for some accounting db's I designed.) It then makes it easier to calculate averages and other values based on just this field.

I've seen posts where users say to 'never' store the summed value in a table. This is 'NOT' a rule you always have to follow! There are many different types of applications I've designed where storing the summed value made other coding much easier along with other benefits (which I won't get into details on).

When it comes to the need of writing cursors to do simple calculations versus just having a summed field in the table itself to do your other calculations off of, the benefits can outweigh the cons of simply using this field.

[evander]

Oh thanks for the reply. Didn't realize I committed grammar mistakes up there...
Actually, I have seen large systems that actually store calculated amounts in permanent tables. I know how to implement this design in a small database, but I'm stuck in dealing with backdated transactions whose values can affect the computations of other transactions that come after it.

I'm well-versed about the accounting side of inventory (coz my job is accounting), so I just need to know the IT side of it. Do you know any book I can read about this subject? I really appreciate your help.

Thanks!

joe

[pkstormy]

What I like to do (versus SQL Server stored procedures or cursors), is to link the SQL Server tables into the front-end mdb file. I can then make use of queries and coding in modules (keeps all the code in one place and makes development time much quicker.) I use ODBC to link the SQL Server tables and some code I have which automatically creates the ODBC on the user's computer when the user opens the app.

I found (when developing a db that had 5+ million records), that there wasn't a significant difference in the speed if I linked the SQL Server tables and wrote the code in vba in a module versus a stored procedure, cursor or trigger (and actually found some faster performance using vba in a module).

If you're more of a SQL Server guy, this may not be the route for you to go.

When it comes to tracking transaction type data, there are 2 key fields I use in all my data tables. One being DateEntered (date type), and the other being EnteredBy (text type). On the MSAccess forms, I set the DateEntered default value to =Now() and for the EnteredBy field, I set the default value to =getuser() which is a routine I wrote to automatically grab the user's loginID securely (which works very well and is only a couple lines of code). (even though you can also set the default value to the user on the SQL Server field as well.) But the getuser() also allows me to implement a lot of other tracking of the user type coding in the mdb.

I also will 'break' the golden rule on normalization rules and will put some of the 'grouping' type fields in my main tables as well as the relational tables (makes it ideal if you're totaling large recordsets since every linked relational table in a query will also slow down performance.)

If you're interested, here's some links which may be helpful:

To automatically create ODBC DSN's:
http://www.dbforums.com/6282465-post37.html

To grab the user's loginID in vba:
http://www.dbforums.com/6274790-post20.html
Note: You can also use the =GetUser() in an expression in a query or anywhere else. This is a great way to securely get the user's loginID.

And here's a nice vb script I use to launch my front-ends. This script has really been a blessing for me and prevents things such as "locked by another user" errors as well as many other benefits (I can have 100 users in the frontend mdb without any problems! I can also easily tell who is currently using the mdb, when they last used it, copy new frontend code without making any users close out of the app, etc...etc...)
http://www.dbforums.com/6274786-post19.html

I also have several coding snippets posted which do a variety of things. You can find these along with coding snippets others have posted here:
http://www.dbforums.com/microsoft-ac...code-bank.html

Other than that, I can't really help on the backend transaction side of things (again, I keep all my coding in functions in modules in the front-end). I do monitor traces on SQL Server but I no longer write things such as stored procedures, cursors or triggers any more. It's so much easier (for me) utilizing the power of queries with linked tables along with just writing functions in modules which I'll then simply import into another mdb design (cuts down development time). I did write a few SQL Server triggers to update data in another table but I then found it to be so much easier to just write a function in a module to do things like this.

As far as a good book, the MSAccess Developers book (by Sybex/Ken Getz) is the one book I keep handy. It has a lot of great coding examples and doesn't require 'installing' an application to see the examples (it just has the mdb files with the examples on the cd.) I hate the books which require you to install an application just so you can see the coding examples. This is really a great book though and there are now several different editions to meet your needs. I still use some of the MSAccess 2000 modules in my apps (such as the FormInfo class module which let's you do a lot of neat things with the design of forms.) For example, see here for some really neat form designs: http://www.dbforums.com/6301669-post51.html (ie. you can make a circular form, a translucent form, a neat drop-down menu type system, a fade-in/out form, etc...)

Hope the above helps a little.

[DaveT]

To pkstormy,

FYI, maybe it's just me, but I can't open the zip files that you reference. Using WinZIP 14.5 on Windows 7 and get the error message:

Cannot open file: it does not appear to be a valid archive

[evander]

To PKStormy:

I'm quite overwhelmed by your reply.

What I like to do (versus SQL Server stored procedures or cursors), is to link the SQL Server tables into the front-end mdb file. I can then make use of queries and coding in modules (keeps all the code in one place and makes development time much quicker.) I use ODBC to link the SQL Server tables and some code I have which automatically creates the ODBC on the user's computer when the user opens the app.

I can imagine this way of designing a system. I'll use this approach in my next projects.

If you're more of a SQL Server guy, this may not be the route for you to go.

Yes, I'm more of a SQL Server guys indeed. Not because I consider myself expert in that area, but simply because it's what I am first accustomed to. I'm still learning. Only this year I realized how powerful the combination of Access and SQL Server can be.

I also will 'break' the golden rule on normalization rules and will put some of the 'grouping' type fields in my main tables as well as the relational tables (makes it ideal if you're totaling large recordsets since every linked relational table in a query will also slow down performance.)

That sounds great! How does it work? I can use this concept in creating financial statement queries.

For example, see here for some really neat form designs: http://www.dbforums.com/6301669-post51.html (ie. you can make a circular form, a translucent form, a neat drop-down menu type system, a fade-in/out form, etc...)

Actually, these things (I believe they require API calls?) are pretty much advanced for me. I only concentrate on designing accounting databases.

I'll apply your ideas in designing an inventory databases. I can't be really perfect this time, but I'll try.

Thanks for replying!

joe

[pkstormy]

To pkstormy,

FYI, maybe it's just me, but I can't open the zip files that you reference. Using WinZIP 14.5 on Windows 7 and get the error message:

Cannot open file: it does not appear to be a valid archive

All the examples on dbforums have been re-posted into the coding examples section on this website: https://www.accessforums.net/code-repository/

[dcartford]

The way to tackle the average costing, is basically having a sproc to update your stock item unit cost each time you perform a receipt or sale of your product. If you are still looking for some books on accounting database design, you may try downloading this book from accountingdes.com

[pkstormy]

To pkstormy,

FYI, maybe it's just me, but I can't open the zip files that you reference. Using WinZIP 14.5 on Windows 7 and get the error message:

Cannot open file: it does not appear to be a valid archive

You may need to change your Internet Explorer Intranet Security to allow downloads from this site. Just add this site to the allowable sites under Local Intranet (under Security tab) IE.

ex: Tools -> Internet Options -> Security -> Local Intranet -> Sites -> Advanced (then add this site).

[evander]

To dcartford: Thanks for telling me about the e-book link.