SQL Query to lookup values from table into multiple fields

[mkjets]

I have worked for hours on trying to find a solution and have not
figured it out. I am working in Access 2003. I need to create a query
that takes values from 1 table and displays them in multiple fields. I
don't have the option of changing the structure of the existing tables
because I am importing them from a separate data source on a regular
basis. I also need to use a query instead of a form because I then
need to be able to export the data easily. If anyone has any ideas it
would be greatly appreciated!

I have the following tables:

Table 1 - Sales
Products | Option A | Option B | Option C
--------------------------------------------------------------------
Item 1 | 200 | 400 | 300
Item 2 | 100 | 300 | 500
Item 3 | 200 | 300 | 100
Table 2 - Equip
ID | NAME
-------------------
100 | Door
200 | Bell
300 | Crank
400 | Speaker
500 | Tire

I would like to make a query to output the following:

Products | Option A | Option B | Option C
--------------------------------------------------------------------
Item 1 | Bell | Speaker | Crank
Item 2 | Door | Crank | Tire
Item 3 | Bell | Crank | Door

I have tried JOIN, but I cannot get it to work across multiple fields.

Thank you so much for any ideas!!

-Matt

[Bob Quintal]

mk****@bellsout h.net wrote in
news:11******** **************@ p79g2000cwp.goo glegroups.com:

I have worked for hours on trying to find a solution and have
not figured it out. I am working in Access 2003. I need to
create a query that takes values from 1 table and displays
them in multiple fields. I don't have the option of changing
the structure of the existing tables because I am importing
them from a separate data source on a regular basis. I also
need to use a query instead of a form because I then need to
be able to export the data easily. If anyone has any ideas it
would be greatly appreciated!

I have the following tables:

Table 1 - Sales
Products | Option A | Option B | Option C
--------------------------------------------------------------
Item 1 | 200 | 400 | 300
Item 2 | 100 | 300 | 500
Item 3 | 200 | 300 | 100
Table 2 - Equip
ID | NAME
-------------------
100 | Door
200 | Bell
300 | Crank
400 | Speaker
500 | Tire

I would like to make a query to output the following:

Products | Option A | Option B | Option C
--------------------------------------------------------------
Item 1 | Bell | Speaker | Crank
Item 2 | Door | Crank | Tire
Item 3 | Bell | Crank | Door

I have tried JOIN, but I cannot get it to work across multiple
fields.

Thank you so much for any ideas!!

-Matt

The join is the right idea, but as you say it won't work across
multiple fields IN THE SAME TABLE.

Solution: just put the EQUIP table into the query grid 3 times.
Access will append a _1 to the second instance of the table and
_2 for the third. You can change the Alias of the table names in
the field list properties to something more meaningful.

In your case, I'd label them Option1, Option2 and Option3. Then
join your three fields to the corresponding lookup table, select
the fields you want and test it..

Better solution: redesign the Sales table
Product | Option | EquipID
item 1 | A | 200
item 1 | B | 400
Item 2 | A | 100
item 2 | C | 500
Item 2 | B | 300
item 1 | C | 300

then join the two tables, and get your final output using a
crosstab query. You are not forced to redesign the table and query
if you add a fourth option using this method.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Rich P]

Hi Matt,

Based on the scenario you present, you can take your sales table and
join it 3 times to the Equipment table

something like this

sales join equip on optionA = ID

then just add the equip table to the query again and

sales join equip1 on optionB = ID

then add the equip table again to the query and

sales join equip2 on optionC = ID

Then drag the product field from the sales table into the grid, then
drag the name field from equip, then drag the Name field from equip1,
then drag the name field from equip2 into the grid. Now run the query
and it will display the result you requested.

Note: this is not the best way to do your query. The correct way to do
this would be in the sales table have 3 columns Product, ID, Option.
You list each item for each option and include the ID for each option
(this is the normalized way to do it). So Item1 would be listed 3 times
in the table - once for each option, then item2 would be listed 3 times
for each option, and item3 listed 3 times for each option. Then (the
not fancy sql way) you create a join query where you join Sales to Equip
on ID = ID. Then create a cross tab query on that query. That will
give you the same results but will be way more flexible and not require
you to keep adding the equip table to the query each time you get a new
option.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[mkjets]

Bob and Rich,

Thank you so much for your help! The solution worked like a charm! It
makes so much sense now that I see it. I have posted the SQL in case
anyone is interested in the future:

SELECT Sales.Products, Equip.NAME AS [Option A], Equip_1.NAME AS
[Option B], Equip_2.NAME AS [Option C]
FROM ((Sales INNER JOIN Equip ON Sales.[Option A] = Equip.ID)
INNER JOIN Equip AS Equip_1 ON Sales.[Option B] = Equip_1.ID)
INNER JOIN Equip AS Equip_2 ON Sales.[Option C] = Equip_2.ID;

I agree this is not the best way to form the tables, but unfortunately
I am working with external data sources that are already formatted this
way. I need to import them into access and this approach will be the
easiest to repeat on a regular basis every time I receive the updated
database. I will keep the better solution in mind for the future!
Thank you so much for the help, I greatly appreciate it!

- Matt