Trying to join 2 fields from T1 to a single field in T2

[Karl Blessing]

Ok I am working in Access to create a SQL Statment and query to use in ASP, I have two tables, one table has two different Years. The other has a single year.

[tt]
Table 1 | Table 2
------------------------
Key_Year | Year
Year1 |
Year2 |
[/tt]

I want to return all records from Table2 that match either Year1/2 from Table1, since I'll be setting a creteria on Key_year.

But when I make join links from both YEar1/2 to YEar, it says I have a Join Mismatch

Karl
kb244@kb244.com

http://kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

 

[kathryn]

You can add a table more than once to a query. Have you tried adding Table1 twice, and linking each to Table2?

Kathryn

 

[Karl Blessing]

Even when Creating a second T2, and joining Year1 to T2(1) and Year2 to T2(2) I still get a Join Mismatch error

also I am wanting the records to come back normally, like if it has both Year1 and Year2

I want a table that is like

---------------------------
Records returned matching YEar1
Records Returned matching Year2

Not
-----------------------------
Records returned matching Year1 appended to records matching Year2

Karl
kb244@kb244.com

http://kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

 

[Karl Blessing]

here is the SQL Statement generated from it

SELECT CgDetPt1.*
FROM cg_compyear RIGHT JOIN CgDetPt1 ON (cg_compyear.individual_date_year2 = CgDetPt1.Year) AND (cg_compyear.individual_date_year1 = CgDetPt1.Year)
WHERE (((cg_compyear.key_year)="1996&quot);

I have Year1 and Year2 pointing to the same Table/Field, It returns an empty record, and I am 100% Positive I have 1996 in the year fields

Karl
kb244@kb244.com

http://kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

 

[Karl Blessing]

I Think I did it with this

SELECT CgDetPt1.*
FROM cg_compyear RIGHT JOIN CgDetPt1 ON (cg_compyear.individual_date_year2 = CgDetPt1.Year) OR (cg_compyear.individual_date_year1 = CgDetPt1.Year)
WHERE (((cg_compyear.key_year)="1996&quot);

changing AND to OR, unfortunatly Access cannot Represent this on the design view.

Karl
kb244@kb244.com

http://kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

 

[Karl Blessing]

ok Here we go, I managed to get both to work for matching up with years that exist in one of the tables

Using a CgDetYear query to do the harder stuff. then doing the rest in ASP where parameters are required (it would be nice to fill in the blanks right into the query so I wouldnt have to result to typing alot in the page itself)

To get back data for year range for single

SQLQuery1 = "SELECT * FROM CgDetYear WHERE Institution = '" & Institution & "' AND Program = '" & gProgram & "' AND key_year = '" & Request("cohort_year") & "'"
Set RS1 = Conn.Execute (SQLQuery1)

to get back year range for total of everything of that program

SQLQuery2 = "SELECT Sum(CgDetYear.Completers) AS Completers, Sum(CgDetYear.WageEarners) AS WageEarners, Sum(CgDetYear.Wage14999) AS Wage14999, Sum(CgDetYear.Wage24999) AS Wage24999, Sum(CgDetYear.Wage49999) AS Wage49999, Sum(CgDetYear.Wage50000) AS Wage50000, Sum(CgDetYear.Military) AS Military, CgDetYear.Year FROM CgDetYear GROUP BY CgDetYear.Year, CgDetYear.Program, CgDetYear.key_year HAVING (((CgDetYear.Program)='" & gProgram & "') AND ((CgDetYear.key_year)='" & Request("cohort_year") & "'))"

RS2.Open SQLQuery2, Conn, 3, 1

so that seems to work fine now. Least I hope so, it tested out well on the page

Karl
kb244@kb244.com

http://kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151