SQL Join statement

M

margaret

I have a table (Class Table) ....

Class ID (Key), Division, Class Description, and Class

On my second table I have

Entry # (key), Division and Class

I need to take the Division and Class from the second table and get the
class description from the first table. If I create a relationship between
the class field in both table, I end up with 100,000 entries because the
Class can use the same number for different divisions. It's the combination
of the Division and Class that gives the Class description. I didn't write
this database or I would have done this differently. And I can't change the
orginal tables, because of that.

I have gone around this and figure that I need a SQL statement in my query
to make it work. However, I am lousy with SQL statements and can't figure it
out. Can someone help?

Thanks.
 
A

Amy Blankenship

Use ClassID, because the ClassID is unique for the Division/ClassName
combination. Well, that's the best practice way to do it.

To do it without changing the table structure you have now, Join on both the
class and the division. You should probably change the relationship for the
whole database, but you can also change it on just that query.

HTH;

Amy
 
M

margaret

I'm not sure how to do what you say. If I create a relationship between the
entries table using division and class, the query won't run. Says that the
relationship is too ambigious.

Can I do a make table, and create a table that puts the class and division
into one field? Then I could do a make table from the entry table that
creates that same field? Would that make sense?

You're right on using the ClassID, however, since I don't have ownership of
the program, I can't change the tables that exist, just add new ones in a
linking database.
 
A

Amy Blankenship

What about rather than using a join, do this:

SELECT Class.Description Table2.OtherStuff FROM Class, Table2 WHERE
Class.Class=Table2.Class AND Class.Division=Table2.Division

Not sure how that would play in the QBE, but should work in SQL view/

HTH;

Amy
 
Top