Basic math in Access

[aaron.kempf]

are you talking about 'in a query'?

that doesn't seem to be a computed column.
That seems to be a 'calculated column'.

You can either calculate a column.. or store a calculation. but
neither of those are as powerful as computed columns in SQL Server.

Sorry

-Aaron

 

[aaron.kempf]

I'd rather know the answer ahead of time-- than to get surprised when
my manager hires a second secretary and all of a sudden -- we're past
the 30 user limit.

You can react or you can be proactive.

You can fool me once-- shame on you.
Fool me twice - shame on me.

You guys 'get tricked' by a piece of crap database once a week.. and
it's like you guys still don't learn.
If the database craps out-- if it doesn't meet your needs-- if it
isn't competitive and scalable-- then don't use Access MDB.

I'm not saying that 'Access Sucks'.

I'm just saying that 'Access isn't a database; it is a front end for
SQL Server-- and that is it'.

-Aaron

 

[aaron.kempf]

because with Access MDB; you can only store this in a query.
You can't bind a calculation to a table.

In SQL Server, you can make a new columns 'ExtendedPrice' which is
equal to Quantity * Price.

In Access, you can write the equation in 300 places -- for all I care.

But in SQL Server; this is much easier.

Thanks & Good Luck (choosing better database architectures in the
future!)

-Aaron

Hi Frank,
The calculation is part of the query and I'll provide you with a sample that
you will have to translate to your exact needs.  However, the idea that you
have about saving that calculation to another table is against the norm indb
design and we can discuss that later.  Here's what you do to accomplish the
query (using the example I used earlier):

Get to the Query Design view

Add the two relevant tables (tblRates, tblUserInput)

Double-click the relevant fields to add them to the query (RateName,
RateAmt, UserQuantity)

For the fourth field, click into the 'Field' row and then click the
'Builder' wizard button-this will open the Expression Builder.  Using this,
you should be able to select the fields from the tables and/or queries that
you want to use in the calculation.  The result would look something like
this:

CalcTotal: [tblRates].[RateAmt] * [tblUserInput].[UserQuantity]

From reading your previous posts, I think that you'll be able to adapt this
to your table and field names and make it work for you.  

One thing concerns me and that is how you will relate the user input to a
particular driver or vehicle, but if you've got that covered then I'm
fretting uselessly.

As far as storing the calculated field into another table - why?  How is
storing it any better than letting Access recalculate it again when you need
it?

--
rpw

Table one hours are a persons (drivers) time and the miles are the amount of
miles they drove in that specified time. We pay the person by both hoursand
miles driven. Hence the hourly rate and the per mile rate. While the rates
remain constant the time and miles vary. I have other tables that are related
such as address from and delivery address, date of delivery, drivers name,
ect. This is kind of like a billing and payroll database for a very small
business.

Your example:
RateName      RateAmt      UserQuantity       CalcTotal
Hours            36.50           2                       73.00
Miles             00.75           37                      27.75
is exactly what I am trying to accomplish.

- Show quoted text -

 

[aaron.kempf]

It's not misdirections. It is not erroreous information.

There isn't an army of MDB dorks in the world that will convince me to
give up on my superior platform.

I laugh at you MDB script-kids and I pray for your sorry career

-Aaron

 

[rpw]

Thanks for the info, but it is useless unless I have SQL Server right? If I
did, I'd be on a SQL forum. But I don't so I'm on an Access forum. Do you
use Access? If not, then why are you here? This is for Access users isn't
it?

BTW, I don't like your delivery of the message. It is riddled with a "My
way's better than your way" supremacist attitude. If I get around by riding
a bike I really don't appreciate someone cruising by, laughing at me and
yelling "Get a Maserati!"

I've read your other posts in this thread and I see that the attitude
carries throughout. I suspect that Bob Q asks you to leave because your
attitude is unbecoming of an intelligent being and your suggestions are
inappropriate for this forum (people here help each other with ACCESS, not
SQL Server).

Perhaps if you go post on a forum dedicated to SQL Server you will get
positive feedback rather than the invitations to leave you get here.

It has become apparent to me that this is how you entertain yourself -
insulting Access and denegrating the choices people have made to use it -
basically it seems like you are trying to pick a fight online. Why? What's
the purpose? Who does it help? It seems to only gratify your ego because
you have the 'bigger' tool: SQL SERVER! So what! Who cares..

I, for one, will now ignore your thinly-veiled insults and ridicules. Have
a nice life and stay away from me please as I do not appreciate the way you
deliver information - it is really insulting to be 'talked down' to and
insulted for my choice of data manipulation tool.

Good bye...
--
rpw

because with Access MDB; you can only store this in a query.
You can't bind a calculation to a table.

In SQL Server, you can make a new columns 'ExtendedPrice' which is
equal to Quantity * Price.

In Access, you can write the equation in 300 places -- for all I care.

But in SQL Server; this is much easier.

Thanks & Good Luck (choosing better database architectures in the
future!)

-Aaron

