Trying to create a join between two tables

[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

 

[foxdev]

select key_year from table1, table2 where table2.year = table1.year1
union
select key_year from table1, table2 where table2.year = table1.year2


Robert Bradley

http://www.foxdev.com/

 

[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