Queries across 2 tables

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I'm not sure if this can be done with just a query, it may need a macro so
if I am in the wrong group, let me know.

I have 2 tables. I want to select a record in the 1st table and perform a
query on the second table based on values in that record. These are just
examples.

Table 1
Item - Number - Color

Table 2
Name - Number - Letter

I need the query to do a >= on the number. The tricky part is the 2nd one.
The search criteria changes based on the value, for example, if the color
was Red then I would need to search for "A" or "C" or "F" but if the color
was Blue I would need to search for "B" or "C" or "D" and so on.
 
A macro isn't going to help here. You can do it with a query IF you have a
third table to link the data between the other tables. This table could be
named something like ColorLetter and look like below:

Color Letter
Red A
Red C
Red F
Blue B
Blue C
Blue D

Then the basic join SQL would look something like:
SELECT [Table 1].*,
[Table 2].*
FROM ([Table 1] INNER JOIN ColorLetter
ON [Table 1].Color = ColorLetter.Color) INNER JOIN [Table 2]
ON ColorLetter.Letter = [Table 2].Letter;
 
Thanks Jerry,

That really helped me in the right direction, after a little massaging I
came up with this statement (real code, no more color letter stuff).

SELECT CharacterProfile.*
FROM (CharacterProfile INNER JOIN XREF ON CharacterProfile.Class=XREF.Job)
INNER JOIN Items ON XREF.Type=Items.Material
WHERE Material="Cloth" and Level>=50;

This seems to work for what I am doing.

Now, the second part of this is that in Table Items, I would like to select
a record and have the "material" and "level" arguments of the Where
statement automatically populated. Basically I want to see which records in
CharacterProfile are a match to the selected record in Items based on
material and level. Any thoughts on this one?

Al


Jerry Whittle said:
A macro isn't going to help here. You can do it with a query IF you have a
third table to link the data between the other tables. This table could be
named something like ColorLetter and look like below:

Color Letter
Red A
Red C
Red F
Blue B
Blue C
Blue D

Then the basic join SQL would look something like:
SELECT [Table 1].*,
[Table 2].*
FROM ([Table 1] INNER JOIN ColorLetter
ON [Table 1].Color = ColorLetter.Color) INNER JOIN [Table 2]
ON ColorLetter.Letter = [Table 2].Letter;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Al said:
I'm not sure if this can be done with just a query, it may need a macro so
if I am in the wrong group, let me know.

I have 2 tables. I want to select a record in the 1st table and perform a
query on the second table based on values in that record. These are just
examples.

Table 1
Item - Number - Color

Table 2
Name - Number - Letter

I need the query to do a >= on the number. The tricky part is the 2nd one.
The search criteria changes based on the value, for example, if the color
was Red then I would need to search for "A" or "C" or "F" but if the color
was Blue I would need to search for "B" or "C" or "D" and so on.
Click to expand...
Click to expand...
 
Back
Top