repeated data in some columns

[Guest]

Hi there,

I'm kind of new to Access and use it only as point-and-click (I have no idea
about SQL!). I'm trying to create a query, but it isn't producing the right
results.

The data describes sample collections from another of locations, many dates
and a couple of different projects. I'm trying to limit the query to one
location and one project. The data for the query comes from a number of
tables.

I seem to be doing ok with limiting the location, finding different dates,
and only looking at one project. Those columns look fine. However, when I
look at the data (counts of different invertebrate taxa), I find that the
same data is repeated in each line. This data is from one real date. But what
happened to my other sample dates?

I have the feeling that I need to tick a box or something simple, but I just
can't work it out!

Thanks in advance for your help!

buggirl

 

[Guest]

How are you joining the tables in the query?
Post your SQL by opening the query in design view, click on menu VIEW - SQL
View. This opens another window. Highlight all, copy and paste in a post.

 

[Guest]

Thanks! What a quick response.

I've copied and pasted the SQL below. It's long and complicated because I
divided the invertebrate tables into broad taxonomic groupings (ie there's
one for Diptera, one for Coleoptera and so on).

Also, I took another look at my query results - there isn't just one data
line that is repeated, there are several. Each of these data lines are
repeated for each of the sample dates.

thanks again!

buggirl

SELECT [site descriptions].project, [site descriptions].[sample date], [site
descriptions].[sample month], [site descriptions].[sample id], [site
descriptions].location, [pool descriptions].[pool id], [invert sample
details].[sample type], Diptera.Chironomidae, Diptera.Ceratopogonidae,
Diptera.Simuliidae, Hemiptera.Corisella, Coleoptera.[Uvarus adult],
Ephemeroptera.Tricorythodes, Ephemeroptera.Callibaetis,
Trichoptera.Leptoceridae, Odonata.Gomphidae, [Oligochaetes and
microcrustacea].Copepoda INTO [Bosque del Apache summer 2007]
FROM [site descriptions] INNER JOIN [pool descriptions] ON [site
descriptions].[sample id] = [pool descriptions].[sample id], ((((((([invert
sample details] INNER JOIN Coleoptera ON ([invert sample details].[sample id]
= Coleoptera.[sample id]) AND ([invert sample details].[pool id] =
Coleoptera.[pool id]) AND ([invert sample details].[sample type] =
Coleoptera.[sample type])) INNER JOIN Diptera ON ([invert sample
details].[sample id] = Diptera.[sample id]) AND ([invert sample
details].[pool id] = Diptera.[pool id]) AND ([invert sample details].[sample
type] = Diptera.[sample type])) INNER JOIN Ephemeroptera ON ([invert sample
details].[sample id] = Ephemeroptera.[sample id]) AND ([invert sample
details].[pool id] = Ephemeroptera.[pool id]) AND ([invert sample
details].[sample type] = Ephemeroptera.[sample type])) INNER JOIN Hemiptera
ON ([invert sample details].[sample id] = Hemiptera.[sample id]) AND ([invert
sample details].[pool id] = Hemiptera.[pool id]) AND ([invert sample
details].[sample type] = Hemiptera.[sample type])) INNER JOIN [Nematodes and
terrestrials] ON ([invert sample details].[sample id] = [Nematodes and
terrestrials].[sample id]) AND ([invert sample details].[pool id] =
[Nematodes and terrestrials].[pool id]) AND ([invert sample details].[sample
type] = [Nematodes and terrestrials].[sample type])) INNER JOIN Odonata ON
([invert sample details].[sample id] = Odonata.[sample id]) AND ([invert
sample details].[pool id] = Odonata.[pool id]) AND ([invert sample
details].[sample type] = Odonata.[sample type])) INNER JOIN [Oligochaetes and
microcrustacea] ON ([invert sample details].[sample id] = [Oligochaetes and
microcrustacea].[sample id]) AND ([invert sample details].[pool id] =
[Oligochaetes and microcrustacea].[pool id]) AND ([invert sample
details].[sample type] = [Oligochaetes and microcrustacea].[sample type]))
INNER JOIN Trichoptera ON ([invert sample details].[sample id] =
Trichoptera.[sample id]) AND ([invert sample details].[pool id] =
Trichoptera.[pool id]) AND ([invert sample details].[sample type] =
Trichoptera.[sample type])
WHERE ((([site descriptions].project)="NSF - summer intensive") AND (([site
descriptions].location)="Bosque del Apache") AND (([invert sample
details].[sample type])="dipnet"));

 

