You should use the Access newsgroups a lot. Look at them as one more
resource to help you get your work done. I recommend that you lurk
and learn in microsoft.public.access.gettingstarted and
microsoft.public.access.tablesdesign to begin. I recommend that you
visit
www.mvps.ort/access and review the "Rules for Databases" of
something named like that. That site is an incredibly valuable
resource to Access developers. Another neat thing to do is to Google
these access groups on your specific issue. You can construct a
Google query to search for your issue across all of the Access groups
in one shot.
Your ideas about the data are more at fault than the data in the first
part. Just forget about fields E,F, and G in that first table. Any
time you need to display or report those derived values, calculate
them. Calculation is the only reliable way to deal with that
situation and requires storing the calculation once as opposed to
storing calculated results for each record. Do not store calculated
results back into the database; at all, ever. Once you understand the
truth of what I have written and you have the appropriate calculations
in place delete those fields from the Inventory table.
Learning the rules about relational database management systems will
lead you to the conclusions above.
The Assemblies part seems to recall a distant echo of "Indented Bill
of Material" chiming.
Your idea of calculating the things you have from the bottom up is
correct. The calculations in use and where you are applying them is
*wrong*
It seems that what you have is the implementation in Access of an
Excel (or other) spreadsheet. Let's change the thinking to
"relational". Some of this will be counter-intuitive. I'll try to
explain how it works at the end... Also, I'll just show some of the
main fields. The ones shown will suffice for a simple system. More
complex considerations may require more fields.
From what you have given, I would characterize what your Inventory
application is tracking as Assemblies and maybe Items. Higher order
assemblies may contain lower order assemblies. In that context, the
lower level assemblies are "sub-assemblies". Lowest level assemblies
contain Items. If your Inventory application is about Finished Goods
then Items don't play. If you're after a complete bill of materials
then Items do play.
First, assume BOM
Table Item will have fields for:
ItemID(Autonumber), ItemName, ItemDescription,
ItemMaterialType, ItemCost, ItemNotes
This is about a single item in raw inventory.
Table Assembly will have fields for:
AssemblyID(Auto...), AssemblyName, AssemblyDescription,
AssemblyNotes
This is about a single assembly.
Table AssemblyItem will have fields for:
AssemblyItemID(Auto...), AssemblyID, ItemID,
AssemblyItemQuantity, AssemblyItemNotes
this is about a single item and the quantity
of it in this assembly
Table AssemblySubAssembly will have fields for:
AssySubAssyItemID{Auto...)
AssemblyID of containing assembly
AssemblyID of sub-assembly, SubAssyQuantity
AssemblySubAssemblyNotes
this is about a single assembly and the quantity
of it in this higher assembly
Note that the above tables don't fully address costs. That's because
I don't know how you arrive at the cost figures you use. I also don't
know if you use different cost figures for in-house transfers versus
sales If you have a separate labor or overhead or other cost that is
included in the assembly cost over and above the cost of the
components then it must be shown as a cost field at that level, call
it AssembleCost. The cost for the whole assembly will be the sum of
AssembleCost and all of the component item costs. Do not store that
sum in the database! Calculate it each time you need it.
Your tables and fields should look either exactly like that or a lot
like that. With the tables and fields as above you're ready to go
with the rest of your design.
You may have noticed that your original number of tables has grown.
That's exactly the way it should be. You may have noticed another
disturbing thing. Some of the tables include the Primary (ID) fields
of other tables. That's part of how the relationships between tables
are communicated. It's just that it kind of seems backwards! A
moment's digression: Access and other relational database management
systems are based on set theory from mathematics. That's where the
"relational" part originates and it's fundamental to the thinking.
There are "parent-child" relationships, in database parlance they are
"one-to-many" relationships. Rather than dealing with the
complexities of trying to contain the names of none to infinite
children within the parent record, each child record simply contains
the Primary Key of the parent record. That handles the case of one to
many. What about those cases of many-to-many; car manufacturers and
car colors for example? In that case you track the Primary Key of
both parents.in each child record. A table for that purpose is called
a "junction table". Using that table you could find all Mercedes of
any color or all red cars from any manufacturer. Depending on the
application, you could find that a significant fraction of records are
of that type. Spend a while with it and it will all make sense.
Once your data is as above you can design your Forms, Queries and
Reports. Post back with questions.
HTH
