Inventory of various item types

[FinsCukinac]

EDIT: skip down to post#4. I've learned a thing or two since writing the initial post.

Here's what I'm trying to do:

I've got two different types of items: screws and foam. Each has a different kinds of characteristics, so each has its own table (tbl_screws and tbl_foam respectively).

I want to be able to track orders, receipts, and inventory of both kinds of items together (i.e. have a master list of all screws and all foam).

Here's how I've tackled it:
The primary key of tbl_screws and tbl_foam is a replication ID.
A UNION query (quni_allMaterial) joins the primary key and name from each table.
My inventory is tracked in tbl_inventory, which pulls from quni_allMaterial.

Problems:
When I view tbl_inventory and try to sort on the material field (the one that pulls from my UNION query), I get this error: "Syntax error in query. Incomplete query clause".

My question for you:
Is there a way around that syntax error?
Is there a better way to handle inventory of different item types? I'm open to all suggestions. I have a lot of leeway in restructuring my db.

Thanks!!
I've attached the example database.

 

[Ranman256]

Your 2 queries must produce the exact same field count,and field types.
If Q1 first field is a date, then Q2's first field must be a date. Etc thru every field.
THEN you can union the 2 queries.....

Select * from Q1
Union
Select * from Q2

 

[FinsCukinac]

Thanks, I've done that - it's the quni_allMaterial query.

I found a great thread on this forum related to my question:
"Question about equipment inventory database design"

I didn't see it the first time through.

 

[FinsCukinac]

Ok, so I read through the entire thread on this forum named:
"Question about equipment inventory database design"

The major takeaway was this:
Keep one master items list. This list contains both screws and foam (and in the full database, many more types of items).

Here's one thing I feel was left unresolved:
In the attached example database (SAMPLE-StoreDiffData) from boblarson, all the optional fields for each item must be of the Short Text data type. What if I want to store a date? Or an attachment?

Here's an additional issue that pertains to my particular situation:
My screws are grouped into families. Each family has its own head type, thread pitch, thread profile etc. Each family member (the individual screw) is unique in length only.

Therefore, in order to avoid repeat data, I break out the family-specific dimensions into its own table: tbl_drawings. What's a good way to tie a screw on the master items list (tbl_items) to the drawing/family (tbl_drawings)? You can see how I did it in the attached "20160929 inventory". I used a joining table.

Thanks in advance for your feedback!