May 5, 2010 at 11:31 pm
Working in Access, I need to create a view which takes values from two different records 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.
Both records are listed in distinct grouping categories, e.g. both from District field in record #5, but differentiated with a Status field of 1 or 2 (Status being child to District).
(I'd like to script out the tables I'm using, according Jeff Moden's suggestion at this site, but I've found that Access doesn't do that like SQL Server does.)
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 _EQ AS q, Comps AS 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?
May 11, 2010 at 12:06 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply