[X]My Assistant

[bmacwilliams]

This should be straight forward stuff, but maybe I'm just not searching the right keywords. I've tried reading about subqueries, linked tables/queries, etc.

Say I have a table of values for a subject and there are multiple fields which store an index value which is then linked to another table that explains the index.
e.g.
table.field1 = 1
table.field2 = 3
table.field3 = 5
etc.

and there is an associate table, let's say they are colors where there are two fields: color.id and color.name
color.id color.name
1 blue
2 red
3 green
4 yellow
5 black

color.id has a 1 to 1 relationship with each of the table.field values.

when I create a query to return the values of table.field1, table.field2, table.field3 I want to return the color.name field, not the color.id field. I can't seem to get past just getting the values from table.field. In my form fields I use dlookup() to do this exact thing, but I'm struggling to get that to work in a query.

Any help pointing me in the right direction is appreciated.

[arnelgp]

that is unconventional way of using a table.
to normalize your first table, you need only two fields.

ID (autonumber)
ColorID (long) Foreign key to colorTable.



'======
for your current table structure, you need to link each fields to a Separate colors table:

SELECT A.name, B.name, C.name
FROM ((table INNER JOIN color AS A ON table.field1 = A.id)
INNER JOIN color AS B ON table.field2 = B.id)
INNER JOIN color AS C ON table.field3 = C.id;

[theDBguy]

Hi. Have you tried something like:

CODE

SELECT color1.name as field1, color2.name as field2, color3.name as field3
FROM table
INNER JOIN color AS color1
ON table.field1=color1.id
INNER JOIN color AS color2
ON table.field2=color2.id
INNER JOIN color AS color3
ON table.field3=color3.id

(untested)
Edit: Oops, too slow, but at least it's good to see I'm on the right track (just wrong syntax). Cheers!

[GroverParkGeorge]

Arnel and theDBGuy have provided approaches to deal with the current, unnormalized tables.

However, the more effective long-term solution is to correct the normalization problem, as Arnel described it.

Here's a link to some background information on correcting the problem of "spreadsheet style" tables.

[bmacwilliams]

I think I did a poor job of describing the situation. Please find test database with query attached.

Attached File(s)

 Database1.zip ( 18.96K )Number of downloads: 3

 

[theDBguy]

Take a look at Query1.

Attached File(s)

 Database1.zip ( 21.24K )Number of downloads: 3

 

[bmacwilliams]

Thank you!

[theDBguy]

Hi. You're welcome! However, George does have a point. Please reconsider his advice. Good luck!

[mike60smart]

Hi Bruce

This is how your tables should be normalised

 Database1.zip ( 29.6K )Number of downloads: 2