Question Using Access and QuickBooks for Inventory Management and Accounting Integration

[Clark3934]

I have recently been charged with the job of overhauling the way a small business runs their inventory. I am not a database professional, so I am very new to this whole process!

An overview of my problem:
Our company sells toothbrushes. We keep toothbrushes in our warehouse and sell them to companies like Wal-Mart. We have 5 total employees, so there are no separate accounting and sales divisions. Everyone works together.

Currently, the company is set up using a program called AdvancePro. As far as I can tell, AdvancePro is nothing more than specialized inventory management database. It keeps track of their warehouse inventory, purchase orders, packing slips, and even houses their customer, rep, and distributor databases.

Every night, after taking all orders, etc., they update QuickBooks by synchronizing it with the data in AdvancePro. AdvancePro updates all the product and customer information into QuickBooks which then keeps track of all the financial data. It allows them to create financial reports and keep track of accounting.

However, I have seen that this system comes with limitations. There are many fields in AdvancePro that do not exist. It does not have the ability to generate custom reports on the data. I cannot create queries into the data. Worst of all, it does not allow the exporting of data in text or .xls form!

So, I have decided to create a database for managing all company contacts, purchase orders, reps, distributors, inventory, et al. However, I have a very big concern. The CFO of the company is concerned with updating the database in QuickBooks. First of all, is this necessary?! I still don't understand why she insists that this be done on a daily basis. Secondly, if it is necessary, how would I make sure that the records updated in my Access database would be accurately reflected when she tries to run quickbooks reports?

Thanks. Any suggestions for creating alternate database solutions would be welcome as well!

 

[ByteMyzer]

The CFO of the company is concerned with updating the database in QuickBooks. First of all, is this necessary?! I still don't understand why she insists that this be done on a daily basis.

It is absolutely necessary to keep the financials in-line with the inventory. Your CFO is absolutely right! Daily inventory transactions affect the amount of money that moves in and out of the various accounts.

Instead of creating a brand new inventory application from scratch, have you investigated the possibility of using MS Access to link to the database in AdvancePro? If this is an option, you could use access to:
* generate custom reports on the data
* create queries into the data
* export the data in text or .xls form

If, however, you decide that designing a whole new system is the way to go, you should investigate the options for syncing the data to Quickbooks, for the sake of data integrity and keeping the business honest. If you do not, and it remains a manual process, you have nothing substantial to prove the integrity of the numbers between the two applications. You might check with Intuit Software and see if they provide any facility for you to do this.

 

[Clark3934]

It is absolutely necessary to keep the financials in-line with the inventory. Your CFO is absolutely right! Daily inventory transactions affect the amount of money that moves in and out of the various accounts.

Instead of creating a brand new inventory application from scratch, have you investigated the possibility of using MS Access to link to the database in AdvancePro? If this is an option, you could use access to:
* generate custom reports on the data
* create queries into the data
* export the data in text or .xls form

If, however, you decide that designing a whole new system is the way to go, you should investigate the options for syncing the data to Quickbooks, for the sake of data integrity and keeping the business honest. If you do not, and it remains a manual process, you have nothing substantial to prove the integrity of the numbers between the two applications. You might check with Intuit Software and see if they provide any facility for you to do this.

Thank you! I personally had never heard of AdvancePro before taking this job (then again, I've never dealt with inventory management). Is it a popular product?

Since AdvancePro does not export data any format, how would I go about accessing its database? I'm not sure if its stored in some proprietary format or not.

Is there anyone here with experience using the software?

 

[DCrake]

Simple Software Solutions

As with most propriertory systems they usually come with a backup and restore utility. When you perfom a back up does it give any indication as to type and location of the backup file. This should give you some indication as how the data is stored.

CodeMaster: