Calculations within database

[Matthew]

I am trying to add a column to the table that will display the results of a
calculation from data in a different column multiplied by a fixed dollar
amount. This information is used for billing and therefore imported into a
Word form letter. Example:
First Name; Last Name; Address; Yadayadayada -> Length X $30 = Balance Due
Please help this user with the correct simple syntax.

matthew

 

[Tim Ferguson]

I am trying to add a column to the table that will display the results
of a calculation from data in a different column multiplied by a fixed
dollar amount. This information is used for billing and therefore
imported into a Word form letter. Example:
First Name; Last Name; Address; Yadayadayada -> Length X $30 =
Balance Due

SELECT FirstName, LastName, Address,
Format(Length * 30, "Currency") AS Yadayadayada,
BalanceDue

FROM MyTable

WHERE BalanceDue > 0

ORDER BY LastName, FirstName


.... in other words, do calculations in the query, and use the query to base
the mailmerge in Word.

Tables are for storage, not for exporting or transfer!

Hope that helps


Tim F

 

[Matthew]

I had a temporary slip of the memory re the use of a table vs. a query. I
do use a query to import the information to the Word form, but I am unsure
of the correct ssyntax to get the product of the column "LOA" X $30.00 to
display in the query. Please help, the help in Access is very confusing to
me.
matthew

 

[TedMi]

SELECT Name, Address, YadaYada, LOA*30 as BalanceDue FROM etc.

Note: Do not use spaces in field/result names

 

[uchi]

The memory can be assisted by loads of drugs .. or actually learning the
right material

First .. NEVER include calculations as part of a working database, you may
include the result to speed up any processing based on the calculation ..
but .. a table is for RAW data not lengthy querying.

OK for all the morons who do Access .. lets say it all over again .......

a TABLE is the RAW data left for other parts of the app to process
a QUERY is the DERIVED data based on results from the TABLE data
a REPORT is any FORMATTED information based on either results from one or
more queries or formatted table data
a MACRO is ROUTINES used for making repetitive instructions easier
a MODULE is pure VBA CODE for interacting with other objects and deriving
results from any table or query data. THIS IS WHERE U DO YOUR CALCULATIONS
!!!!

oh yeah .. it helps if you actually build an ACID transaction process ..
(look that bit up)

 

[Tim Ferguson]

I had a temporary slip of the memory re the use of a table vs. a
query. I do use a query to import the information to the Word form,
but I am unsure of the correct ssyntax to get the product of the
column "LOA" X $30.00 to display in the query. Please help, the help
in Access is very confusing to me.

Open a New query in the Query Design grid, and pull in any fields you
actually want to see. Now select an empty column and enter this in the top
row:

Yadayadayada: FORMAT(LOA * 30, "Currency")

and leave the rest of the column empty (unless you want to sort according
to the yadayadayada column), except for the 'Display' which should be
checked of course. The new column will be called yadayadayada but you can
change it to something more sensible if you like.

Remember that the multiplication sign in nearly all computer applications
is a star * not a letter ex X.

Hope that helps


Tim F