The product is not defined in ERP program. The table struktur stores an article st_art_id (a part of some product) and it's parent article st_hart_id, along with structure row and position numbers, and needed quantity st_kvant to produce a single unit of parent article. The procedure processes table struktur product-wise, and composes a BOM list saved to SQL Server database table. I had a table struktur imported from ERP system into SQL Server database, and I needed to get BOM list for predefined products into Excel report. I have a SQL Server database, where a recursive query doing what feels like same thing you want to do.Īs was said here before, recursive queries are possible in SQL Server, not not in Access. I admit I haven't looked, but find the claim that you can't find BOM examples surprising because it's such a widely used type of system. Perhaps that will indicate a possible approach. (IF YOU READ THIS AND RECOGNIZE THE SYSTEM AND FIGURE OUT WHO I MIGHT BE, KEEP IT TO YOURSELF!)Īt any rate, my point is that I'm fairly certain this was all done via table relationships but this also required a table for equipment location codes. Thus a component on an assembly on an assembly on a system belonging to an area might look like What I'm thinking that made this possible was an equipment location approach that was (perhaps) 7 levels deep, each level a max 4 character code that represented a location. This is another opportunity for me to regret not keeping the tables manual when I retired as it was a 2" thick binder duplex printed list of all the tables and fields info. AFAIK, none of this was done via recursive methods, rather it was table design. All the tables were in some type of sql server db and Access was the query tool. I used to work with a CMMS system that contained a BOM component. I've tried searching, but BOM is too short a search term, and "Bill Material" (the of also being too short to use for searching) does not seem to find any examples. So, I'm left with a single table that has a part and a reference to a higher level part, that relationship being a piece part into an assy, or a sub-assy into a higher level assy or product. Each product could have a common component in multiple sub-assy's as well as those sub-assy's at different levels of the BOM so not easy to just set up tables for simple parts (which actually have subs which are the raw material), first level sub-assy's, second level sub-assy's, etc.
I am looking to have a table that self-references - a part can be a component of another part (assy), which in turn can be a component of a higher level assy, which in turn can be a component in a higher level assy.
This must be a normal use of a database (individual part used in assy's - seems like the classic one to many relationship situation), but I'm not finding any samples/examples.
0 kommentar(er)
