locked
Derived columns (attributes) in Access 2007 RRS feed

  • Question

  • i have a table as follows:

    stock(

    MID number(7) primary key,

    quantity number(7),

    price currency,

    amount curency

    );

    I want to calculate the amount as price*quantity for each record, instead of taking its input from user.

    So i want to know if i can do this in Access 2003 or 2007.

    Also can i take some values from other table to calculate and input data directly (without user's input) ?

    Saturday, November 26, 2016 2:45 PM

Answers

  • Hi,

    Access provides a Query Wizard and a Query Designer to make it easy for you to create queries. Once you get good with it, you can then go straight to designing queries using SQL statement.

    To create a query in Access, go to the Ribbon and select the Create tab. In the Queries group, select either the Query Wizard or the Query Designer.

    If you select the Wizard, just follow the prompt screens. If you select the Designer, the Select Tables window should pop up. Just add the tables you want to include in your query and then close the table window. Then, drag each field you want to show in the query result onto the query grid. Add any calculations you want by entering the expression in an empty column You can also add any criteria, if necessary. Save the query and then open it to view the result.

    Hope it helps... 

    • Marked as answer by mittal0109 Monday, November 28, 2016 4:42 PM
    Saturday, November 26, 2016 5:42 PM
  • Hi mittal0109,

    as .theDBguy already suggested you that you can create queries by 2 ways using Query Wizard and a Query Designer.

    here is the example of that.

    if you want to write query then please refer the example mentioned below.

    I created a table like yours.

    then I write a query like mentioned below. I pass the parameters which will prompt you to enter values.

    insert into stock values (mid,quantity,price, [quantity]*[price]);

    when I run this query I get result like mentioned below.

    same way you can do this using Query designer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, November 28, 2016 12:59 AM

All replies

  • Hi,

    You can do it at the table level if you were using Access 2010 or higher. For Access 2003 and 2007, you can only do it using queries, which is really the correct way of doing it because storing calculated values in tables is against "Normalization" rules.

    Just my 2 cents...

    Saturday, November 26, 2016 3:48 PM
  • Thank you, this is the answer i was searching for, can you help me out to show where to write queries, i can write queries on my own, just need the path for it.
    Saturday, November 26, 2016 4:58 PM
  • Hi,

    Access provides a Query Wizard and a Query Designer to make it easy for you to create queries. Once you get good with it, you can then go straight to designing queries using SQL statement.

    To create a query in Access, go to the Ribbon and select the Create tab. In the Queries group, select either the Query Wizard or the Query Designer.

    If you select the Wizard, just follow the prompt screens. If you select the Designer, the Select Tables window should pop up. Just add the tables you want to include in your query and then close the table window. Then, drag each field you want to show in the query result onto the query grid. Add any calculations you want by entering the expression in an empty column You can also add any criteria, if necessary. Save the query and then open it to view the result.

    Hope it helps... 

    • Marked as answer by mittal0109 Monday, November 28, 2016 4:42 PM
    Saturday, November 26, 2016 5:42 PM
  • Hi mittal0109,

    as .theDBguy already suggested you that you can create queries by 2 ways using Query Wizard and a Query Designer.

    here is the example of that.

    if you want to write query then please refer the example mentioned below.

    I created a table like yours.

    then I write a query like mentioned below. I pass the parameters which will prompt you to enter values.

    insert into stock values (mid,quantity,price, [quantity]*[price]);

    when I run this query I get result like mentioned below.

    same way you can do this using Query designer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, November 28, 2016 12:59 AM