UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Display Field From Related Table In Query Result, Access 2016    
 
   
bmacwilliams
post Oct 22 2019, 01:07 PM
Post#1



Posts: 12
Joined: 11-October 05
From: Salt Lake City, UT


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.

--------------------
Bruce MacWilliams, Ph.D.
Shriners Hospitals for Children
Go to the top of the page
 
arnelgp
post Oct 22 2019, 01:22 PM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


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;

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
theDBguy
post Oct 22 2019, 01:22 PM
Post#3


Access Wiki and Forums Moderator
Posts: 76,490
Joined: 19-June 07
From: SunnySandyEggo


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!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Oct 22 2019, 01:29 PM
Post#4


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
bmacwilliams
post Oct 22 2019, 04:06 PM
Post#5



Posts: 12
Joined: 11-October 05
From: Salt Lake City, UT


I think I did a poor job of describing the situation. Please find test database with query attached.
Attached File(s)
Attached File  Database1.zip ( 18.96K )Number of downloads: 3
 

--------------------
Bruce MacWilliams, Ph.D.
Shriners Hospitals for Children
Go to the top of the page
 
theDBguy
post Oct 22 2019, 04:21 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,490
Joined: 19-June 07
From: SunnySandyEggo


Take a look at Query1.

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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bmacwilliams
post Oct 22 2019, 05:00 PM
Post#7



Posts: 12
Joined: 11-October 05
From: Salt Lake City, UT


Thank you!

--------------------
Bruce MacWilliams, Ph.D.
Shriners Hospitals for Children
Go to the top of the page
 
theDBguy
post Oct 22 2019, 07:33 PM
Post#8


Access Wiki and Forums Moderator
Posts: 76,490
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mike60smart
post Oct 27 2019, 09:29 AM
Post#9


UtterAccess VIP
Posts: 13,459
Joined: 6-June 05
From: Dunbar,Scotland


Hi Bruce

This is how your tables should be normalised

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

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2019 - 04:46 PM