Selecting Two Records of the Same Field 2

[BoulderBum]

Let's say I have a table person containing the fields eye_color and hair_color. Let's also say in another table, colors, I have the fields color_name and rgb_value.

If color_name is related to hair_color and eye_color, is there a way to retrieve two cooresponding RGB values for storage in the same Recordset?

 

[Nitibob]

I think what you need here is an alias table. If you want to access the same table twice - then you rename it as another name.

If you are doing this in the query grid, then add the table colors to the query twice - linking them to the relevant fields on person .

Let me know if you are writing it in SQL....and i will try and give an example.

Hope this helps

 

[lespaul]

Hey Colin,
try this:

SELECT EYE_COLOR, HAIR_COLOR, EC.RGP_VALUE, HC.RGP_VALUE FROM PERSON
INNER JOIN COLORS EC ON PERSON.EYE_COLOR = EC.COLOR_NAME
INNER JOIN COLORS HC ON PERSON.HAIR_COLOR = HC.COLOR_NAME

The syntax may be a little off for access, but you should get the picture.

HTH

ps - at my daughter's birthday party last Saturday she wanted the climbing wall available for use, so Paul got it all tightened up and I actually got to climb again! I just did one quick run to the top (and the next day my shoulder was kinda tweaked), but I CLIMBED AGAIN!!!!

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[BoulderBum]

So the above SQL creates two aliases for colors named HC and EC. Is that correct?

Here's what I did:

SELECT bg_color, fg_color, EC.rgb_value, HC.rgb_value
FROM ( preferences
INNER JOIN colors AS EC ON preferences.fg_color = EC.color_name )
INNER JOIN colors AS HC ON preferences.bg_color = HC.color_name

Where the 'AS' keyword is optional, but the parenthesis were not.

Leslie,

Nice! You'll be ripping down Enchanted Tower in no time! I've only been bouldering once since Flag. :-(