Hi Frank,
The calculation is part of the query and I'll provide you with a sample that
you will have to translate to your exact needs. However, the idea that you
have about saving that calculation to another table is against the norm in db
design and we can discuss that later. Here's what you do to accomplish the
query (using the example I used earlier):

Get to the Query Design view

Add the two relevant tables (tblRates, tblUserInput)

Double-click the relevant fields to add them to the query (RateName,
RateAmt, UserQuantity)

For the fourth field, click into the 'Field' row and then click the
'Builder' wizard button-this will open the Expression Builder. Using this,
you should be able to select the fields from the tables and/or queries that
you want to use in the calculation. The result would look something like
this:

CalcTotal: [tblRates].[RateAmt] * [tblUserInput].[UserQuantity]

From reading your previous posts, I think that you'll be able to adapt this
to your table and field names and make it work for you.

One thing concerns me and that is how you will relate the user input to a
particular driver or vehicle, but if you've got that covered then I'm
fretting uselessly.

As far as storing the calculated field into another table - why? How is
storing it any better than letting Access recalculate it again when you need
it?

--
rpw

Table one hours are a persons (drivers) time and the miles are the amount of
miles they drove in that specified time. We pay the person by both hours and
miles driven. Hence the hourly rate and the per mile rate. While the rates
remain constant the time and miles vary. I have other tables that are related
such as address from and delivery address, date of delivery, drivers name,
ect. This is kind of like a billing and payroll database for a very small
business.

