Opinion on how I should handle my data?

[xcrx]

I wrote a database a few months ago that tracks inventory for a manufacturing plant. Some of the features include ordering parts from our in house Fab department as well as tracking these orders and tracking the equipment orders we get from customers.

At the moment I have all these features feeding tables such as a table for parts that have been ordered individually, another table for parts that have been used and so on. These tables all go into queries with the proper connections and formulas to come up with my totals for each part. Some the queries are several layers of queries and get a little complicated. So I guess what I want to know is if I would be better off doing away with some of these queries and maybe turning them into append queries and storing all the finalized data in one table that would be updated when a part was ordered or use?

I know doing it that way would run quicker but are there any other advantages or should I just leave it that way it is?

 

[Guus2005]

If you are asking for us to evaluate your database design you're not giving enough information.

A table for parts that are ordered individually sounds very specific and not very flexible. Another tabel for parts that have been used? I am not sure what you mean.

The solution which you choose is commonly used in datawarehouses. To save running time for lengthy queries. These queries usually run once a day and data is stored in a single table to create reports.

HTH

 

[Simon_MT]

I'm not a slave to normalisation, however I use non-normalised data very sparingly. There is nothing wrong with the way you have designed your application, in fact, the 'building block' approach is entirely sensible. If you have an issue with speed or the performance of your application, if you be better first to see what is slowing it down. Perhaps a fex more indexes or perhaps testing [back-up version] your queries and dropping a table one at a time to see if there is a performance issue somewhere.

Simon

 

[xcrx]

I uploaded a sample of the database so you can get a better idea of what I am working with. I had to remove some of the vba and all the reports to get it down to the right file size but I am mostly curious about my table and query structure.

It seems to me that there should be an easier way for me to handle all of it. It is quite complicated at the moment. Any help/ suggestions would be greatly appreciated.

 

[jdraw]

Not everyone has ability to open accdb. Can you provide a sample in mdb format?

Also, your vba may be useful, we could probably do with just some sample data.

 

[xcrx]

Here is the same database again as a .mdb file. The VBA that I removed just has to do with loading images and checking quantities. I thought I should leave the real data in there so I can demonstrate the level of complexity it gets to. However if you would like to see a copy of the full database with all the reports and VBA let me know and i will try splitting it into multiple zip files and uploading that.

 

[Simon_MT]

What you really have is Inventory at different stages:

On Order
In Progress
Sub assembly
Sub Assembly Build
Delivery
Delivery Other
LaserDelivered
Sig Parts?
etc

What you really want to find out is the Acquistion and Attrition or parts and it may pay to have a transactional file for all parts abd allocating On Order, Work In Progress, Delivered. Then perhaps combining where these parts are used and designating these into categories Laser SubAssembly etc.

Simon

 

[jdraw]

I agree with Simon MT. You have a number of tables and forms that seem to deal with parts of one or more processes. I think you should look for data models involving Inventory Control etc and see if there isn't something to help remove some of the current complexity. Transactions, including moving from one phase of fabrication to another, as well as adding to current stock and removing from stock would seem to be
a method to remove some of the "extra" info.

Do you have a set of business rules that your system is attempting to satisfy?

You can see more on Business rules and data models here.
http://www.databaseanswers.org/
I looked at your relationships window and do NOT see any relationships.

 

[xcrx]

Thank you I appreciate your input. I don't have a set of business rules to satisfy unless I am confused about what you mean. I do have relationships set up for all my tables. It might have dropped them when I converted to mdb.

I will take a look at the setting up some transactions and seeing how that works for me.

 

[Simon_MT]

To put it very simply Stock Transactions events are flagged:

P [+] Purchase
W [-] Work in Progress
D [-] Delivered
S [-] Sub Ass

Simon