Results 1 to 4 of 4
  1. #1
    Govyn is offline Novice
    Windows XP Access 2000
    Join Date
    Jun 2010
    Posts
    3

    Adding tabulated values in a query

    Access 2000, Access level: beginner

    Hello everybody,

    I'm experiencing some difficulties adding values in a query. Here is a simplified description of my DB.

    Table: Project
    Fields: Proj_ID (Primary key), Proj_Name, Prio_desc, Comp_desc, VA_desc.

    Table: Priority
    Fields: Prio_desc (Primary key), Prio_Value

    Table: Complexity
    Fields: Comp_desc (Primary key), Comp_Value

    Table: Value Added
    Fields: VA_desc (Primary key), VA_Value

    The fields With the same name are linked together

    Essentially my user will enter a new project or modify the characteristics of an existing project.



    I'm trying to make a query that will add the values and return a score that can be used to rank the order in which my projects need to be accomplished. Here is what I've done:

    Query: Score

    Fields: Proj_ID, Proj_Name, Expr1: [Score] [in criteria : ((Comp_Value + VA_Value) * Prio_Value)]

    This does not work, I get a text box asking me to enter a parameter.

    I welcome any and all advise.

    Thank you in advance

    Govyn

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You should not be storing the desc in more than 1 table. The better way to handle this is to store the primary key as a foreign key. Additionally, it is more efficient for the primary and foreign key fields to be numeric rather than text datatypes

    tblProject
    -Proj_ID Primary key, autonumber
    -Proj_Name
    -fkPriorityID
    -fkComplexityID
    -fkValueAddID


    tblPriority
    -pkPriorityID (primary key)
    -Prio_desc
    -Prio_Value

    tblComplexity
    -pkComplexityID primary key, autonumber
    -Comp_desc
    -Comp_Value

    tblValueAdded
    -pkValueAddID primary key, autonumber
    -VA_desc
    -VA_Value

    Using the above, the query would be as follows:

    SELECT tblProject.txtProjectName, tblComplexity.comp_value, tblPriority.Prio_Value, tblValueAdded.VA_Value, ([Comp_Value]+[VA_Value])*[Prio_Value] AS score
    FROM tblValueAdded INNER JOIN (tblPriority INNER JOIN (tblComplexity INNER JOIN tblProject ON tblComplexity.pkComplexityID = tblProject.fkComplexityID) ON tblPriority.pkPriorityID = tblProject.fkPriorityID) ON tblValueAdded.pkValueAddID = tblProject.fkValueAddID;


    On another note, usually when you have similarly design tables, it is usually a sign that the tables are not normalized. You can probably set up a table that holds all 3 parameters (priority, complexity and value add) in a table and then join them to the project via a many-to-many relationship. Something along these lines:

    tblParameters
    -pkParameterID primary key, autonumber
    -fkParameterTypeID foreign key to tblParameterTypes
    -txtParameterDesc
    -parametervalue

    tblParameterTypes (3 records: priority, complexity and value add)
    -pkParameterTypeID primary key, autonumber
    -txtParameterType

    then bring the parameters and project together

    tblProjectParameters
    -pkProjParameterID primary key, autonumber
    -fkProjectID foreign key to tblProjects
    -fkParameterID foreign key to tblParameters

  3. #3
    Govyn is offline Novice
    Windows XP Access 2000
    Join Date
    Jun 2010
    Posts
    3
    Thank you for your response. I'll try to adapt the code to my actual tables. I'll have to look into the normalized design, although it might be a bit much for me to chew right now.

    Thanks again

    Govyn

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What happens if someone wants to add a new parameter on which the project is to be rated? You would have to add a new table and then change any related queries, forms or reports. With the normalized design you would just add a new record to the parameter type table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding tabulated values in a query
    By Govyn in forum Access
    Replies: 0
    Last Post: 06-28-2010, 08:56 AM
  2. Adding Totals to a query using queryDef
    By jrickels in forum Programming
    Replies: 7
    Last Post: 04-06-2010, 07:47 AM
  3. Replies: 1
    Last Post: 10-09-2009, 11:52 AM
  4. Replies: 10
    Last Post: 08-01-2009, 06:48 AM
  5. Adding new column to make-table query
    By dtn118 in forum Access
    Replies: 2
    Last Post: 08-03-2008, 06:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums