Your problem isn't necessarily the list of materials but the list of properties of materials as I understand your discussion. Having a variable list of properties for display can indeed be a pain in the toches.
Having multiple tables is one possibility but you would have to do a complex UNION query to rejoin these disparate tables and the mapping of the disparate fields would be nightmarish, in my opinion. (Stated as OPINION, not with absolute certainty.)
If you are talking about five or six fields that will be blank for each item but different fields will be blank for different categories, that might not be SO bad to put into a single table. Remember, text fields that are empty take up only the space required for the "empty text string" pointer - which isn't so much as to eat your socks in wasted space. Now, if it were, like, 50 fields, that would be a bunch. Five or ten fields - that is nothing much.
In either case, if this is a display-only issue, there are ways to deal with this, but they all require some work.
In the case that everything is in a single table but some things will be blank, you can write some VBA code under your forms and reports to muck about with the text boxes that display the disparate properties of your items. You need a materials-class code for sorting and grouping purposes. AND you need to design this class-code so that everything that has the same exact class-code will have the same exact display format.
In reports, you can group by the class code and make a class header. In the Section_OnFormat event, you can write this code I am about to describe. You would do this for the Class Header and Detail sections. In the class header, you would use this code on the column labels. In the detail section, you would use it on the text boxes showing your attributes. On forms, you can also put the same type of code, again based on the class code, this time in the Form_OnCurrent event routine. On forms, you typically have the text box and label box in the same form section.
A text box has an associated label box. In the event routines I mentioned, you can look at the class header and decide what fields are relevant. Then you can hard-code a sequence that either enables the text box and label box or disables them and makes them invisible (.VISIBLE=NO).
For enabled text box / label box pairs, you can adjust the top and left coordinates to make them move flush to each other. You find a "reference" point on the chosen line where you will display things. That point is where you will start these "dynamically located" boxes. Make it a variable in the VBA code. Load it with the reference point you choose. (Your units are called TWIPS, which is a Microsoft display unit.) It is, essentially, a distance from the left border of the form or report.
Now when you find a box you will enable, adjust the box's LEFT property to the reference point. Then add the box's WIDTH property to the reference point variable. Remember to adjust both the text box and its label, and also remember to adjust the .VISIBLE property to NO for things that won't apply.
If you look into making this a subroutine (and you declare one of your formal parameters to be a Control object), you can put this subroutine in a general module and use it on any form. (Remember to make it a Public Sub if you do this.) Now, the logic of whether something is enabled or disabled might be so simple as a Select/Case statement with a different bunch of subroutine calls for the same controls for each case. You would put that in the form's event code.
If you are going to do this, you must assure that each dynamically-moved control is EXACTLY the same width as its associate label; each dynamically moved control must be as tall (.Height property) as all other dynamic controls; and each dynamic label is exactly as tall as other dynamic labels.
Finally, you must define the form or report line where this stuff goes based on the class-code that has the most data on the line at one time. That is the widest display you will ever show, so your form/report has to hold that line. It sounds awfully complex, but it isn't really.
Remember Julius Caesar's advice ... divide and conquer. Split the problem into the box-mover and a select/case for the formatting decision-maker. Attacking those parts separately makes this manageable. Not to mention that it is a really spiffy-looking form or report when you are done with it, because things move around on this form based on the data context.
If you really are not comfortable with VBA, this solution is not for you, but then I don't a solution that is, offhand. Variable amounts of data based on class-codes (whatever you choose to call them) can be a real bear in ANY application, even for the long-term professionals.
