Need some pointers on creating a simple inventory database
February 22, 2012 7:02 AM Subscribe
Need help setting up a simple Access database. I'm trying to reconcile a bunch of inventory, which is all recorded in multiple Excel spreadsheets. I want to import all the Excel files into an Access database so I can match them up all at the same time.
What I have is a master spreadsheet with the inventory for our organization. This master spreadsheet is updated every week and has about 50,000 lines.
I also have about a hundred other spreadsheets with items that were removed from our facility. I need to compare all these other spreadsheets against the master inventory to check that none of the removed items are still on it.
I know how to use the MATCH function in Excel to compare the spreadsheets one at a time but that's time consuming when I have around 100 files.
I do know how to import all the spreadsheets into Access as tables, but I'm lost on how to link the tables or run queries from there so I can get a report showing which property items need to be taken out of inventory.
thanks!!!
What I have is a master spreadsheet with the inventory for our organization. This master spreadsheet is updated every week and has about 50,000 lines.
I also have about a hundred other spreadsheets with items that were removed from our facility. I need to compare all these other spreadsheets against the master inventory to check that none of the removed items are still on it.
I know how to use the MATCH function in Excel to compare the spreadsheets one at a time but that's time consuming when I have around 100 files.
I do know how to import all the spreadsheets into Access as tables, but I'm lost on how to link the tables or run queries from there so I can get a report showing which property items need to be taken out of inventory.
thanks!!!
Do you have to have the Removed files separate like they are now, or can you combine them into a new file (keeping copies of the old versions, of course)? If you have two files, one with current inventory and one with removed items, you could use MATCH or even COUNTIF to compare them all at once.
Similarly, you could import all of the files into one table in Access (a Removed table). Then it would be a simple matter of importing the Inventory Table and comparing the two columns that house the product codes (or whatever the unique identifier is) in each table. If you need to know which file each record came from, you could add a column to the Removed table called "File" and update the null values after each import.
posted by soelo at 9:33 AM on February 22, 2012
Similarly, you could import all of the files into one table in Access (a Removed table). Then it would be a simple matter of importing the Inventory Table and comparing the two columns that house the product codes (or whatever the unique identifier is) in each table. If you need to know which file each record came from, you could add a column to the Removed table called "File" and update the null values after each import.
posted by soelo at 9:33 AM on February 22, 2012
Response by poster: desjardins - the two most important fields are BARCODE and SERIAL#. These are common to the master inventory sheet and the removed inventory sheets. Some of the inventory sheets have typos where the serial number has been entered as the barcode.
There is a lot of other data in the sheets but all I really care about for the time being is matching the barcodes and serial numbers. The other data is stuff like location, cost, acquisition date, etc.
The property was removed in increments so I have the sheets of removed property broken down into increments. I have started importing tables into the database, using the master inventory sheet as one table and then creating a table for each increment of removal and merging the removal sheets into one table for each increment.
posted by smoothvirus at 10:31 AM on February 22, 2012
There is a lot of other data in the sheets but all I really care about for the time being is matching the barcodes and serial numbers. The other data is stuff like location, cost, acquisition date, etc.
The property was removed in increments so I have the sheets of removed property broken down into increments. I have started importing tables into the database, using the master inventory sheet as one table and then creating a table for each increment of removal and merging the removal sheets into one table for each increment.
posted by smoothvirus at 10:31 AM on February 22, 2012
importing spreadsheets from Excel is easy; I design all my databases in Excel, and then import to Access to make the relationships.
What you need to link the tables are Unique IDs (UID) - any combination of letters or numbers which are unique to each item and which can serve as the Primary Key for the table in Access. Primary keys cannot repeat within a table, but are used to connect to either Primary Keys in another table (1-to-1 relationship) or non-primary keys in another table (1-to-Many relationship). (You can do many-to-many relationships, but you need to use a special junction table for that).
posted by jb at 10:33 AM on February 22, 2012
What you need to link the tables are Unique IDs (UID) - any combination of letters or numbers which are unique to each item and which can serve as the Primary Key for the table in Access. Primary keys cannot repeat within a table, but are used to connect to either Primary Keys in another table (1-to-1 relationship) or non-primary keys in another table (1-to-Many relationship). (You can do many-to-many relationships, but you need to use a special junction table for that).
posted by jb at 10:33 AM on February 22, 2012
Are either the barcodes or serial numbers unique to each item that you want to track? If so, that would make a good primary key.
However, the question is: what do you want to relate the list of products to, and what kind of queries do you want to make?
posted by jb at 10:34 AM on February 22, 2012
However, the question is: what do you want to relate the list of products to, and what kind of queries do you want to make?
posted by jb at 10:34 AM on February 22, 2012
If what you want is a simple count (I had 10 widgets, and then 2 were removed here, and 1 removed another time - and now I want to know how many I have - ie 7), the you might be better off with a spreadsheet, which makes on-the-fly calculations easier. (Subtract numbers in your REMOVE sheet from your MASTER sheet).
That said, Access is very good for making Forms which lets other people enter data without having to a) figure their ways around the tables, and b) limits what they can change so that they can't screw anything up. (But since I've always done my own data-entry, I never did learn how to make forms :(
posted by jb at 10:38 AM on February 22, 2012
That said, Access is very good for making Forms which lets other people enter data without having to a) figure their ways around the tables, and b) limits what they can change so that they can't screw anything up. (But since I've always done my own data-entry, I never did learn how to make forms :(
posted by jb at 10:38 AM on February 22, 2012
Response by poster: Yes, the barcode and serial number are unique to each item.
What I need is a table of items in the master inventory sheet where either the barcode or serial number matches an item in any of the sheets of removed property.
posted by smoothvirus at 11:02 AM on February 22, 2012
What I need is a table of items in the master inventory sheet where either the barcode or serial number matches an item in any of the sheets of removed property.
posted by smoothvirus at 11:02 AM on February 22, 2012
Best answer: What you want is a small relational database. This is not terribly difficult and you can get away with not following all the rules but essentially you have to design your tables to relate to one another.
Chances are, there is a rough design already with each spreadsheet having a "table" of data that needs to connect to another. Divide the tables that relate to one another via one and only one serial number into one group and the tables that have one serial number but many records.
This second group needs a one-to-many relationship.
For example, a serial number or bar code belongs to all twelve mops in your store.
Luckily, the most common relational database tutorial I have seen is an inventory system.
Try searching for tutorials and matching up your items to the tutorial list. Here is a starting point.
posted by occidental at 4:43 PM on February 22, 2012
Chances are, there is a rough design already with each spreadsheet having a "table" of data that needs to connect to another. Divide the tables that relate to one another via one and only one serial number into one group and the tables that have one serial number but many records.
This second group needs a one-to-many relationship.
For example, a serial number or bar code belongs to all twelve mops in your store.
Luckily, the most common relational database tutorial I have seen is an inventory system.
Try searching for tutorials and matching up your items to the tutorial list. Here is a starting point.
posted by occidental at 4:43 PM on February 22, 2012
This thread is closed to new comments.
posted by desjardins at 8:58 AM on February 22, 2012