Databases
StackOverflow2013
Postsdbo
POAggregating data in two different ways within a single record
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAggregating data in two different ways within a single record
    primarykey
    data
    text
    <blockquote> <p><strong>Possible Duplicate:</strong><br> <a href="https://stackoverflow.com/questions/10352750/need-help-for-my-sql-query">Need help for my SQL query</a> </p> </blockquote> <p>Well I may have posted this earlier also, but my requirements changed a bit, so please help me on this one. </p> <p>My database structure: </p> <p><strong>ATT</strong> (<code>Activity</code> table)</p> <ul> <li>Act_ID(PK)</li> <li>Assigned_To_ID (FK, refer to <code>Emp_ID</code>)</li> <li>Project_ID (FK, refer to <code>Project_ID</code>)</li> <li>Product_ID (FK, refer to <code>Product_ID</code>)</li> <li>Status (can be <code>New, OnHold, Cancel, Closed</code>)</li> </ul> <p><strong>Product_Table</strong> </p> <ul> <li>Product_ID (PK)</li> <li>Product_Name</li> </ul> <p><strong>Project_Table</strong> </p> <ul> <li>Project_ID (PK)</li> <li>Project_Name</li> </ul> <p><strong>Employee_Table</strong> </p> <ul> <li>Emp_ID (PK)</li> <li>F_Name.</li> </ul> <p><strong>Emp_Product_Project_Mapping</strong></p> <p>(This table basically maps which employee is assigned to which product and which project. Duplicates entry are allowed in all the 3 columns, to map that 1 employee can be assigned to multiple project or products)</p> <ul> <li>Emp_IDFK(FK refer to Emp_ID PK)</li> <li>Product_IDFK(FK refer to Product_ID PK)</li> <li>Project_IDFK(FK refer to Project_ID PK)</li> </ul> <p><strong>Constraints</strong> </p> <ul> <li>In 1 Project --> Multiple employees can work</li> <li>1 Employee -- > Can be assigned multiple activities (<code>Act_ID</code>)</li> <li>At any given point of time <code>Status</code> can be any of those given values.</li> <li>At any given point an activity can be assigned to 1 product and 1 project.</li> </ul> <p>Now in my SQL query what I want to do is a load check ::</p> <p>An activity (represented by <code>Act_ID</code>) is assigned to an employee is represented by <code>Assigned_To_ID</code>, for Project (<code>Project_ID</code> in <code>ATT_Table</code>) for product(<code>Product_ID</code> in <code>ATT_table</code>) and a particular status. I need to output 5 values from my SQL query: </p> <ol> <li>Name of Employee</li> <li>Name of Product</li> <li>Number of total activities assigned for that Product(in 2) where Status is either New or InProcess.(Here, i am taking count of all the activities that are related to Product (in 2).No matter to whom it is assigned. Its not related to Employee (in 1). That i want in my 4rth part). </li> <li>Now,i want the count of activities that Employee (in 1) is assigned to(out of total count in i have got from (3)). Hope i am making myself clear. In 3rd it was Total activities assigned to product(in 2). In 4rth its activities out of my result of 3rd, assigned to an employee in 1st. </li> <li>Load. That will be = (Result in 3)/(result in 4) * 100 ((Result in 3) is a larger value than (result in 4), which is obvious.) </li> </ol> <p>Now There may be a condition That an employee let's say E1 is working on product P1,P2. So my table output will be look like this:: </p> <pre><code>1 2 3 4 5 E1 P1 E1 P2 </code></pre> <p>So here 3, 4, 5 will be having values corresponding to activities for that Product(P1 or P2)</p> <p>Please help me on this one. Using MSaccess database. Thanks.. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USShantanu Tomar
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTDuplicate
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAggregating data in two different ways within a single record
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAggregating data in two different ways within a single record
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POAggregating data in two different ways within a single record
      1. This table or related slice is empty.
      1. VTModeratorReview
    1. COjust tell me where to send the invoice
      singulars
      1. POAggregating data in two different ways within a single record
      1. USᴳᵁᴵᴰᴼ
    2. COWell, this is somewhat related to my previous post.. and this is kind of an edited ques. Please refer to link http://stackoverflow.com/questions/10352750/need-help-for-my-sql-query/10353148#comment13370080_10353148 Thanks.
      singulars
      1. POAggregating data in two different ways within a single record
      1. USShantanu Tomar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload