Calculating On Hand Inventory

[ssanfu]

@ thearn
Maybe you could post an image of the relationship window (if you have set up relationships)

Better would be to
make a copy of the dB
delete all of the data from all of the tables
do a compact & repair
Compress (Zip) the dB
Post it....

[orange]

1-Post a copy of the database as Steve suggested.
2-Describe a day in the life of the business -at a high/30000 ft level so we understand the main activities.

[thearn]

@ thearn
Maybe you could post an image of the relationship window (if you have set up relationships)

Better would be to
make a copy of the dB
delete all of the data from all of the tables
do a compact & repair
Compress (Zip) the dB
Post it....

Table relationship window

[thearn]

I have been unable to get the database small enough to post I have delete all records, ran the compact/repair it is still about 4 MB it seems the system will accept files 2 MB or less I posted a screen view of the relation ship table in the post to ssanfu.

This is a heavy equipment service and repair company, customer will bring in a machine to be serviced, the shop will determine the issue with the machine, they will then source the parts needed for repair (Purchase Order), make the repair and then bill the customer for the parts at their cost along with tech's labor (Service Order). This for the most part happens all in the same day, part in part out. This application is essentially a way for the company to bill the customer, they are not really interested in tracking the inventory of parts, they just want to know what part was used on what machine. there are only about 20 items at the max that they actually track, like oil which they may buy in a Drum but the machine may only use 5 Gallons out of that drum. Services maybe performed in the shop or on site at the machines location.

This database was kind of dumped on me and the design was poorly done, but it is what I got to work with, just have to do the best I can.

Thanks so much for your assistance

[orange]

Often more helpful if you extend each of the tables to expose all fields in the relationship window before posting.

Also, can you tell us more about this?

This database was kind of dumped on me and the design was poorly done, ....

[thearn]

Often more helpful if you extend each of the tables to expose all fields in the relationship window before posting.

Also, can you tell us more about this?

Someone else created the database, but never finished it and is no longer around, I was given the directive to make it work. I have reposted the relationship window for your review.

[orange]

I created 2 draft models based on my general understanding of your post.

First is a model to show how general shop supplies (bulk oil, rags, tape, wire...) could be accounted for across multiple, diverse jobs/projects. This was first used when a poster had a requirement to deal with bulk rolls of sail cloth. Sails could use multiple colours and pieces of cloth from several rolls. So costs and quantity used was distributed over several jobs.



Original sailcloth model


The second is a conceptual draft model of the main pieces and relationships based on my understanding of your heavy equipment service/repair business. There may be details that are unaccounted for because of the lack of detail and reference data.


I hope this is useful.

You may also get some insight from Barry Williams' site's data models
Vehicle Maintenance
Vehicle Maintenance w/Attributes