asked on

MS Access Query question

I wish I could award more than 500 points but oh well. I also wish I could have come up with a better title.

In this example I have 3 main tables (state, nfl_team, nhl_team) and 2 join tables (one that joins nfl and state and another that joins nhl and state) that I am entering via a form. The values for the nfl and nhl teams are being entered via a subform within the form for the state table. These tables are linked by the state_id field.

These are the values that I entered in the state form.....

State      NFL Team      NHL Team
NY      Giants      Rangers
      Jets      Islanders
            Sabers
CA      49ers      Sharks
      Raiders      Kings
      Chargers      Ducks
DC      Redskins      Capitals
PA      Eagles      Flyers
      Steelers      Penguins

I want to create a query that displays all nfl and nhl teams where state = “DC” or “PA” (I could have picked NY and/or CA but didn’t feel like typing all the possible combinations). This is what I would expect to see.
PA      Eagles      Flyers
PA      Eagles      Penguins
PA      Steelers      Flyers
PA      Steelers      Penguins
DC      Redskins      Capitals

I was wondering if it was possible in within the query mode within MS Access 2003 to have that same recordset to display like the following below.
PA      Eagles      Steelers      Flyers      Penguins
DC      Redskins            Capitals

In which the number of columns is dynamic (since for any given state you could have x amount of teams per sport)

Is anything like this possible so that I can export the query to Excel?

Note - If I were using NY or CA there would be more columns due to the notion that those states have 3+ teams for each of the two sports (for the query that I want to have),

Unfortunately I don't have a web browser and access on the same machine so I can show you the db diagram. No matter, since my real case involves even more tables but I didn't want to scare you guys.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

plummet

Hi,

This may be possible using crosstab query/queries but I have a question first, why do you have separate join tables? If you have 2 tables, each with 2 columns, State and Team, then you can link these together using the state field. If you want more information on the State then you can have another table.

I'm sure you have a good reason for the structure you are talking about, but I can't help asking the question!

shuittny

ASKER

@plummet - in reality my issue was more complex than just a few tables and a few fields (altogether its 8 tables and many fields) but I opted to use a smaller subset for my example.