Print shop order price

[santinimatias]

Can't figure out how to create a report that calculates the price of print orders in access.



I've created a paper size table, paper type table, and a plot table. Also I have an employee table and a projects table.
Paper size table contains:
- sizeID
- Papersize (8.5x11, 11x17, 24x36, 30x42, 36x48)

Paper type table contains:
- typeID
- PaperType (Bond, Presentation, Vellum, Regular)

The plot table contains:
- plot date
- number of sheets per sets of drawings
- number of sets
- color/bw

I am trying to create a report that based on the paper size, paper type, number of sheets, number of sets and color or b/w, it calculates the order price. Also I will sort it based on project number.

I thought of having a price list table, but don't know how to connect the order variables (paper size, paper type, color or b/w) to the price.
I also tough of having the report to run a query that combines every variable's ID into a unique number. The price table would have the unique number along with the price (this would be the price of one sheet, which I would later have to multiply with the total number of sheets and the number of sets).

First, I don't know if this will work, or if there's a better way. Second, I don't know how to make it work.

Any suggestions would be greatly appreciated!

Thanks!

[orange]

I don't know from experience what should or shouldn't work. You have done considerable work in defining what factors are involved in satisfying/comprising an order.
My guess is that you step back and attempt to set up an expression to identify costs.

Along the same line as an automotive repair shop:

Total cost of Order = cost of person time + cost of materials + shop overhead where

-- a person has a rate/hour for doing his/her job
-- materials have a rate per sq ft, rate per foot, unit cost for special processing....
-- shop overhead may be a percentage of the People+materials cost, some arbitrary cost for set up, reordering,....

I have seen (at some point in the past) where someone tried to equate different things to an abstract unit. As I recall, his abstract unit was "stones". Each item/process on his list was equivalent to so many "stones". So, whether it was creating prints, colored prints or prints of various sizes, or prints within X hours, he would calculate his expression to be worth y "stones". He then had a "conversion factor", which could change, to relate "stones" to money (dollars, pounds, yen ..)
The concept of "stones" provides a relative 'worth' of the various components. The "conversion factor" brings it all into real terms. In general terms, a "stone" could equate to a dollar at some point in time, and perhaps 1 stone = 2.5 dollars at a later time. (Modifying the conversion for inflation, new suppliers, other factors).

This may be way too abstract, but I think the point is all of the materials and all of the processes involved have a cost. And you have attribute those costs to something and relative to each other to make sense of your charges to customers for orders processed.

Interesting issue, please let us know what you decide. Good luck.

[santinimatias]

Thank you for the response.
Considering that the ink is a dependent variable (the amount of ink used in the print depends on the size of the paper and paper type), I think that the "stone" unit system might work best, but I would be twisting it to a "code". to create this code I could use the unique number for each field/table.
To do this, first I would have to create an "Ink" table for the "color or B/W" options, and delete it from the Plot table (where I currently have it located). Then, I would have to combine each variable's ID into one code (I.E., the paper size ID #1 for a 24x36, the paper type ID #4 for Bond paper, & Ink ID #2 for B/W, which would create the code #142). I would also create a Price List table where I have the "code" field and the "price" field. Since the variables for each field is limited I could just have a code for each possible combination.
Finally, a query would calculate the final price by multiplying the code price with the total number of sheets, and shown in the report.


Thank you for your help. I will let you know how it went.

[santinimatias]

Update:

The "code" system worked out very well... all calculations are done nicely in the query.
Thank you for your advice!

[orange]

You are welcome. Glad it worked for you. Good luck.