Search code examples
multi-table

Performing a Multitable Query without Cartesian Product

Just learning MS Access 2013 and feeling stuck with how to retrieve the data I want using a multitable query. I have two tables: one stores a list of organizations and the other stores a list of individual names. In a third table, I have a lookup field that I'd like to be populated by all of these organizations and individuals. When I use the Query Design tool to try to make this happen, the only thing I can seem to produce is a cartesian product. Any suggestions?

Solution

  • You definitely don't want the cartesian product.

    While I'm not too familiar with MS Access (SQL is more my realm), MS Access is just a front-end to a database, and it actually turns what you design with the Query Design tool into a SQL dialect called Access SQL.

    Here's what you want to do: link the Organizations table to the Individuals with the join criteria being Organizations's primary key on the Individuals's foreign key. So if your tables look like this...

    Individuals table:

    Name | OrganizationId
____ | ______________
John | 1

    Organizations table:

    OrganizationId | OrganizationName
______________ | ______________
1              | StackOverflow

    You want to have design your query so that Individual.OrganizationId (foreign key) is joined to Organization.OrganizationId (primary key)