Macola tables in Access 2000

[Anonymous_354]

I am trying to make a tweaked database that comes out of Macola each night. It is an old version of Macola, so I simply imported the tables into Access. There is a list of parts and their parent assemblies/parts. I need to find the head of several series (automatically) and have the top level be called "1" (how creative), the next level "2", and so on down the list (all automatically, of course as it will need to be run every night). The original tables will get wiped out, along with the columns. Does this make sense? Is there anyone out there who has dealt with Macola? Any thoughts?

 

[Anonymous_354]

In simpler terms, I've found my way up the hierarchy by hand starting from the lowest part and ending up with the final assembly and eventually the project. I need a program to assign numbers while working up the chain. I was assuming a query would do that, but I may have to take more drastic measures.

 

[lark_tilly]

Macola - been there both pre and post Y2K.
We don't use it anymore but once upon a time I knew it very well.
Sounds like you're trying to build something like a bill of material.
You say you need to find the head of some series and you mention projects.
I faintly remember something about projects but it's fuzzy.
Are there a limited nbr of bills you're tring to do this to or is it the full table?

 

[Anonymous_354]

About 11,000 rows that I need to look through and label. In essence, the entire project orders since 1988 (I have no idea why they're keeping it around). Yes, it is a bill of materials (indented at that ) It's spanned over 2 tables. The head in one, everything else in the other. I realize this is out of context, but is there a "better," newer program like Macola (pereferably free)?

 

[lark_tilly]

Yeah, Macola is a little pricey
Course it's cheaper than Oracle!
Unfortunatley, I'm on an even older system now so I can't help you there.
Okay, so one table has the project nbr and the other table all the components. Am I right then that in the second table the record that contains the level 2 record has the project nbr as the parent and the component as the child. I've forgotten what the actual field names are.
Also, can there be more that one child at the same level or is it strictly
one parent/child per level.(Probably can't be that lucky!)

 

[Anonymous_354]

the way I followed it down is I took a component on a paper somebody printed out for me and looked for it in the ITEM_NO column. Then I tool the PARENT_NO and looked for it in the ITEM_NO. If you do this enough times, you'll end up at the second to the top (of the hierarchy, not the list). Then I took that parent number and put it in the other table and it magically worked!

 

[lark_tilly]

Yeah, that sounds right.
Okay, first question.
What is the purpose of identifying the levels? Are you trying to set up an inquiry ability to view the BOM for a project or print a report or what?
Question two.
Can you send me a copy of the two tables?
You can delete all the "real data" and just fill in a record or two from each table with dummy data. If I write something to blow through the tables and mark the levels I might as well get the field names right up front.
Question three(from the previous email)
Do you know if there are more than one ITEM_NO per PARENT_NO at the same level?

 

[Anonymous_354]

Answer one:
I'm trying to do things in Access that Macola can't.

Answer two:
Sure I can. However the tables are too big to compress and post here. One way I've solved this in the past is e-mail. However, I may just be able to make a database based off of the current one and post it here. Maybe that will suffice. Give me 30 minutes or so.

Answer three:
Yes, there are duplicates. But from all I can see, there are only two of each... if that was what you were asking.

 

[Anonymous_354]

Here it is. It's a whopping 128KB when extracted. The tables should not be played with in design view, only through queries or a series of queries. If you want the full set of tables, I'll need to e-mail them to you.

 

[lark_tilly]

Ah, yes. I remember those tables.
Now that I have them, what exactly do you want me to do?
You said something about assigning a Level but the problem with that
is that the same parent/child could be used in multiple BOMs and appear at
a different level in each. What are you trying to accomplish? Do you want to be able to print a BOM for a particular project or what?

 

[Anonymous_354]

Please forgive my ignorance, but what's a BOM?

I'm trying to build a front end app in VB.NET (reason being that it is more flexible than Access), and am finding Macola hard to work with... I'm trying to add functionality that Macola doesn't have, as well as making these tables accessible to those without Access.

I need a hierarchial scructure based off numbers. For example:
Sorry I had to use the vB CODE markers, but they make this easier to work with...

Table Table Table Table
IM*   BM*   BM*   BM*
1 Parent
      2 Child
            3 Grandchild
                  4 Bottomfeeder
      2 Child
            3 Grandchild
            3 Grandchild
      2 Child
1 Parent
      2 Child

Table IM* = IM*****
Table BM* = BM*****

Sorry if that was hard to understand...

If it's possible, you might be able to make a module or something that will look in the IM***** table and begin to follow the top row all the way down. Then have it loop but exclude the rows with numbers in a column.

 

[Anonymous_354]

Does anyone have any ideas? Should I take a different approach?