Homeless Access Template Question

[jwolsr]

Believe it or not there have been and still are many using and/or referring to the Access template NorthWind. This template is an excellent beginning for depicting a manufacturer’s environment but there is no one upgrading, correcting or improving it. Many have asked questions in several forums but frequently there are no answers and the database template remains a homeless orphan.

A compelling question that has been repeated is why the core table “Invoices” never gets populated with currency amounts. Anyone trying this will see that an order can be created and populated with products and quantities from inventory that is priced, shipped to a customer on a specified date by a specified carrier, and then invoiced complete with ship date, amounts, prices, totals, etc. BUT… the invoice data never gets into the “Invoices” table.

Surely someone who has studied the VBA has uncovered the error by now but it has been kept a secret. Can anyone shed a light on this?



Thanks

[June7]

I've never looked at the Northwind in depth. Are you saying the Invoices table does not get a record for the Invoice with an InvoiceID and InvoiceDate? Surely there must be a record! I expect the other data (amounts, prices, totals) are not saved in the invoice record because that data is calculated.

[jwolsr]

Yes! The data is available thru queries. The data is used to prepare an invoice but when it's through only the OrderID and Invoice Date go to the table. The other three fields are $0.00.

It appears to be errors in the VBA code.

[June7]

Saving calculated data is usually unnecessary and often ill-advised. Saving calculated data must have strong justification. I am surprised the fields even exist in the table.

[jwolsr]

That may be true... I don't know.

However, this NorthWind Access template has been around for many years (2003?) and this glaring issue has been there with nobody seemingly stepping up to correct it. The function of the Invoice table is necessary and obviously an efficient way to have the summary data readily available. It's a means to export economic data to an accounting platform.

The reason for the question is to solicit someone to investigate/analyze the code and correct it.

So far it remains unsolved. Any takers??

[June7]

Whether or not there is anything to correct is opinion. Some might say the correction would be to remove the unnecessary fields. Code modification possibly not terribly difficult and someone somewhere probably has for their needs but unless it is published by MS and replaced in the template library, not a real benefit. Even posting to a thread in forum has limited benefit. Threads get buried and if you don't know exactly what to search for, it might never surface.

Every accounting software I used requires transactions, not summary data. And if export is needed to maybe a tax calculator like TaxAct, that could still be accomplished with queries.

Is this a modification you desire for your use?

[jwolsr]

Thanks for your response. You are the first that I and others who posed this concern have seen and it is appreciated.
Yes, I certainly could and would use the Invoice table if it functioned as intended. There are 'work-arounds' which are obviously what's being used ineffectively due to the broken table.
I wasn't aware there were unnecessary fields in a table that someone took time and effort to design. Each field appears to have a purpose. Why would it be there with obvious missing data? A table with all zero fields is surely unnecessary so I guess that's why you asked but the point is why not give it the functionality the authors intended?
The particular VBA associated with the "Create Invoice" command is too complex for my limited experience/knowledge of Access coding because it uses numerous functions and jumps around so much that I get lost keeping up with the flow. I'm not a programmer/developer and there's probably efficiency reasons to use one-line functions and jump in and out of them. Having only Fortran, Cobol and Excel macro writing experience it's out of my scope as an engineer.

If there's no "benefit" to make this correction then so be it. I guess MS has enough errors to correct that have more value. I would have thought that anyone using NorthWind as a DB starter or for reference would like to see it error free.

Thanks again.

[jwolsr]

June7: I forgot to mention I'll be out for a couple days in case there's no response.
jwolsr

[June7]

Who can say why the developer created the fields and then didn't followup with code to use - maybe it is the focus of a tutorial somewhere. Speculation is futile, the db is what it is. I certainly don't have the incentive to modify. If it is beyond your capability then I guess the question is, is it worth paying someone to modify the db to your requirements?

Seems I've seen more than one version of database called "Northwind".

[Dal Jeanis]

The other possiblity - which seems very likely from the existence of Invoice records numbered up into the thirties while newly created records number in the eighties, is that the original design included invoice records being created, but at some point that design was changed as unnecessary.