Selecting from a Table

[Karl]

Hello,

I have two tables. The master table us a long list of plant data collected
for various sites. There are over 500 different sites and each site may have
several records of different plant species. On a separate table I have a list
of 175 sites that I am concerned with. Therefore I want to select all the
records from the master table that correspond with a site number from the
second table. I tried creating a relatioship between the two using site id,
and adjusting the join types in the query but for some reason its not working
in the query. I end up with more records then I have in my master list.

Any advice how I can pull the selected records I want out of the master
table?

 

[KARL DEWEY]

Therefore I want to select all the records from the master table that
correspond with a site number from the second table.
SELECT [Site Name], [Location], [master table].*
FROM [Site Table] INNER JOIN [master table] ON [Site Table].[site id] =
[master table].[site id];

I took some liberty with field names.

 

[Armen Stein]

Hello,

I have two tables. The master table us a long list of plant data collected
for various sites. There are over 500 different sites and each site may have
several records of different plant species. On a separate table I have a list
of 175 sites that I am concerned with. Therefore I want to select all the
records from the master table that correspond with a site number from the
second table. I tried creating a relatioship between the two using site id,
and adjusting the join types in the query but for some reason its not working
in the query. I end up with more records then I have in my master list.

Any advice how I can pull the selected records I want out of the master
table?

Sounds like what you need is an Inner join between the two tables. An
Inner join has no arrowheads when viewed in the query designer. An
Inner join includes a record only when there is a match from both
tables.

However, if you are getting more records than you expect, it is
because there are duplicate values of the field you are joining with.
For example, do you have duplicate sites in the table of 175?

If you need to suppress these duplicates, one easy way is to first
create a Totals query of just unique sites from your second table.
Then join your master table to that query instead.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com