Access- How to build query

Posted on 2012-08-12
Last Modified: 2012-08-27
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

Question by:motioneye
    LVL 15

    Assisted Solution


    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.
    LVL 29

    Expert Comment

    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]

    Author Comment

    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
    LVL 29

    Expert Comment

    Can you explain how you're putting it in a form?
    LVL 61

    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now