Weight Estimating for Ships - Suggestions on how to create a Weight Distribution

[BoatsinAccess]

Hello,




I design ships and as a part of that create weight estimates during the design phase. Currently, the weight estimates are completed in Excel but i am wondering if Access can be used instead.


The input that we enter into the estimate includes:
the name of the item
the type
the weight
the longitudinal center of gravity (fore and aft)
the transverse center of gravity (side to side)
the vertical center (up and down)
the aft extent of the weight
the forward extent of the weight


This information can all be added stored and reported on in Access easily enough.


My question relates to something called a longitudinal weight distribution which is essentially a calculation of the weight of the vessel at a given location along its length. In excel this is done in a large table that has a column for every frame of the ship and a row for each weight item. A calculation is done to determine the weight per frame (Weight / (Forward Extent - Aft Extent)) and then using if statements, the weight per frame, for a given item, is applied in a given column (frame), if that frame is between the forward and aft extent of the weight. All the weights in each column are then summed to give a weight/frame for all the weight items in the estimate. The resultant is a table that has only the total weight per frame for each frame.


My question is how could this sort of thing be best accomplished in Access. Essentially, i want a seperate table in access to be the longitudinal distribution table and it should only have a column titled "Frame" and a column titled "FrameWeight". I am just not sure if it is possible / how best to do the calculation to get from data table to this distribution table? Any help would be really appreciated.

[Gicu]

Could you upload a sample Excel file so we could get a better idea? From what you're saying you don't need a "distribution" table as it is calculated from the "data" table, that should be done in a query (or series of queries) and presented in a report.

Cheers,
Vlad

[BoatsinAccess]

Thanks for your response, Vlad. Attached is a simple mock up in excel. The grey is the inputs. The blue w/conditional formatting is where the weights are assigned to frames (formulas should be easy to follow) and then the green bit sums the table above and is what I want to figure out how to calculate in Access.

Sample Long. Distribution.zip

[Gicu]

Here it is, have a look and see if that was what you wanted. Once the crosstab is open click on the Totals in the Home ribbon and you should get your data summary similar to your Excel.

Cheers,
Vlad

[BoatsinAccess]

Thanks Vlad! That is great. Certainly shows me a good way to accomplish the task.