Calculation

[Joe]

Hi, all,



I am a newbie of Ms Access.

My boss needs me to develop a small-scaled Ms Access 2002 application that
to store and do some calculation of the cost (printing).



That means I provide a Form for user to input records (printing material,
vendor, customer, jobs¡K).



Because there are different combinations of calculation method, such as
print 1-500 pages for $A, print 501-1000 for $A * 1.8 ¡K¡K.Also different
price for color and black and etc.



So I don¡¦t want to hardcode the equation in the query, I had use a table to
store the equation. Following are the table structure



Table: BASE_PRICE

Category | Price

===========

A | 10

B | 20



Table: EQUATION

From | To | multiple

===============

1 | 500 | 1

501 | 1000 | 1.8

1001 | 1500 | 2.7



I want to let user to input the no. of page and category in Form and then
the application search the equation & base_price table that it within which
group and do calculation.



For example, if user select category [A] and input 550 for pages, so it
means $10 * 1.8 = $18.



So I want to know that can Ms Access do that?



Thx for your attention !



Joe

 

[John Vinson]

Hi, all,



I am a newbie of Ms Access.

Joe, it appears you're also new to newsgroups. Please, if you wish to
post a message to more than one newsgroup, crosspost rather than
multiposting: that is, put all (no more than three, that's plenty!) of
the newsgroup names in the Newsgroups line rather than posting
independently to multiple groups. This will let us who volunteer to
answer see what questions have already been handled.

My boss needs me to develop a small-scaled Ms Access 2002 application that
to store and do some calculation of the cost (printing).

You would probably NOT want to store the cost. You might, but
ordinarily it's easier to just recalculate it as needed. See below.

That means I provide a Form for user to input records (printing material,
vendor, customer, jobs¡K).

You don't start a printing job with the binding, do you? <g> Start
your database with the Tables. The Forms and the Reports are the final
product; the foundation is your table. You'll need several tables
here:

Customers
Jobs
Vendors
Materials
(and, looking below) Costing

Define these tables and their relationships first, using the
principles of database normalization. THEN build forms to fill them,
and queries to combine the data.

Because there are different combinations of calculation method, such as
print 1-500 pages for $A, print 501-1000 for $A * 1.8 ¡K¡K.Also different
price for color and black and etc.



So I don¡¦t want to hardcode the equation in the query, I had use a table to
store the equation. Following are the table structure



Table: BASE_PRICE

Category | Price

===========

A | 10
B | 20



Table: EQUATION

From | To | multiple

===============

1 | 500 | 1
501 | 1000 | 1.8
1001 | 1500 | 2.7



I want to let user to input the no. of page and category in Form and then
the application search the equation & base_price table that it within which
group and do calculation.

A "Non Equi Join" query will work here. It's a very arcane and
specialized query type, and the Dummies books (or even some advanced
books) won't list it! But I find it useful for such things.

Use your Form to put data into a Job table; this table will have
fields for the Category and the NumberOfPages. You can then create a
Query joining [Jobs] to [BASE_PRICE} by Category (that's the easy
one). Then - for starters - join [Jobs] to [EQUATION], linking
NumberOfPages to [From]. In a vacant Field cell in the query grid type

TotPrice: [EQUATION].[MULTIPLE] * [CATEGORY].[PRICE]
(or whatever the calculation actually is).

Now select View... SQL. You'll see the query in SQL view - a bunch of
text. In this you'll have a JOIN clause resembling

FROM [Jobs] INNER JOIN [Equation] ON [Jobs].[NumberOfPages] =
[Equation].[From]

Edit this to read

FROM [Jobs] INNER JOIN [Equation] ON [Jobs].[NumberOfPages] >=
[Equation].[From] AND [Jobs].[NumberOfPages] <= [Equation].[To]

You can now base a Report on this query (perhaps with some additional
criteria to report on specific jobs).

 

[gandalf]

SELECT BASE_PRICE.Category, BASE_PRICE.Price,
EQUATION.multiple, BASE_PRICE.Price*EQUATION.multiple AS
Total, EQUATION.MinAmount, EQUATION.MaxAmount
FROM BASE_PRICE, EQUATION
WHERE (((BASE_PRICE.Category)=[Which Category]) AND
(([PagesToPrint]) Between [EQUATION].[MinAmount] And
[EQUATION].[MaxAmount]));

From & To are changed to MinAmount & MaxAmount due
possible naming issues
[Which Category] & [PagesToPrint] are parameters