[Guest]

For me to analyze it more than just a glane I would need to build all those
tables and populate with some data in the fileds that are joined.

I suggest the you make a copy and work on the copy in this way --
Open the query copy in design view, delete one table at a time, and run the
query. Then delete the next one until it runs correctly. That way you can
find what is giving you the problem.
You may have more than one to fix so do the same thing if after fixing what
you find wrong and it does not run right.

--
KARL DEWEY
Build a little - Test a little

Thanks! What a quick response.

I've copied and pasted the SQL below. It's long and complicated because I
divided the invertebrate tables into broad taxonomic groupings (ie there's
one for Diptera, one for Coleoptera and so on).

Also, I took another look at my query results - there isn't just one data
line that is repeated, there are several. Each of these data lines are
repeated for each of the sample dates.

thanks again!

buggirl

SELECT [site descriptions].project, [site descriptions].[sample date], [site
descriptions].[sample month], [site descriptions].[sample id], [site
descriptions].location, [pool descriptions].[pool id], [invert sample
details].[sample type], Diptera.Chironomidae, Diptera.Ceratopogonidae,
Diptera.Simuliidae, Hemiptera.Corisella, Coleoptera.[Uvarus adult],
Ephemeroptera.Tricorythodes, Ephemeroptera.Callibaetis,
Trichoptera.Leptoceridae, Odonata.Gomphidae, [Oligochaetes and
microcrustacea].Copepoda INTO [Bosque del Apache summer 2007]
FROM [site descriptions] INNER JOIN [pool descriptions] ON [site
descriptions].[sample id] = [pool descriptions].[sample id], ((((((([invert
sample details] INNER JOIN Coleoptera ON ([invert sample details].[sample id]
= Coleoptera.[sample id]) AND ([invert sample details].[pool id] =
Coleoptera.[pool id]) AND ([invert sample details].[sample type] =
Coleoptera.[sample type])) INNER JOIN Diptera ON ([invert sample
details].[sample id] = Diptera.[sample id]) AND ([invert sample
details].[pool id] = Diptera.[pool id]) AND ([invert sample details].[sample
type] = Diptera.[sample type])) INNER JOIN Ephemeroptera ON ([invert sample
details].[sample id] = Ephemeroptera.[sample id]) AND ([invert sample
details].[pool id] = Ephemeroptera.[pool id]) AND ([invert sample
details].[sample type] = Ephemeroptera.[sample type])) INNER JOIN Hemiptera
ON ([invert sample details].[sample id] = Hemiptera.[sample id]) AND ([invert
sample details].[pool id] = Hemiptera.[pool id]) AND ([invert sample
details].[sample type] = Hemiptera.[sample type])) INNER JOIN [Nematodes and
terrestrials] ON ([invert sample details].[sample id] = [Nematodes and
terrestrials].[sample id]) AND ([invert sample details].[pool id] =
[Nematodes and terrestrials].[pool id]) AND ([invert sample details].[sample
type] = [Nematodes and terrestrials].[sample type])) INNER JOIN Odonata ON
([invert sample details].[sample id] = Odonata.[sample id]) AND ([invert
sample details].[pool id] = Odonata.[pool id]) AND ([invert sample
details].[sample type] = Odonata.[sample type])) INNER JOIN [Oligochaetes and
microcrustacea] ON ([invert sample details].[sample id] = [Oligochaetes and
microcrustacea].[sample id]) AND ([invert sample details].[pool id] =
[Oligochaetes and microcrustacea].[pool id]) AND ([invert sample
details].[sample type] = [Oligochaetes and microcrustacea].[sample type]))
INNER JOIN Trichoptera ON ([invert sample details].[sample id] =
Trichoptera.[sample id]) AND ([invert sample details].[pool id] =
Trichoptera.[pool id]) AND ([invert sample details].[sample type] =
Trichoptera.[sample type])
WHERE ((([site descriptions].project)="NSF - summer intensive") AND (([site
descriptions].location)="Bosque del Apache") AND (([invert sample
details].[sample type])="dipnet"));

How are you joining the tables in the query?
Post your SQL by opening the query in design view, click on menu VIEW - SQL
View. This opens another window. Highlight all, copy and paste in a post.