Using Access to Reconcile Difference Between Two Tables of Inventory

[DMDG]

Hi all,




[Newbie Disclaimer: I'm an experienced excel user, but fairly new to Access. (Do I hear a collective grown from anyone reading this? ha ha) I apologize if I fundamentally misunderstand any of the features of Access in my inquiry.]


I have two spreadsheets which contain records of the same inventory items and their movements in and out of inventory. The items have the same item numbers in both systems. I have created an Excel sheet to compare total inventory levels for each item between the two sheets. However, the number of recorded movements are so many as to make the spreadsheet nearly unmanageable (responds VERY slowly even on a fast system).


I have heard that Access can handle large amounts of data much more easily than Excel. I'm hoping to create an Access Database which will:

1. Allow me to load inventory transactions into two separate tables.
2. Allow me to calculate the net inventory level (quantity) for each item which results from the various movements on the above spreadsheet.
3. Allow me to compare inventory levels for each item and identify items which have a discrepancy.
4. Allow me to identify the date and transaction ID at which inventory levels become out of balance between the two tables. (figure out where things went wrong.)

Before I go down the road of attempting to build out this database, is the above task something which Access can easily accommodate? Or am I trying to get Access to do a job it is not meant to do.


I'm attaching an example of the spreadsheet I've used to identify inventory discrepancies in case it better communicates my intent.



Thank you for any feedback or ideas you may have.

[June7]

Does this help http://allenbrowne.com/AppInventory.html

Mostly anything can be done with enough code.

[DMDG]

Hahaha True. I guess I should have qualified the question then. Is Access the right tool for this job, or would I have to go through a large amount of manipulating/coding in Access to get the same thing that I have on the attached spreadsheet above? Again, the goal here is to get an alternative to Excel since the quantity of data is too big for Excel.

Thanks again.

[DMDG]

And thanks for the link. Somehow I missed that the first time through.

[June7]

Even if VBA in Access would be required, I think Excel has already shown itself to be the wrong tool for storing this data.

If Access queries and VBA don't seem up to the analysis task, could maybe export subsets of records to Excel to do that.

There's always a way.

Seems the immediate need is to get relational database designed and progress from there.

It's not clear to me what 2 sheets you compare. Don't really want to go into in-depth analysis of the workbook.

[DMDG]

I appreciate the feedback. In that case I'll start setting up the database with the items, various inventory transactional logs and then see where I get. I'm familiar with Excel VBA. Maybe with a few tips along the way I'll be able to get there.

Thanks for the guidance.

[June7]

If you aren't already familiar with relational database principles and Access functionality, might work through an introductory book on Access or online tutorials first.

[DMDG]

I am familiar with the relational database principles, and have source table from which to pull easily generate source lists. Once it comes to summing products by multiple criteria (item number, batch number, data and transaction type) is when I might find myself beyond my current level of experience in Access.

[June7]

That's where studying a book could give you a head start. What you just described is an aggregate query.