The Free Inventory Management Template for Access is a professionally designed inventory management database of your transactions, stocks, employees, and suppliers. Mar 31, 2011 Hi I am considering using the Access Inventory Template (from Microsoft Online- Business. My Issue is that I need to have multiple locations for the parts. This article shows you how to use the Goods web database template in Access 2010 to track inventory, orders, invoices, purchasing, and other business-related data. I simply need a database that would allow me to organize part numbers, descriptions, print reports, and allow me to format my information so that I can import it into a purchase MRP System. For now I have one master list that has lookup list under it for categories, Material, and other options. The master list then runs two queries that are my assembly list and part list. Then the forms/reports run off of the queries. This Microsoft Access database is based on the Inventory Control template. It has been modified to enhance functionality, usability and make the database more applicable to Australian business practices. Inventory Control is a basic inventory management software tool suitable for many small enterprises. Use the Inventory. The second table structure that you were talking about would include what fields so that I could get a simple BOM report. It's impossible to tell you exactly what you should do to build a proper import file for your MRP system, since we don't know (a) what your current Access table structure is or (b) what your MRP system expects. MRP systems are even more complex than BOM systems, as they typically take into account different vendors, different vendor_lead_times, different partnumbers for the same part etc etc etc. However, if your 'master list' is built with several subtables/queries, you should probably start off there and examine the relationship between those tables. In most cases, you'll have a 'header' table which would describe the item, and then you'd have detail records (i.e. 'lineitem' data) which would show the actual items which make up that assembly. In most cases, you might also have a lineitem which is itself an assembly, and you'll have to account for that as well. As to reports - again, if your data is formatted in a Header/Detail format, then you'd build a Report/Subreport type of object, with the Report showing the main data and the Subreport showing the 'lineitems'. The difficulty would be when your lineitem is itself an assembly, I think. You can have several levels of subreports, so you might be able to do something like Report-SubReport-Subreport (and more levels, if needed). Again, this is a very complex subject, and not one that lends itself well to online forums. Hi Terry, So to me, it seems like you're touching on two different topics with this question. The more obvious of the two is 'how does one modify a template to accommodate a table structure change?' Which would also lead to what is probably the more important question, 'how should I modify my tables to account for the described scenario?' We can answer the template question only generally, but I think I can give you a good start on the table structure change. I'll start there since you'll need to account for your table changes in order to make the desired changes to the rest of the template. The scenario you laid out seems to describe a many-to-many relationship. It sounds like you can have many types of parts, and that any of these parts could show up at any number of different locations. Access doesn't have a mechanism to allow for a many-to-many relationship between two tables, but it's possible to put one together using a junction table. This article has a pretty good description of many-to-many relationships: Also, I haven't watched this video, but it did come up on a search on help for many-to many relationships on the Office.Microsoft.com site, so you may want to give it a look: The basic idea is that you would have one table with a list of all locations, another with a list of parts, and then a central table that contains the foreign key fields for both of those tables along with any pertinent information about the parts at that location. Your data might look like this once you entered it in the junction table: Part No| Location| Part Count 123| Seattle| 28 124| Seattle| 45 125| Seattle| 205 123| Orlando| 60 124| Orlando| 12 As you can see, this allows you to track the quantity of part 123 in Seattle, and the quantity of the same part number in your Orlando office. Does that come close to what you're trying to do? As for modifying the template, I typically would advise against it, simply because there are so many potential pitfalls. In order for the forms and reports in those templates to work correctly, you need to remove all references to fields that will be removed and make sure that you're adding controls for any fields that you do add and need reflected on the final objects.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
May 2018
Categories |