0

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?

Ryan
  • 3
  • 1

1 Answers1

0

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)

skilbjo
  • 510
  • 1
  • 7
  • 20
  • Thanks, John. The thing is that the Individuals have no relationship to the Organizations. The two tables are separate lists of my "referral sources" but I choose to list them in separate tables because they require different data. What I'm hoping to do is include a "referrer" field in a third "call log" table that is populated by all referral sources from the two separate tables. – Ryan Oct 26 '14 at 20:43
  • Hmm, ok, so your objective is not data retrieval from relational data, but to try to assign individuals to organizations (eg no relationship between John and StackOverflow in the first two tables, but in the third table you are now trying to assign John to StackOverflow)? – skilbjo Oct 27 '14 at 00:13
  • I may not be explaining the structure clearly. Let me get more specific: say I have two entries in the table ‘Individuals’: John Smith, Mary Jones; and one entry in the table ‘Organizations’: Whole Life Clinic. Any one of these three could be a referral source to me, so in my ‘Call Log’ table, I’d like a ‘Referrer’ field, within which when I click on the drop down field, all three are listed: John Smith, Mary Jones, and Whole Life Clinic. The field should lookup entries from both tables and be populated without any conditions or relationship between the two. Thank you! – Ryan Oct 27 '14 at 13:51
  • Ah, I see. If you just want to stack the tables on top of each other, what you're looking for is a `union`. Here's the pseudocode: `select Individuals.Name from Individuals union select Organizations.OrganizationsName from Organizations` Sorry for the formatting, can't format answers well in the comments :( – skilbjo Oct 27 '14 at 17:41
  • You can mark my answer as accepted then... :) Trying to get enough reputation so I can upvote answers in the future :) – skilbjo Oct 27 '14 at 22:35