Using MS Access/subcategories


Relationship snapshot
Relationship snapshot  
QUESTION: I am a real Access novice and am using a pre-built template to build a basic inventory list. The actual list is minimal at the moment, but should escalate to 10's of thousands of units within this year. Now, we have a few major categories which may have less than 20 different products each at the moment. The one category that I am wanting to have a subcategory for is for one electronic board that is serialized. These boards will be moved to different locations throughout the country and will need to be report-able and track-able by location, serial number, and/or machine (that they are within at a particular location). As of right now, the inventory table contains the following fields:
-ID, Auto, PK
-Item, ShortText
-Tested, Y/N
-Category, ShortText
-Location, ShortText (Should be drop down, currently not)
-Supplier, Number
-Manufacturer, Number
-Usage, ShortText (this is where i want the "which machine within a location")
-Reorder Level, Number
-TargetStockLevel, Number
-Attachments, Attachment
-Discontinued, Y/N
-Comments, LongText

A link to a snapshot of the table relations is at:

I would be grateful for any assistance the you could provide...

ANSWER: First there are a number of issues with your database. One is not using the Default ID name for Autonumber PKs. I always name my PKS, tablenameID. Then name the corresponding FKs the same so the relationships are clear. Second, you should not use spaces in object names. This can come back to haunt you. I prefer using camel notation (ex: LastName). Also don't use special characters like dashes and slashes. I know you said you are using a pre-built template, but Microsoft is very bad when creating these templates.

Another problem is using the Short Text datatype for a number of your fields. Category, Location, Usage all appear to be prime candidates for lookup tables so all your are storing is a foreign key that would be better off as Number.

If there is only one subcategory I would be inclined to elevate it as a Major category.

Do you need to maintain a history of Locations? Do you track Machines the same way? If a Machine is assigned to a location in another table, then all you need is the UsageID, the LocationID is then redundant.

---------- FOLLOW-UP ----------

Inventory Report View
Inventory Report View  
QUESTION: Scott, thank you for your thorough and quick response. I so see your points in regards to the basic setups in paragraphs one and two. I will take your advice and make the necessary changes.

Paragraph 3:The categories and subs are like the following:

*Cabling (Category), part# (Item)

*Hardware (Category), part# (Item)

*VSMIB-HD-REV-G (now listed as category, but should actually be an item under the "Hardware" Category), serial# (same item for each, just a different assigned # that needs to be trackable by location)

Paragraph 4: Actually, the UsageID needs to be sub underneath the LocationID; as each VSMIB for example, will be shipped to a location, say Iowa (which i need to run a report just for all VSMIBS in Iowa... then once in the Iowa location, the VSMIB will be placed inside a Usage element, say a standalone computer housing (which I also need to be able to track down to the particular unit (Usage Element) in which the VSMIB has been placed for operations).

I have attahced a snap shot of the mess that I currently have regarding the reporting for the VSMIB's. Thank you again for you effort to assist me!


Re subcategories. You have 2 choices here. The first choice is to leave VSMIB as a category. The second is to create a Subcategories table and and assign at least ONE subcategory to each category. You could use the same for as the Category where no subcat exists.

The other issue requires a bit of a redesign. These Access templates are not designed to be turney applications. Instead they are guidelines to illustrate how to create functionality. So you need another table that lists all the machines at a location. Then All you need is to store the MachineID with the item and you can then track where it is.

One of the princiles of a relational database is that data exists in one place only. So you don't need to repeat the LocationID when you already have a table that shows where a machine is located.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.