Querying from two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Let me describe my hang-up. In the first table (table 1), I have a unique
code (CONTROL #), each one (i.e. row) corresponding to a particular sampling
event. In table 2, I have the same set of CONTROL #s, but they are not unique
in this table since each row describes an individual organism caught in each
sample (i.e., CONTROL #s are often repeated). Unfortunately, I have a YEAR
variable in table 2 that I want attached to the unique control numbers in
table 1. When I query in design view, I link the two tables with the CONTROL
#. I then ask for CONTROL # from table 1 and YEAR from table 2, but the
query gives me all the CONTROL #s from table 2 (and its associated YEAR), not
the unique CONTROL #s (from table 1) I was hoping for. I've right clicked on
the link to change the join properties, but none of the options produce the
desired outcome. I think this is really a question of a 1 to many relational
database. I'd appreciate any ideas. Thanks.
 
Yes, it is a one-to-many relationship. You have only to get Access to
enforce it.

Make a backup of your database into another folder or with a new name.

Open whichever database you want to use for test. Open the
Relationships and find the two tables under consideration. Draw a
line from CONTROL # (I'd get rid of " #" in the field name) in the
first/main table to CONTROL # in the 2nd table. Click on the join
line and enforce referential integrity and enable cascading deletes.
You may get a message that executing your instructions will leave
orphan records. That means you have records in the 2nd table whose
CONTROL # value doesn't match any record in the 1st table. You'll
have work to do...

That should resolve the current issue. Now you can create the query
you wish.

By the way, the most usual way to present the data in a one-to-many
relationship is with the Form/Subform paradigm. The Help for that is
pretty good.

There's another great newsgroups to lurk and learn:

microsoft.public.access.tablesdesign

and a super valuable web site to explore:

www.mvps.org/access

Post back as new issues arise.

HTH
 
Back
Top