Access database master inventory from multiple product tables

[roluko]

Hi,
I have just recently joined a new company, and one of my tasks is to see if it is possible to convert 5-6 separate excel inventory files into one access database.

I have little prior experience with database design, and have started by googling, trying to find examples I can adopt.

We currently have one 'master parts' excel file, listing all inventory items. Parts used in production plus all other items in the company, like furniture, coffee machines, PC's, switches, spare parts etc. The 'master parts' excel file currently have 5000+ rows, and keeps growing as we identify and register new items into that file.

We use a barcode scanner to manage inventory in the 'master parts' excel sheet (parts in/out, parts counting etc.) These tasks is performed by several excel VBA macros, triggered by the barcode scanner. All VBA scripts is stored in a macro enabled excel sheet, triggered to run by the barcode scanner.

Currently, each part type have their own excel inventory file, with their own part naming convention. For example, the 'Equipment' excel file have a list of all equipments, with unique names like 'EQU.XXXX' where XXXX is incremented by one for every new equipment registered. Then the same for instruments (INST.XXXX), lifting equipment (LFT.XXXX) etc.

At the moment, we have about 6 or 7 of these individual inventory excel files.

As for the 'Master Parts' excel file, we have ordinary parts used in the workshop and production together with MRO products (Maintenance, Repair and Office supplies). For the 'ordinary' parts, we use the supplier's partnumber, if possible.

Then we manually add all the parts from the individual inventory excel files into that same master parts file, so that we can easily use the barcode scanner to scan parts in/out.

Parts scanned in/out is added to two or more 'parts used by project' excel files (performed by the excel VBA script mentioned earlier), and that 'parts used by project' is manually copied or in some cases moved into other excel files used by accounting for invoicing etc.

Some of the inventory excel files have lots of columns for special information required by each part type, and the master parts excel file currently have some 90+ columns.....making it really easy to get lost and make mistakes.
Currently, adding new parts to the individual inventory excel files and then into the master parts file is done manually.

I have started to create an access database to eventually replace all these individual excel sheets, but are currently stuck on how to add parts into the master database from the individual inventory excel files. How do I create a form where users can just click the 'add new part' button, select 'part type' (equipment, instrument etc), and then be guided through entering correct information. On that same topic, how do I structure the tables in such a way that they get their own unique naming convention, and at the same time, is added to that master table?
And how do I assign a scannable unique identifier into the master database? We want to be able to identify part types by the label we create and stick to each item. Label include barcode (QR code, to be precise, for example EQU.1013, INST.1014 etc), text representation of the barcode, and name+description of item. Where suppliers partnumber is used, that will be the QR code to use.

In the process we will also create new tables for transactions, suppliers, manufacturers, alternative suppliers, products etc.
As for products table, we want each unique product in a table, and then assign either an internal ID, a EQU, INST etc ID, and then the master parts table and individual inventory tables links to the products table for individual product details.

Could anyone give some pointers where to look for how to solve the issue with all the individual inventory excel files (or tables)?
Any hint, suggestion or pointer would be appreciated.

 

[CJ_London]

I assume each of the excel files have the same format and other than the fact the data is in separate tables, it is otherwise normalised.

if this is the case you need to mimic the deign in Access, but include an extra field for 'asset type' - the equivalent of your excel file or worksheet name - plus of course an autonumber PK field

You can add data from excel enmass by a variety of different ways - using transferspreadsheet, copy/paste (if columns are in the same order), sql, etc

how do I structure the tables in such a way that they get their own unique naming convention, and at the same time, is added to that master table?

don't understand this, you will need to provide some example data

And how do I assign a scannable unique identifier into the master database?

I presume you mean a bar code. Barcodes are just text (usually number characters) so just add a field call 'barcode' or 'QRCode' or similar. Barcodes can be created quite simply in Access, but think QR codes need more work

 

[Uncle Gizmo]

It's quite a common requirement, moving from Excel to MS Access. There are a number of issues you should know about in advance of making this move. You should find My Blog here useful...​

Excel in Access - Problem & Solution!​

Excel in Access – Nifty Access

www.niftyaccess.com

 

[The_Doc_Man]

Since you are in the design phase of this project, now is an excellent time to give you a bit of reading material. You will do yourself a lot of dirt if you don't learn how to normalize data tables (which is implicit in your attempt to somehow merge multiple tables from Excel.)

