Windows 7 64bit 
Access 2010 64bit
Data sum novice question
It’s a BOM problem that is something that I can’t seem to figure out. If I sound confused its because I totally am.
BOM table (A) : parent (left), child(right), Qty. Another table (B) gives you unit of measurements, descriptions and codes.
It’s a little confusing because the data models are sort of different, typed in standards that really should be in a separate table : but they live together in the same table. I named the typed in rate “r1". But, the BOM (A) is a faithful csv file I got from the old program.
On the right side I’ve put some typed in “standards” (r1) with unit of measurements (a pound of this type of steel costs 55 cents) which uses parts that we buy. This is what they called the “item master” on the old system. (I took us off that (we had professionals then), and its my problem now to get us back in the game).
In the same BOM table you have Left side parents adjacent to Right side children : or other parts down lower in the food chain. These “manufactured” parts are signified with integer quantities.
More background. There are also more tables/fields I was able to get via reports/forms. How they were generated , I don’t know. In the reports they have every thing all summed up nice like for each part we make.
In my trying to figure out the process I discovered two other defined sums: “this level” and “lower levels.” They exist in the old (dos) program and I don’t know how its done. There are also “pointer” files which I don’t have any clue at all what they do.
In teaching myself SQL, I can see if all the manufactured parts are “summed up” it’s a fairly simple SQL statement / report.
In trying to get there :
I’ve looked at Access, SQL, VBA Recordsets (which also require an SQL statement), Cube, Group by with Rollup extension (SQL server) and read that you can do it all in SQL and just in SQL (Joe C.). My head is waterlogged.
What am I missing? I can see some arithmetic coming from the tables I have for “this level.” Getting the grand sum for the whole part is where I’m lost.
I was in over my head years ago, but things got beyond my control (programs were not kept up to date, the management took a mental flyer).
So, is there a method that I need to do to get this raw data summed up to the point where I can write a query and do a Report? Can all this raw data get me where I need to go with the tools I have looked at?
Reply With Quote
