473,727 Members | 2,034 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Query to lookup values from table into multiple fields

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

Aug 25 '06 #1
3 30286
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

Aug 25 '06 #2
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 ***
Aug 25 '06 #3
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

Aug 26 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1766
by: JC Mugs | last post by:
Help needed for project-Access 2002(office xp) PROBLEM: Figuring out how to lookup a record and DDate field in Table1 - Take that DDate-field data from record looked up and assign it to Date field in a new record on a form using Table2. BASIC Table1 Fields: DID -Primary KEY DDate --Information I need to move to new form
2
2683
by: Betrock | last post by:
This is probably very simple, but I just can't see my way thru it..... Short version: keyed values(numeric)in a lookup table are stored in a main table. They are displayed as text values - the keyed values description. I need a parameter query to return a range of these values. The parameter input won't accept the 'text' version. You can do it with 'Find' because you can select 'as formatted'. But I need a range. Please, any...
3
579
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to make a query that selects from a table as desribed below .. I have a table (Volunteer) that has a member field (memnumber) and a number of fields that are headed in various categories and are yes/no formated
3
4800
by: Janross | last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's network, it takes at least 5 minutes to run. Obviously not workable! I know where the problem is, I just don't know how to fix it. The query calls a function, and I assume it gets slow because the function runs on every record. So--is there a way to rewrite the function so it's quicker?...
1
3255
by: Robert | last post by:
I am trying to create a db for service providers by county. I'm relatively new to db programming, but I have done quite a bit of programming ranging from the old basic days up to doing some programming in the HotDocs software. I've kind of accomplished my goal in access, but I'm not quite there yet and figure I've really screwed something up. The db consists of 5 tables I believe. T1 is the main contact info, with an autonumber ID...
4
4617
by: jon f kaminsky | last post by:
Hi- I've seen this problem discussed a jillion times but I cannot seem to implement any advice that makes it work. I am porting a large project from VB6 to .NET. The issue is using the combo box bound to a table as a lookup, drawing values from another table to populate the available selections. This all worked fine in VB6. I have distilled the problem down to a simple form drawing data from the Northwind database for a representative...
3
1771
by: silvastein | last post by:
It would be great if someone can help me with this. It seems like it should either be easy (so far, not!) or maybe it isn't possible. Here goes: Table B has TextField1 that looks up values from a single field in Table A. When I create a form for filling in Table B, TextField1 appears as a long list of options from Table A. If I were to make the field really tall, it would show multiple rows of data from Table A. I would prefer...
2
2393
by: Fendi Baba | last post by:
I created a person table with various fields such as Suffix, Salutation, etc, Some of these fields may not be mandatory for example suffix. In the actual table itself, I only have a field for suffix ID where 1=Phd, 2= MD. To display all of these to the user, I created a form with an underlying query. The problem I am encountering is this, when we have an empty field, for example where ID="", the query returns nothing. How do i work around...
2
5770
by: Jody | last post by:
Hi I've been working on a database which basically incorporates 3 tables to describe say a widget which is either sold or leased. I have the Widget table which stores the information related to the widget itself. I then have a WidgetSale table which stores only information related to the sale of the widget (advertised price, headline, copy, date of sale
0
8891
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9260
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9185
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9120
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6703
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6011
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4786
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.