locked
need to combine items from two table rows into one view row RRS feed

  • Question

  • User1280950372 posted

    Working in Access, I need to create a view which takes values from two different records in another table and puts them on one row in the view. I'm having a hard time doing this since both row items come from the same field in the original table (actually in this case, using views instead of tables).

    I have two queries I try to join together, but come up with the wrong results:
    vwComp1:

    SELECT q.CompStatus, q.EQID, c.CompsID, q.LastName, q.FirstName
    FROM _EQ AS q, Comps AS c
    WHERE q.CompStatus=1 AND c.CompsID=q.CompID

    vwComp2:
    SELECT q.CompStatus, q.EQID, c.CompsID, q.LastName, q.FirstName
    FROM _EQAS q, CompsAS c
    WHERE q.CompStatus=2 AND c.CompsID=q.CompID

    vwAssignment:
    SELECT vwComp1.*, vwComp2.*
    FROM vwComp1, vwComp2

    This does fine in the vwComp2 part of vwAssignment, in the sense that it displays the correct name information, etc.; but for vwComp1, it just takes the name and other fields from the first row of vwComp1 and repeats it on every row of vwAssignment. This is really strange because vwComp1 and vwComp2, when displayed individually, each correctly puts the names in each row; something like this:

    Bob White        Rhonda Red
    Bob White        Freda Freschetta
    Bob White        Linda Lime
    Bob White        Jean Green

    --when it should read something like this:
    Bob White       Rhonda Red
    Fred Redd       Freda Freschetta
    Jim Brown       Linda Lime
    Rob Black        Jean Green

    Any suggestions? 

    Saturday, May 8, 2010 5:44 PM

Answers

  • User1280950372 posted

    It looks like I finally resolved the problem. Basically, I dragged tbl_EQ onto the editor two times.

    SELECT q.LastName, q.FirstName, r.LastName, r.FirstName
    FROM (tblComps AS c INNER JOIN tbl_EQ AS q ON c.Comp1ID=q.EQID) INNER JOIN _EQ AS r ON c.Comp2ID=r.EQID
    WHERE q.EQID=c.Comp1ID And r.EQID=c.Comp2ID
    


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 11, 2010 2:02 PM