A daunting data capture query


I have just been hired to replace an access database with a SQL database, reporting to be done in SSRS. The company in question manufactures hydraulics everything from individual components to complete pieces of equipment. They have an issue they would like me to resolve on top of the migration for years they have problems getting their pricing to work correctly. The access database has thousands (9000 approx) of formulas in a pricing/markup table which is called anytime their ERP program needs to be apply pricing/markup costs for a particular components or entire pieces of equipment being manufactured. A BOM (Build of Materials) team is regularly updating price lists for the manufacturing team so price comparison won't work.  The issue is that formulas aren't always applied to correctly (normally they aren't applied when they should) due logic flaws in the legacy ERP system. The company wants to try find a way to flag a formula that perhaps isn't being utilized as often as it should. The legacy system in questions is due for an update when they have cash and the business stomach for it.

My idea:
1. Group every formula to into an individual component and/or pieces of equipment as soundly/tightly as possible. Then every time the formula is called it's counted and stored in a separate table. Then at the end of the month one could tally the amount of components/equipment being manufactured and the amount of formula calls doing a statistical analysis on that.

Any others?

Any advice is always appreciated

Thank you
ZackGeneral IT Goto GuyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>The access database has thousands (9000 approx) of formulas in a pricing/markup table
Based on the description I don't see anything in SQL Server that will help you where Access can't, unless we're talking some of the standard Access limitations such as the size of the database, number of transactions, number of concurrent users, etc.

>The company wants to try find a way to flag a formula that perhaps isn't being utilized as often as it should.
To get a handle on this...


Create a 'log' table,


Create a function that accepts @FormulaName and @WhateverElseYouCareAbout and in that function insert a single row into the 'log' table


Go on an easter egg hunt across your 9,000 formulas and add the above function call to each one. Then over time you can watch the log table grow, and 'get a handle' on how formulas are being called.  I pulled this off at a previous client that had Access reports damn near all over the country and no tracking mechanism of who ran what, and it worked great.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
replace an access database with a SQL database
Technically you can't replace an "Access" database with a SQL Server database since SQL server supports only a small part of what would be in an Access application.  You can always simply "upsize" the tables from Jet/ACE to SQL Server and keep the Access FE although you may have to make changes to the FE to make Access behave properly in a client/server setup.  It depends on the skill level and foresight of the original developer how much work the conversion will involve.  If you want to get rid of Access entirely then you need to also replace the FE with something else.  

Don't forget that changes will be required to the ERP also since it will now be using SQL tables rather than "Access" tables to get the formulas.

Since the ERP is the place where the formulas are used, that would be the place to do the counting.  Jim's suggestion could work for you depending on how the formulas are actually executed in the ERP but you would have to update the 9,000 formulas as he said.

Another possibility is using a Trigger.  But, that depends on how the formula table is used in the rest of the application.  If it is only used by one form to enter the formula and then by the ERP, you might be able to use a trigger to log reading that table.  The problem with this method of course is you don't actually know what the query or app is going to do with the formula once it is read so this concept would only work if the access to that table is pretty much limited to the process that executes the formulas.
ZackGeneral IT Goto GuyAuthor Commented:
Thank you once again Jim your advice is always most helpful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Enterprise Software

From novice to tech pro — start learning today.