Search this forum. SEARCH is on the menu bar at the top right of the screen, to the right of where your login name shows up. You can look for the word "normalize" and "normalization." However, you could also choose to do a web search. If so, you must search for "Database Normalization" because there are other types of normalization in math, chemistry, politics, diplomacy, ...

IF you choose a web search, at least the first few articles you should try should be taken from .EDU domains, because the educational sites tend to have better tutorials. The .COM sites are often good, but sometimes they have something to sell you of a proprietary nature. Once you get more comfortable with normalization, the .COM sites are usually OK and might even be helpful.

 

[Pat Hartman]

Internally the application should use autonumbers (arbitrary sequence numbers) as the primary key for each table. To satisfy your request for a custom "external" number, we can show you how to generate a separate sequence for each group. It will be easier if you separate the text prefix and the numeric suffix into two separate fields though since you can only do arithmetic and numeric fields. It is always easier to concatenate two fields than to take one field and separate it into two parts, so start with the fields separated.

If you can post a spreadsheet (only one if they are all the same format) and we can take a look at what you might need for additional tables.

 

[roluko]

Attached is four stripped down examples of the excel files I want to convert into a master parts database. These excel files have evolved over the last 10-12 years, columns have been added as needed over the years and as new people have been hired and put in charge of the various excel files. A cleanup of columns and information we really need have been postponed, as more urgent tasks have emerged. Also, several excel VBA scrips are in use, developed by multiple people over time, and many of these scripts use hardcoded column names and column numbers (for example, Offset(3, 2).Select and variations of the offset function. So, at the moment, just deleting and/or renaming columns may have unknown and hidden side effects, causing VBA scripts etc. to fail. I'd like to focus on converting the excel files into a proper database, rather than spend time fixing excel VBA scripts.

I have started to create a normalized structure, based on what I have learned by studying information found on .EDU sites and youtube videos. Currently I am a bit stuck on how to solve the challenge with the individual excel files, like equipment, instruments etc.

Trying to describe what I want, I have attached some stripped down examples.

One good thing is that I am starting fresh - I have the opportunity to create a brand new database from scratch, as long as I am able to keep the existing convention for naming and identifying items. I really want to avoid having to create new barcode labels for the existing 3000+ unique items in our current Master Parts excel file

Any advise or help that could help me move forward with this project would be appreciated.

 

[jdraw]

Any advise or help that could help me move forward with this project would be appreciated.

Do you have a corporate sponsor or patron who has tasked you to modify/modernize the company's inventory management process and has allocated resources to do so as an approved project? If not, then better to start by laying out some "observations of current processes and deficiencies" and "an approach to solution" for consideration to your manager/management.

If you are talking "moving 5 or so spreadsheets to a single Access database" and the company runs a whole series of independent or quasi-independent spreadsheets to manage inventory, then you may be addressing a symptom rather than the real issue. You may want to confirm the scope of the project/task within any planned IT/Data management regime.

A database that supports processes across the company can not have a bunch of independent developers working in trial and error mode. You need some discipline - that involves some authority and accountability - to get all players (designers/developers/users/managers..) on the same page with a common and well communicated approach.

There are a number of articles in various formats dealing with Database, Access etc in the Database Planning and Design link in my signature.

Good luck.

 

[Pat Hartman]

1. Equipment and Instruments probably belong in the same table with a type code. Parts belong in a separate table.
2. Do NOT use dummy foreign keys. If you do not have a FK, then the field should be null. However, most FK's should be required so make sure to make the field required if that is the business rule.
3. The IDTag should be just the sequence number. The type code I mentioned in #1 would reflect INST or EQU and can be used concatenated with the sequence number for printing purposes.
4. There needs to be a unique index on the SeqNum field and the Type field. You will need to use the indexes dialog to make a compound unique index.
5. Always use the PrimaryKey for relationships. Do not use the SeqNum/Type combination.
6. To automatically generate a sequence number for a new row, use dMax()
Me.SeqNum = Nz(DMax("SeqNum", "YourTable", "Type = '" & Me.Type & "'"), 0) +1. The Nz() handles the null return you would get when adding the first row for a new Type code.
7. Make a table to hold valid TypeCodes.
8. For each Instrument or Equipment, keep an Inspection period in days, weeks, or months. Then when you do an inspection, you create an inspection date record with results and calculate the next inspection date.