How Do I Get a Field Associated With a Foreign Key

  • Thread starter Thread starter Geoff Robinson
  • Start date Start date
G

Geoff Robinson

Hi,

In a Query I need to grab a list of foreign keys of a particular field
and then show what another field associated with it is.

So Table A Has the Foreign Key. I want to take each Foreign Key (can
be duplicates) and basicly say "SELECT FieldName FROM TableB WHERE ID
= Foreign Key." Is there a nice way of doing this in a query? Using a
programming language I would just create a recordset and then loop
through that doing more sql queries. That option is not available to
me.

I greatly appreciate any help in advance.

Geoff
 
Geoff

I'm not clear, from your description, what you are trying to end up with --
you have described a "how".

If I wanted to see the [Field] value in TableB that corresponded to the
TableB key that was stored in TableA (i.e., a foreign key in A), I'd use a
query.

I'd join TableA and B on that field (primary key field in TableB to foreign
key field in TableA), then select the "value" field from TableB for display.
Since it sounds like there's a relationship between A and B, I'd probably
also select other fields in A for display. Then run the query!

Good luck

Jeff Boyce
<Access MVP>
 
Thanks for your help. I'm going to use your insight and then see if
it I can get this to work. I know it is easier than I am making it
but I'm having problems getting my mind around this for some reason.

In the meantime, let me clarify my scenario.

TableA contains a bunch of foreign keys, which are all the primary
keys of TableB. The same key can appear multiple times in TableA. In
TableB has a key and a value. I want to take all the foreign keys in
TableA and give a list of the appropriate value associated with it in
TableB.

So if A has:
1
1
2

And B has:
1 Bob
2 Geoff

I want the query to produce

Bob
Bob
Geoff

I know this should be easy. I just have to work it out. I've only
done simple sql queries so far and I need to stretch out.

thanks again,
Geoff
 
Geoff

If you use the Access query design window, it will be just as easy as you
just described -- no SQL needed. (or, if you need SQL statements, build the
query in the design window, then change the view to <SQL> to see what Access
uses to describe the more graphical representation.)

Query design mode:

Add TableA and TableB. (If you've established the relationship, the join
will already show)

Join on the related field.

Select the primary key from TableA (that way, you'll get one listing for
every TableA record).

Select the "value" field from TableB.

With these two fields selected (added to the grid), run the query.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top