Calculations on a report/query

[nick243]

So I have a report where I have purchase orders a from vendors by a sequence. My first thing to do is add up all vendor po's lbs in that sequence number then run a vba code that gives me the cost based on lbs. My problem is when I want to divide that cost by the lbs evenly on each po in that sequence then add that to single po cost at the end of the row. Here is an example of my report in design mode. My example of the design view picture is not correct exactly. I have created a query that does all the calculations in the container id portion I just don't know how to divide that cost up between the po's and add that to the costs at the end of the row. Any help would be appreciated.



Thanks,
Nick

[aytee111]

Use queries to do all the calculations prior to creating the report. That way you can check your data before trying to display it. For instance, create one query to calculate the total cost, then bring in that query to evenly distribute the total across all records.

[nick243]

That's the way I am thinking, I guess I am confused about how to evenly distribute the total that I have to the singular level from the total. Is there any examples out there? I'm not sure how to start a query that can distribute a total number back to the singular PO evenly based on weight.

Thank You,
Nick

[aytee111]

You need to figure out the mathematical formula prior to putting it into a query. I use Excel for this purpose, get the formula correct, then translate it into query-speak. It would be something like taking the total and using each weight as a ratio/percent of the total.

[nick243]

The calculation I came up with is ("Weight of the po"/Total Weight for the Sequence)*"Total Cost" This worked out for me on the report side. I just now need to put this in query speak.

[aytee111]

The weight of the po will come from the table.
The Total Weight will come from a separate query linked in to this one.
Not sure where the Total Cost comes from but it seems it would also be a separate query, or if the same grouping can be in the total weight query.