Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2004
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Query Criteria (Access 2003)

    I need help with SQL. I have a table similar to the one below.


    <table border=1><td>Item</td><td>Operation</td><td>Hours</td><tr><td>A</td><td>1</td><td>10</td><td>A</td><td>2</td><td>0</td><td>B</td><td>2</td><td>5</td></table>

    I only want to return the items with both operations 1 and 2. In this example item A. Then, I want to divide the hours for operation 1 between operations 1 and 2. For example operation 1 would have 5 hours and operation 2 would have 5. What SQL command will give this result?

    Thanks in advance,

    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple Query Criteria (Access 2003)

    Will there be at most two records per Item value, or can there be multiple records for Item=A and Operation=1?
    Can Operation have other values than 1 and 2?

    Please provide as much relevant detail as you can.

  3. #3
    Lounger
    Join Date
    May 2004
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Query Criteria (Access 2003)

    The combination of item code and operation code makes a record unique. Each item in the table may not have either operation 1 or 2. Each item may have other operations besides 1 and 2. Not all items will have the same operations or the same number of operations (up to 9 different operations). Each operation will be used by many items.

    I need to work only with items that have both operation 1 and operation 2.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple Query Criteria (Access 2003)

    Create a new query in design view.
    Add your table twice. The second instance will be assigned an alias consisting of the table name followed by _1.
    Join the two instances on the Item field.
    Add the Item field from the first instance, and the Operation field from both tables to the query grid.
    Enter 1 in the Criteria line under the first Operation field, and 2 under the second one.
    Clear the Show check box for both Operation columns.
    Add a calculated column (I have named the table tblTest for illustration purposes):

    <code>AvgHours: ([tblTest].[Hours]+[tblTest_1].[Hours])/2</code>

    Unfortunately, you cannot use this query directly in an update query, for updating the Hours field will instantaneously change AvgHours. You must change this query to a make-table query and run it. Let's say that you create a table tblTemp. Here is the SQL:

    SELECT tblTest.Item, ([tblTest].[Hours]+[tblTest_1].[Hours])/2 AS AvgHours INTO tblTemp
    FROM tblTest INNER JOIN tblTest AS tblTest_1 ON tblTest.Item = tblTest_1.Item
    WHERE (((tblTest.Operation)=1) AND ((tblTest_1.Operation)=2));

    Now create a new query based on tblTest and tblTemp. Join them on Item, and add the Hours and Operation fields from tblTest. Change the query to an update query, and enter tblTemp.AvgHours in the Update to line under Hours. Enter 1 or 2 in the Criteria line under Operation. Run this query to update the values. The SQL is:

    UPDATE tblTemp INNER JOIN tblTest ON tblTemp.Item = tblTest.Item SET tblTest.Hours = [tbltemp].[AvgHours]
    WHERE (((tblTest.Operation)=1 Or (tblTest.Operation)=2));
    Attached Images Attached Images
    • File Type: png x.PNG (13.5 KB, 0 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •