• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Access- How to build query

Do You know in access 2007 let says I have column call [amount],[cost] and [profit]

How do I create function or similar so that [profit] will have value from [amount]-[cost]

I do not know where to start will column properties in access

2 Solutions

You should create a table with amount and profit. These columns are the only data you have to store, as profit is a calculated column.

Then create a query object based on your table. Add both columns, and add a 3 calculated column just writing this on its name:

[profit]: [amount]-]cost]

Then execute the query. This should work.

Hope it helps. Regards.
And in case you need a visual, here you go.
query builderDouble click on Amount and Cost in your table will add them to the bottom section.  (You can also drag them from the table to the bottom section).  Then as gplana said, just add a calculated field for profit.  
profit: [amount]-[cost]
motioneyeAuthor Commented:
The query run perfect when I execute it, but when I create it as a one of my field in access form, it does not show me the values
Can you explain how you're putting it in a form?
Are Amount and Cost included in your form's recordsource (in other words, are they included in the table or query that your form is based on)?

If yes, then you can either add the expression for Profit to your form's query as described previously, or you can use the following expression in the Control Source property of a textbox on your form, including the = sign:

 = [Amount] - [Cost]

Open in new window

If Amount and Cost are NOT part of your form's recordsource, you may have to use a VBA function such as DLookup.

Place the following, including the = sign in the control source property of your textbox:

= DLookup("Amount - Cost", "YourQueryName")

Open in new window


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now