Your example:
RateName RateAmt UserQuantity CalcTotal
Hours 36.50 2 73.00
Miles 00.75 37 27.75
is exactly what I am trying to accomplish.
tblRates
RateID (primary key - keeps things organized in Access' mind)
RateName (text - the name of the rate)
RateAmt (currency - the amount of the rate)
tblUserInput
InputID (PK-primary key)
RateID (FK - aka 'foreign key' because this is how this table 'relates' to
the tblRate)
UserQuantity (number - this is what the user inputs after selecting a rate)- Hide quoted text -

- Show quoted text -

 

[Bob Quintal]

Thanks for the info, but it is useless unless I have SQL Server
right? If I did, I'd be on a SQL forum. But I don't so I'm on an
Access forum. Do you use Access? If not, then why are you here?
This is for Access users isn't it?

BTW, I don't like your delivery of the message. It is riddled
with a "My way's better than your way" supremacist attitude. If I
get around by riding a bike I really don't appreciate someone
cruising by, laughing at me and yelling "Get a Maserati!"

I've read your other posts in this thread and I see that the
attitude carries throughout. I suspect that Bob Q asks you to
leave because your attitude is unbecoming of an intelligent being
and your suggestions are inappropriate for this forum (people here
help each other with ACCESS, not SQL Server).

Perhaps if you go post on a forum dedicated to SQL Server you will
get positive feedback rather than the invitations to leave you get
here.

It has become apparent to me that this is how you entertain
yourself - insulting Access and denegrating the choices people
have made to use it - basically it seems like you are trying to
pick a fight online. Why? What's the purpose? Who does it help?
It seems to only gratify your ego because you have the 'bigger'
tool: SQL SERVER! So what! Who cares..

I, for one, will now ignore your thinly-veiled insults and
ridicules. Have a nice life and stay away from me please as I do
not appreciate the way you deliver information - it is really
insulting to be 'talked down' to and insulted for my choice of
data manipulation tool.

Good bye...

That being said, do you still need help with your original
calculation problem?

 

[BruceM]

No need to apologize. They're good enough for my needs. When they no
longer are, I'll change how I do things. Until then, excess capacity is of
no interest.

are you talking about 'in a query'?

that doesn't seem to be a computed column.
That seems to be a 'calculated column'.

You can either calculate a column.. or store a calculation. but
neither of those are as powerful as computed columns in SQL Server.

Sorry

-Aaron

 

[BruceM]

Jet is the database that comes with Access. Access itself is not the
database. From your previous postings I thought you understood that.

I'd rather know the answer ahead of time-- than to get surprised when
my manager hires a second secretary and all of a sudden -- we're past
the 30 user limit.

You can react or you can be proactive.

You can fool me once-- shame on you.
Fool me twice - shame on me.

You guys 'get tricked' by a piece of crap database once a week.. and
it's like you guys still don't learn.
If the database craps out-- if it doesn't meet your needs-- if it
isn't competitive and scalable-- then don't use Access MDB.

I'm not saying that 'Access Sucks'.

I'm just saying that 'Access isn't a database; it is a front end for
SQL Server-- and that is it'.

-Aaron

 

[rpw]

Hi Bob,

Both you and I have provided the OP with AfterUpdate code that would solve
his problem, but it looks like he might have been chased off with all the
unrelated activity (someone else's goal?).

But thanks for asking! ;-)

 

[aaron.kempf]

I disagree.

SQL Server is one of the most popular storage engines for Microsoft
Access applications.

SQL Server is included as an optional component on the Microsoft
Office disk-- and it has shipped that way for 70% of this decade.

Thanks

-Aaron

Thanks for the info, but it is useless unless I have SQL Server right?  If I
did, I'd be on a SQL forum.  But I don't so I'm on an Access forum.  Do you
use Access?  If not, then why are you here?  This is for Access users isn't
it?

BTW, I don't like your delivery of the message.  It is riddled with a "My
way's better than your way" supremacist attitude.  If I get around by riding
a bike I really don't appreciate someone cruising by, laughing at me and
yelling "Get a Maserati!"

I've read your other posts in this thread and I see that the attitude
carries throughout.  I suspect that Bob Q asks you to leave because your
attitude is unbecoming of an intelligent being and your suggestions are
inappropriate for this forum (people here help each other with ACCESS, not
SQL Server).

Perhaps if you go post on a forum dedicated to SQL Server you will get
positive feedback rather than the invitations to leave you get here.

It has become apparent to me that this is how you entertain yourself -
insulting Access and denegrating the choices people have made to use it -
basically it seems like you are trying to pick a fight online.  Why?  What's
the purpose?  Who does it help?  It seems to only gratify your ego because
you have the 'bigger' tool: SQL SERVER!  So what!  Who cares..

I, for one, will now ignore your thinly-veiled insults and ridicules.  Have
a nice life and stay away from me please as I do not appreciate the way you
deliver information - it is really insulting to be 'talked down' to and
insulted for my choice of data manipulation tool.

Good bye...
--
rpw

because with Access MDB; you can only store this in a query.
You can't bind a calculation to a table.

In SQL Server, you can make a new columns 'ExtendedPrice' which is
equal to Quantity * Price.

In Access, you can write the equation in 300 places -- for all I care.

But in SQL Server; this is much easier.

Thanks & Good Luck (choosing better database architectures in the
future!)

-Aaron

Hi Frank,
The calculation is part of the query and I'll provide you with a sample that
you will have to translate to your exact needs.  However, the idea that you
have about saving that calculation to another table is against the norm in db
design and we can discuss that later.  Here's what you do to accomplish the
query (using the example I used earlier):
Get to the Query Design view
Add the two relevant tables (tblRates, tblUserInput)
Double-click the relevant fields to add them to the query (RateName,
RateAmt, UserQuantity)
For the fourth field, click into the 'Field' row and then click the
'Builder' wizard button-this will open the Expression Builder.  Using this,
you should be able to select the fields from the tables and/or queriesthat
you want to use in the calculation.  The result would look somethinglike
this:
CalcTotal: [tblRates].[RateAmt] * [tblUserInput].[UserQuantity]
From reading your previous posts, I think that you'll be able to adaptthis
to your table and field names and make it work for you.  
One thing concerns me and that is how you will relate the user input to a
particular driver or vehicle, but if you've got that covered then I'm
fretting uselessly.
As far as storing the calculated field into another table - why?  How is
storing it any better than letting Access recalculate it again when you need
it?
--
rpw
Table one hours are a persons (drivers) time and the miles are the amount of
miles they drove in that specified time. We pay the person by both hours and
miles driven. Hence the hourly rate and the per mile rate. While therates
remain constant the time and miles vary. I have other tables that are related
such as address from and delivery address, date of delivery, driversname,
ect. This is kind of like a billing and payroll database for a very small
business.
Your example:
RateName      RateAmt      UserQuantity       CalcTotal
Hours            36.50           2                       73.00
Miles             00.75           37                      27.75
is exactly what I am trying to accomplish.
tblRates
RateID (primary key - keeps things organized in Access' mind)
RateName (text - the name of the rate)
RateAmt (currency - the amount of the rate)
tblUserInput
InputID (PK-primary key)
RateID (FK - aka 'foreign key' because this is how this table 'relates' to
the tblRate)
UserQuantity (number - this is what the user inputs after selecting a rate)- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[aaron.kempf]

It's not called 'excess capacity'.

It is called 'centralizing equations' instead of 'storing
calculations'.

and it is the right way to do things.

Thanks

-Aaron

 

[aaron.kempf]

Jet is _ONE_ of the database that come with Access.

SQL Server is the same price-- easier-- and more powerful.

Do the math

-Aaron

 

[Homer J Simpson]

Table 1 has two fields: Hours and miles. Table 2 has two Fields: $36.50
and
$.75. I just need to take the numbers from table 1 and times them by table
2
and save to table 3 for billing purposes. I know that in a form or report
I
can take the field from table 1 and in a unbound text box have it times by
a
defined amount. The problem with that is I need the history stored and
retreiveable.

Perfectly reasonable and quite doable.

How will you identify the rows in Table 3? By date / employee ???

 

[Tony Toews [MVP]]

It is fairly rare that you'd need to <save> the calculated value. Instead,
use a query to calculate the value 'on the fly'.

I somewhat disagree for this specific question by Frank. The rate in
question might very well be the rate at the time of the rate
calculation. Thus Frank may prefer to save the rate on either table2
or table3 as appropriate and then calculate the value "on the fly"

This would be the same situation as in saving a part cost and price on
a transaction table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/