Multiple Self Joins - Is there a better way?

I need to pull data out of an access database that is stored in 8 different rows (no I did not design this table and I cannot change the format). The first problem is there is no unique key, you have to concatenate 5 columns to identify it uniquely. I need to take the bulk of the data from one of the 8 rows, but also pull the different values from the price column from each of these 8 rows to display on a single line in the query. If anyone can help me, I'd be eternally grateful.
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I've been unable to locate an exact example but have continued to play and here's what I have for you to try (It may not be any faster since I don't have that much data in my test case):

create a primary key on the 5 columns in your table
create 8 queries (1 for each year)
ex./ for year 1:  
   SELECT, Q.type, Q.exp,, Q.area, Q.yr, Q.hour FROM Q WHERE (((Q.yr)=1));

Go to relationships and set up a 5 column relationship from query year1 to each of the other 7 years queries.

Then create the final query as (this will paste very ugly, but should be straight forward in the query wizard):
SELECT, year1.type, year1.exp,, year1.area, year1.hour, year2.hour, year3.hour, year4.hour, year5.hour, year6.hour, year7.hour, year8.hour
FROM ((((((year1 INNER JOIN year2 ON (year1.area = year2.area) AND ( = AND (year1.exp = year2.exp) AND (year1.type = year2.type) AND ( = INNER JOIN year3 ON (year1.area = year3.area) AND ( = AND (year1.exp = year3.exp) AND (year1.type = year3.type) AND ( = INNER JOIN year4 ON (year1.area = year4.area) AND ( = AND (year1.exp = year4.exp) AND (year1.type = year4.type) AND ( = INNER JOIN year5 ON (year1.area = year5.area) AND ( = AND (year1.exp = year5.exp) AND (year1.type = year5.type) AND ( = INNER JOIN year6 ON (year1.area = year6.area) AND ( = AND (year1.exp = year6.exp) AND (year1.type = year6.type) AND ( = INNER JOIN year7 ON (year1.area = year7.area) AND ( = AND (year1.exp = year7.exp) AND (year1.type = year7.type) AND ( = INNER JOIN year8 ON (year1.area = year8.area) AND ( = AND (year1.exp = year8.exp) AND (year1.type = year8.type) AND ( =;

slightwv (䄆 Netminder) Commented:
Can you provide a little more detail?  such as a table definition, some test data and expected results?
Hi KNawrocki,

Perhaps some subqueries would meet your requirements?  Something like this?:

  , col2
  , col3
  , col4
  , col5
  , Price
  , (SELECT Price FROM yourTable WHERE [Conditions here that meet uniquely identify where the first price comes from]) AS price2
  , (SELECT Price FROM yourTable WHERE [Conditions here that meet uniquely identify where the second price comes from]) AS price3
  , (SELECT Price FROM yourTable WHERE [Conditions here that meet uniquely identify where the third price comes from]) AS price4
  , [etc for the other four columns]
FROM yourTable
  [Conditions here that meet uniquely identify where most of you data is coming from]
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

KNawrockiAuthor Commented:
mcmonap -- Actually I have tried that and it works (slowly) for up to 6 self joins -- but then times out for the 8 joins.

slightwv --

Confidential data, I cannot give exacts, but here is an example of data types
ID Number      Type Name Exp      Edu      Area      YR      Hour
12345      Manual      2      1      Local      1      2.95
12345      Manual      2      1      Local      2      3.20
12345      Manual      2      1      Local      3      3.30
12345      Manual      2      1      Local      4      3.40
12345      Manual      2      1      Local      5      3.50
12345      Manual      2      1      Local      6      3.60
12345      Manual      2      1      Local      7      3.70
12345      Manual      2      1      Local      8      3.80
12345      Manual      2      1      Distant      1      3.00
12345      Manual      2      1      Distant      2      3.30
12345      Manual      2      1      Distant      3      3.40
12345      Manual      2      1      Distant      4      3.50
12345      Manual      2      1      Distant      5      3.60
12345      Manual      2      1      Distant      6      3.70
12345      Manual      2      1      Distant      7      3.80
12345      Manual      2      1      Distant      8      3.90
12345      Manual      5      2      Local      1      4.00
12345      Manual      5      2      Local      2      4.10
12345      Manual      5      2      Local      3      4.20
12345      Manual      5      2      Local      4      4.30
12345      Manual      5      2      Local      5      4.40
12345      Manual      5      2      Local      6      4.50
12345      Manual      5      2      Local      7      4.60
12345      Manual      5      2      Local      8      4.70
12345      Manual      5      2      Distant      1      4.00
12345      Manual      5      2      Distant      2      4.10
12345      Manual      5      2      Distant      3      4.20
12345      Manual      5      2      Distant      4      4.30
12345      Manual      5      2      Distant      5      4.40
12345      Manual      5      2      Distant      6      4.50
12345      Manual      5      2      Distant      7      4.60
12345      Manual      5      2      Distant      8      4.70

Now this data repeats for many additional ID_Numbers

I need to Disply
ID Number, Type Name, Exp, Edu, Area, Hour YR1, Hour YR2, Hour YR3, Hour YR4, Hour YR5, Hour YR6, Hour YR7, Hour YR8
All in one row
Here's an example of how to do this in UDB.  Not sure if the same functionality exists in Access.  It assumes combination of col1, col2, col3 are unique as you stated above for your five columns.  Give it a try!!

select col1,col2,
 max(case when col3 = '1' then col4 else null end) as year1,
 max(case when col3 = '2' then col4 else null end) as year2,
 max(case when col3 = '3' then col4 else null end) as year3
from test2
group by col1,col2

Here was my test table:

A        AA       1        11      
A        AA       2        22      
A        AA       3        33      
B        BB       1        110      
B        BB       2        220      
B        BB       3        330      

And the results of the query:

A   AA   11    22     33
B   BB   110   220   330      

slightwv (䄆 Netminder) Commented:
Now that I see what you are after, I don't think you can get there from here in access.  This is a common question in the DB world.  A quick search (even just in this site) for "rows to columns" will yield many hits.  You just need to locate an example for Access.

You say there is no unique key on the table.  Why can't you add one or at least index the 1st 5 columns together?  This should make the self joins faster.  If this does not make performance acceptable, your only other option may be to write some VBA code to derive the results.

Also, how many rows are in this table (you may be pushing the limits of access)?  

I'll keep looking and if I find anything, I'll post back.

KNawrockiAuthor Commented:
I first believed that the limits of access may be the problem. I have three similar tables, I started with the smallest that contains 3456 rows, the other two both have over 200,000 rows.

The one time I got this to work with 6 self joins, it took over two hours on the smallest table.

If I were to convert this to MS SQL, does anyone know of a SELECT that would work, or am I looking at importing to SQL and redesigning the tables as relational?
Unless you have an index on the 5 unique columns, you're probably scanning through the entire table for each "iteration" of the query.  If you don't have an index, I suggest creating one (as a previous poster mentioned) and retrying it on the small table.  Then, a query like the one from mcmonap above should work fine.  Make sure you create it as a multiple-field index.
slightwv (䄆 Netminder) Commented:
200,000 rows is starting to push the limits of Access (MY OPINION!!!  There are those die-hards that will disagree)

in MS SQL and with the proper indexes as mentioned, the query from mcmonap or the original self-joins that you were talking about should perform OK.

For my own curosity:  Did you try out my last suggestion?  I'm wondering if runs any faster.........
KNawrockiAuthor Commented:
slightwv --
Just got to finish putting that one together (got held up in meetings). Yes it worked. Now I've got to take this and publish using asp. I've always written my asp selects simply and outside of access. I guess another challenge is at hand!

Thanks - you've been a great help.
slightwv (䄆 Netminder) Commented:
For future reference:  At times it is better to use CODE to compile results.  Since you are using asp (I'm guessing you mean the Microsoft Web platform):  It might be better to perform a simple select and loop through the dataset to compile the report.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.