How to create query to calculate percentage

[straw]

Hello Forum Access Expert,



I have just started working on access database related project and I wonder if someone can advise me on how I can create a query to help calculate 80% of transaction amounts, basically there are 3,000 transaction and I need to create a query to calculate the transactions for at the click of a button. I can set create a form but I have no idea how the set up the query.

From the Master table, I have "Transaction Amount" and I have created a new field "Net Interest" which will be 80% of the individual transaction amount for the 3000 transactions.
Any advise would be much appreciated. Please see attached attempt, it doesn’t look right :-)


I look forward to hearing back soon.
Thanks in Advance

Straw

[Dal Jeanis]

The answer depends on what you are doing with it.

Let's suppose you want the query to return two values, the [Interest Value] and a calculated field called [Gross Interest Value] which is .8 * [Interest Value]

The left column would be

FIELD  [Interest Value] 
TABLE  [tbl_Master_Tax]

The right column would be

FIELD  [Gross Interest Value]: 0.8 * [tbl_Master_Tax].[Interest Value]
TABLE  (leave blank)

Now, as written, that query isn't good for anything much, because you can't related it back to any data. About all it will be useful for is calculating the Sum. You could get the sum easier, with the following SQL:

SELECT sum(0.8*[Interest Value])
FROM tbl_Master_Tax;

The 0.8 could be inside or outside the sum, and you'd get the same mathematical result.

[straw]

Hi Dal, Thank you for your response.

I should have warned the community that I have very basic access skills and we are using access 2003 without any link to SQL but then I could be wrong.
With the query, what I would like is for Access to calculate 80% of all the transaction amounts and also to be able to be able to calculate 20% of these transactions.
There are 3 fields within the Master Table.

• Trans_Amount
• Net Interest (which is 80% of Trans_Amount
• Tax (which is 20% of Tran_Amount)

I can easily do this in Excel but unable to replicate the process Access especially when the business want to generate this report at the click of a button. I hope all of this makes any sense to you, will very much appreciate your advice .

Regards,

T

[straw]

My apologies Dal, I now understand what you mean with regards to the SQL query. As of the time I emailed you I was a complete novice but having spent a bit of time on the database I have discovered new things.
Thank you very much.

[Dal Jeanis]

Hmmm. I must never have hit "post" on my reply yeterday.

Glad you got it worked out. Let us know if you have any more questions. If not, please mark the thread "Solved". Top of page, under "Thread Tools".

By the way, no apologies necessary. We're here to help, and telling you WHERE to poke around is one of the ways that we help you learn what you need to know to do your particular job.

[straw]

Thank you very much, I will mark this as resolved. I will be posting another question shortly.