Results 1 to 2 of 2
  1. #1
    jplank is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2013
    Posts
    1

    Multiplying two values from the same column

    Name Number1 Number2
    Name1 1 2
    Name1 2 1
    Name1 3 -1
    Name1 4 3
    Name1 5 5
    Name2 1 2
    Name2 2 5
    Name2 3 2
    Name2 4 0
    Name2 5 1
    ...
    ...

    For each Name, Number1 will always contain the values from 1 to 5. Number2 can vary, however.

    I need help constructing a query that, for each name, will look at the values in the Number2 column when Number1 is 1 or 2 and multiply them. So, the result I'm looking for is:

    Name1 2
    Name2 10



    (Since 2 = 2 x 1 and 10 = 2 x 5)

    I haven't had much look Googling (most search results involve multiplying two columns together, which isn't exactly what I'm after).

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    This query will work as long as there is only one value per [Name] and [Number1]. If there are more than one value per [Number1] = 1 or 2 then this query will not work.

    SELECT DISTINCT Test1.Name1, DLookUp("Number2","Test1","Name1 = """ & [Name1] & """" & " AND Number1 = 1")*DLookUp("Number2","Test1","Name1 = """ & [Name1] & """" & " AND Number1 = 2") AS Expr3
    FROM Test1
    WHERE (((Test1.Number1)=1 Or (Test1.Number1)=2));

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

Similar Threads

  1. Replies: 2
    Last Post: 12-05-2011, 04:53 AM
  2. Replies: 3
    Last Post: 09-28-2011, 04:29 PM
  3. Multiplying Values from a form and table
    By Lxmanager in forum Access
    Replies: 5
    Last Post: 02-19-2011, 07:07 PM
  4. Multiplying
    By chazcoral2 in forum Forms
    Replies: 16
    Last Post: 09-10-2010, 01:44 PM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 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