Best method of storing this data?

[autocol]

Hi everyone,

I've recently begun building a database to keep track of stock at work. Nothing particularly special or difficult. I have a little bit of prior experience with Access and VB, but not heaps.

Basically, I've created a tables for parts, companies etc, and am relating all the data together.

At the moment I've made a form that allows you to enter a new part, with Part Number, Description, Category, Supplier, etc.

The complication comes when I want to create an ASSEMBLY of parts. I'd like to create an assembly (which has a lot of similarities to a part, in that it has a part number, description etc), and that assembly needs to store a list of parts that it includes. A simple assembly might include a few items, ie, a bowl of meatballs includes the bowl, a fork, the spaghetti, and 5 meatballs. A more complex assembly (a complete dinner for 5) might include 5 bowls of meatballs (a sub assembly), pepper and salt, 5 glasses and two bottles of coke.

I'm not sure how to best store this assembly data...

I can't really have a table with a finite number of "part" spots because the assemblies get quite large. I'd rather not use an ugly VB macro that stores the PartID's with quantities either, as that could get thoroughly out of hand pretty quickly.

I'm sure there must be a simple method for doing this, but without having a clue as to what it might be called I can't really look for it in help!

Any clues you guys could give me would be great.

Thanks

Col.

 

[autocol]

Okay, I've found subdatasheets.

That seems to be what I want to do.

The question then becomes, can a subdatasheet include data from the parent data sheet? My spaghetti example above is a bit silly so let me choose a better one:

Take a BMX bike for example. Say I have a table of parts that has:

PartID Description
1 Frame
2 Wheel
3 Forks
4 Tyre
5 Handlebars

Within that same table I want something like:

PartID Description
6 Complete Wheel
+ Wheel × 1
+ Tyre × 1

And then nest that back in again like so:

PartID Description
7 Complete Bike
+ Frame × 1
+ Forks × 1
+ Handlebars × 1
+Complete Wheel × 2
+ Wheel × 1
+ Tyre × 1

I've made a query that works with a many-to-one relationship (a supplier of multiple parts for example), but I'm not sure how to work that back into what I'm trying to achieve with the nesting of parts in multiple sub-levels.... ?

Any ideas?

Thanks
Col.