This thing's got me baffled for almost 2 days now. I have developed a database which deals with engineering equipments. What happens is that for a particular project, the company needs certain equipments of particular ratings. Each of these equipments is connected with something called Switchboard. The rating of the switchboard is the collective rating (i.e. sum of ratings) of all the equipments connected to that swtichboard. Initially the rating value for swtichboard is entered as 0 (zero) and the actual value for the switchboard is calculated when the report is generated. So far, piece of cake. What can happen here is that a particular switchboard might be connected to another switchboard. So lets say SB1 (Switchboard 1) is connected to SB2. Then the rating for SB2 will be ratings of all equipments connected to SB2 plus the collective rating of SB1. This works ok if SB1 is connected to SB2. However, now if SB2 is connected to SB3 (let's say), the problem starts to arise. Considering that only SB2 is connected to SB3 and nothing else, the rating for SB3 truns up to be 0. This is because the query sums up the ratings for connected equipments and since SB2 is specified as having "0" rating, as i mentioned before, SB3 rating turns out to be 0 in query calculation. How do I tell Access to add the collective rating of SB2 and not its individual rating?
The problem here is that the levels of connection are not fixed. SB1 to SB2, SB2 to SB3, SB3 to SB4 and so on. At some places, only 1 level, at others more. If the levels were fixed, the query writing would have been easy. Here it is not so. I was hoping if somebody could help me out with any idea or suggestions. I dont mind doing this thing at report level or query level. I have tried to state most of the facts, however, if any extra detail is needed or something is confusing, i'll respond to it ASAP.
Thanks to all for your time and efforts in advance.
What you have described sounds like a recursive level BOM structure. The level ratings of all the various pieces of equipment is based on the relationships of the parts to each other.
Enumerating the rating in a recursive BOM is not possible with simply a query. It now becomes necessary to rely on the wonderful world of Visual Basic. If you could attach a sample of the database structure in question, it would be more feasible to put you on the right track.
If the levels were fixed, the query writing would have been easy. Here it is not so.
Ok, but can you say the number of levels is less than say 20. If so, then run a query that keeps updating each level. I've attached a Db to explain what I mean. The Macro CalcRatings sucessively runs a couple of queries which together calculate the next level of ratings that haven't been calculated (it recalculates all the lower ones too but that doesn't matter 'cos it still gets the same answer). The point is is doesn't matter if you run the two queries 20 times if you only have 3 levels because the sums will still be the same. Obviously this is wasted computations but maybe that doesn't matter.
I put Switchboards (SB's) and Equipment (E) in the same table but they could quite easily be in separate tables. The first iteration would be slightly different though.
Here is the revised file. One field has been added to your existing table: LLC. This acronym stands for Low Level Code, a critically enumerated value in recursive BOM calculations. I was able to apply it to your database quite easily. No other modifications were made to the table structure.
I modified your CalcRatings Macro to RunCode for a function by the same name under an added module, modCalcRatings, viewable under the Modules tab from the main database window.
First, the code sets all the LLC's to 0. Then, it sets the Ratings to 0 for the SB-parts.
Next, it runs a recursive update query to set the proper LLC values for each relationship. Since SB3 in this example is at the top, the LLC value is 0. Then, since SB2 reports directly to SB3, SB2's LLC value is 1; same is true for E6. E4, E5 and SB1 report to SB2, so their LLC is set to 2. E1, E2 and E3 report to SB1, so their LLC is set to 3.
Finally, it runs a recursive update query to set the proper Rating Values for all the SB-parts, in LLC descending order:
E1(5)+E2(3)+E3(2) = 10 -> SB1
E4(1)+E5(1)+SB1(10) = 12 -> SB2
E6(8)+SB2(12) = 20 -> SB3
Run it and see for yourself. See if this is along the lines of what you are looking for.
That's neat. My VB is rather basic so seeing your code has given me insight
I'm not entirely sure why you need LLC for this Why not just use the following (thanks for your code )...
Public Function CalcRatingsNoLLC()
Dim Acq As QueryDef
Set Acq = CurrentDb.CreateQueryDef("")
CurrentDb.Execute "UPDATE Equipment SET Rating=0 WHERE EquipCode Like 'SB*';"
CurrentDb.Execute "UPDATE Equipment AS E1 SET E1.Rating = DSum(""[Rating]"",""Equipment"",""[ConnectedTo]='"" & [E1].[EquipCode] & ""'"") " _
& "WHERE E1.EquipCode Like 'SB*';"
Loop Until Acq.RecordsAffected = 0
My guess is LLC has more fundamental uses than just rolling up figures in the world of BOM calcs. Would that be right ?
That is correct. The LLC generally serves as a means of insuring that parent-child relationship information updates by hierarchy level occur in the correct sequence, and prevents unnecessary redundancies. For larger scale BOM structures, it's an excellent control over part cost rollups, accounting profile updates, exclusive/shared relationship updates, and more.
Hi howey and Matthew,
Tks a ton guys for all your time and efforts. I really appreicate it. I have just downloaded both the DBs and am going throuhg it right now. So far, it is really a great help. Tks again guys. I'll get back in case if I need any further advice or suggestions. Cheers and have a great time.