Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004

    Unanswered: 'flatten' a query

    Hi all,
    Ok so i have 2 tables. driver and team, each team has a maximum of 3 drivers.

    driver has fields driverid and drivername, with driver as the primary key
    team has teamid and teamname, with teamname as the primary key
    I then have a lookup table, which assigns each driverid to a teamname.

    I'm trying to make a query with the format:
    teamname teamdriverid1 teamdrivername1 teamdriverid2 teamdrivername2

    I'm presuming i need lookup functions for this but need help on it.

    Thx in advance,

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    why is there a lookup table?

    Add teamid to the driver table, then use a crosstab query.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Chicago, IL
    You only need a lookup table if you have a many-to-many relationship. Answer these two questions:

    1. Can a driver be on more then one team?
    2. Can a team have more than one driver?

    Number two has already been answered, but if you answer Yes to number one then you have a many-to-many relationship. If that is the case then you need the third table. Otherwise you only need two tables, one for drivers (with a TeamID field) and one for Teams.

  4. #4
    Join Date
    Jul 2004
    Thanx for the replys, ok i got rid of the lookup table, i just thought it was needed to make this query in that format. I'm doing this for a friend who's importing the database into mysql, which apparently supports very little in access. When trying to make the query i can get a new record for each driver of the team, i.e.
    team1 driver1
    team1 driver2
    team2 driver3

    But he specifically says he needs all drivers in a single row.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts