5167 Shares

How do I find the 10 % deposit for each job using a query in Access?

How do I find the 10 % deposit for each job using a query in Access? Topic: Guess jobs application
June 24, 2019 / By Neville
Question: The instructions say, if each customer is to make a 10 percent deposit for each job, display and print the Job ID, customer ID, and deposit amount for every record in the Jobs table. (Use properties on the shortcut menu to format deposits with a dollar sign and two decimal positions.)
Best Answer

Best Answers: How do I find the 10 % deposit for each job using a query in Access?

Kip Kip | 6 days ago
I am not sure if I understood properly your question, but I think your query has to look something like this: Select Jobs.Id, Jobs.CustomerId, Jobs.DepositAmount From Jobs Where Jobs.DepositAmout >= Jobs.DepositAmount * .1 If you need more info from the Customer, you will need to make a join to customers table, but in my example I assume the CustomerId is present on the jobs table. No Idea what ar you talking about on the shortcut menu to format deposits... I guess is something related to the query editor application you are using. Good luck!
👍 160 | 👎 6
Did you like the answer? How do I find the 10 % deposit for each job using a query in Access? Share with your friends

We found more questions related to the topic: Guess jobs application


Kip Originally Answered: How do you add count column to query results in SQL MS Access?
Unfortunately SQL doesn't do things they way your example shows. It will not add the value to an existing table in a new column with a straight query. You could probably create a temporary table containing the first set of data and then write a subquery to calculate the numbers you are looking for and then reference everything with a third view to get the formatting you want. It can be done, but there is not a simple way to do it through a single Query. Good luck!
Kip Originally Answered: How do you add count column to query results in SQL MS Access?
Well, there probably is a way to do this. However, best database practice is to never use a calculated value for an actual column in a table. If you can calculate the value using a query, you shouldn't take up valuable disk space storing it. And if you are using Access, you are going to have a tough time using the better SQL functions - I know. Been there, done that. I ended up having to do alot of coding in the VBA behind to get some of the stuff to work. Access sucks, so I feel for you. Good luck : )
Kip Originally Answered: How do you add count column to query results in SQL MS Access?
SELECT aField, cnt FROM YourTable INNER JOIN (SELECT aField AS f, count(*) AS cnt FROM YourTable group by aField) AS x on YourTable.aField=x.f

Hunter Hunter
When creating the query just build an expression. the expression would be Table1.Field * .1 Hope that helps.
👍 60 | 👎 0


If you have your own answer to the question guess jobs application, then you can write your own version, using the form below for an extended answer.