Crosstab query problems

  • Thread starter Thread starter Cathleen
  • Start date Start date
C

Cathleen

I am attempting to create a crosstab query that would give me an output like
this:

event_id top_depth_zooplankton Species1 Species2
Species3
1 0 P. truncatum
K. longispina D. rosea

I assume I need a crosstab to achieve this from the following query (which
works well) that shows the three most abundant zooplankton species at each
depth for each "sampling event". This query's output looks like:
event_id top_depth_zooplankton density_zooplankton zooplankton_taxon

Here's the SQL:
SELECT Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton, ZoopLookup.zooplankton_taxon
FROM ZoopLookup INNER JOIN Zooplankton ON ZoopLookup.zooplookup_id =
Zooplankton.zooplookup_id
WHERE (((Zooplankton.density_zooplankton) In (Select Top 3
[density_zooplankton] FROM Zooplankton As Dupe WHERE Dupe.event_id =
Zooplankton.event_id AND Dupe.top_depth_zooplankton =
Zooplankton.top_depth_zooplankton)) AND
((Zooplankton.sample_type_zooplankton)="FP"))
ORDER BY Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton DESC;

Does anyone have any suggestions on how to get to the output I'd like? All
my attempts at basing a crosstab query on the above query have ended with an
error saying Microsoft Jet database engine can't recogize
Zooplankton.event_id (there are no parameters for this field).

Any help would be greatly appreciated.

Thank you!
 
Crosstab queries have issues resolving subqueries. You can use a DCount() in
your query to count the number of records to get 1, 2, or 3 for use as the
column heading.

There have been some other solutions posted here that use a table/query in a
self-join that aggregates and counts from one side of the join.
 
I've tried DCount() but I can't get past the first field (event_id) without
getting the Microsoft Jet Database Engine can't recognize... error. I've
noticed that most other people who have posted with that problem solved it by
defining parameters, but I don't understand why I would need parameters for
that field. Any ideas on what's going on?
Thanks again!

Duane Hookom said:
Crosstab queries have issues resolving subqueries. You can use a DCount() in
your query to count the number of records to get 1, 2, or 3 for use as the
column heading.

There have been some other solutions posted here that use a table/query in a
self-join that aggregates and counts from one side of the join.


--
Duane Hookom
Microsoft Access MVP


Cathleen said:
I am attempting to create a crosstab query that would give me an output like
this:

event_id top_depth_zooplankton Species1 Species2
Species3
1 0 P. truncatum
K. longispina D. rosea

I assume I need a crosstab to achieve this from the following query (which
works well) that shows the three most abundant zooplankton species at each
depth for each "sampling event". This query's output looks like:
event_id top_depth_zooplankton density_zooplankton zooplankton_taxon

Here's the SQL:
SELECT Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton, ZoopLookup.zooplankton_taxon
FROM ZoopLookup INNER JOIN Zooplankton ON ZoopLookup.zooplookup_id =
Zooplankton.zooplookup_id
WHERE (((Zooplankton.density_zooplankton) In (Select Top 3
[density_zooplankton] FROM Zooplankton As Dupe WHERE Dupe.event_id =
Zooplankton.event_id AND Dupe.top_depth_zooplankton =
Zooplankton.top_depth_zooplankton)) AND
((Zooplankton.sample_type_zooplankton)="FP"))
ORDER BY Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton DESC;

Does anyone have any suggestions on how to get to the output I'd like? All
my attempts at basing a crosstab query on the above query have ended with an
error saying Microsoft Jet database engine can't recogize
Zooplankton.event_id (there are no parameters for this field).

Any help would be greatly appreciated.

Thank you!
Click to expand...
Click to expand...
 
I think you have a typo ---
WHERE (((Zooplankton.density_zooplankton) In (Select Top 3
[density_zooplankton] ...
to be ---
WHERE (((Zooplankton.density_zooplankton) In (Select Top 3
[zooplankton].[density] ...
 
What is the SQL view of the query you tried with the DCount(). Also, your
SELECT TOP 3 didn't have an order by so I wouldn't think it would have been
reliable.

--
Duane Hookom
Microsoft Access MVP


Cathleen said:
I've tried DCount() but I can't get past the first field (event_id) without
getting the Microsoft Jet Database Engine can't recognize... error. I've
noticed that most other people who have posted with that problem solved it by
defining parameters, but I don't understand why I would need parameters for
that field. Any ideas on what's going on?
Thanks again!

Duane Hookom said:
Crosstab queries have issues resolving subqueries. You can use a DCount() in
your query to count the number of records to get 1, 2, or 3 for use as the
column heading.

There have been some other solutions posted here that use a table/query in a
self-join that aggregates and counts from one side of the join.


--
Duane Hookom
Microsoft Access MVP


Cathleen said:
I am attempting to create a crosstab query that would give me an output like
this:

event_id top_depth_zooplankton Species1 Species2
Species3
1 0 P. truncatum
K. longispina D. rosea

I assume I need a crosstab to achieve this from the following query (which
works well) that shows the three most abundant zooplankton species at each
depth for each "sampling event". This query's output looks like:
event_id top_depth_zooplankton density_zooplankton zooplankton_taxon

Here's the SQL:
SELECT Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton, ZoopLookup.zooplankton_taxon
FROM ZoopLookup INNER JOIN Zooplankton ON ZoopLookup.zooplookup_id =
Zooplankton.zooplookup_id
WHERE (((Zooplankton.density_zooplankton) In (Select Top 3
[density_zooplankton] FROM Zooplankton As Dupe WHERE Dupe.event_id =
Zooplankton.event_id AND Dupe.top_depth_zooplankton =
Zooplankton.top_depth_zooplankton)) AND
((Zooplankton.sample_type_zooplankton)="FP"))
ORDER BY Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton DESC;

Does anyone have any suggestions on how to get to the output I'd like? All
my attempts at basing a crosstab query on the above query have ended with an
error saying Microsoft Jet database engine can't recogize
Zooplankton.event_id (there are no parameters for this field).

Any help would be greatly appreciated.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
As usual, my problem is actually more complex than I originally thought. My
top 3 query has two issues that will make it so that I don't always have
three records for each "sampling event" and depth. First, there are sometimes
ties leading to up to six values for each group. Secondly, sometimes there
are only one or two species present in the sample.

Since my goal is to link these data to other parameters based on depth, I
think I could achieve my result with three separate queries (one for the
maximum density in each group, one for second highest, etc.). So, this brings
me to a couple of questions:
1. How can I specify that the query return the second-highest density value
in each group?
2. If there is a tie (several species with the same density) can I combine
those species names into one field?

Sorry to change directions here, but thanks for all of your help!
 
The first step is to create a ranking query that derives the numbers 1, 2,
3,... You will need to figure out how you want to break ties if you have
them.

Can you provide a basic query that lists all the values needed as well as
the field(s) you want to use to create the 1, 2, 3,...?
 
Here's my attempt at a query ranking based on density_zooplankton. But, it is
ranking everything in the Zooplankton table. Is there a way to get it to rank
only what is specified in the criteria of the main query (WHERE
Zooplankton.sample_type_zooplankton = "FP")?

Secondly, if I get this ranking query to work, I'd like to make a query that
selects all of the rank 1 and combines the species names (zooplankton_taxon)
of any ties into one field?

SELECT Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton(SELECT Count(*) FROM Zooplankton AS Dupe
WHERE Dupe.event_id = Zooplankton.event_id AND dupe.top_depth_zooplankton =
Zooplankton.top_depth_zooplankton AND dupe.density_zooplankton >=
Zooplankton.density_zooplankton) AS DensityRank,
ZoopLookup.zooplankton_taxon, Zooplankton.density_zooplankton
FROM ZoopLookup INNER JOIN Zooplankton ON ZoopLookup.zooplookup_id =
Zooplankton.zooplookup_id
WHERE (((Zooplankton.sample_type_zooplankton)="FP"))
ORDER BY Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton;


Thanks again - I appreciate all of your help!
 
I figured out my first question. Here's the new SQL, working as I'd like it
to. But, any query I try to run based on this one is very, very slow. My next
step is to create a query that selects all of the rank 1 and combines the
species names (zooplankton_taxon) of any ties into one field?

Also, any ideas why an ! keeps appearing between
Zooplankton.density_zooplankton and the subquery - this gives me an error? If
I delete it, all is well, but then it reappears again later.

Thanks again!

SELECT Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton (SELECT Count(*) FROM Zooplankton AS Dupe
WHERE Dupe.event_id = Zooplankton.event_id AND dupe.top_depth_zooplankton =
Zooplankton.top_depth_zooplankton AND dupe.density_zooplankton >=
Zooplankton.density_zooplankton AND Dupe.sample_type_zooplankton = "FP") AS
DensityRank, ZoopLookup.zooplankton_taxon, Zooplankton.density_zooplankton
FROM ZoopLookup INNER JOIN Zooplankton ON ZoopLookup.zooplookup_id =
Zooplankton.zooplookup_id
WHERE (((Zooplankton.sample_type_zooplankton)="FP"))
ORDER BY Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton;
 
Try this query which assumes Event_ID and top_depth_zooplankton and
density_zooplankton are all numeric fields.

SELECT Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton,
DCount("*","Zooplankton", "Event_ID =" & Event_ID & " AND
top_depth_zooplankton = " & top_depth_zooplankton & " AND
density_zooplankton >= " & density_zooplankton & " AND
Sample_type_zooplankton = 'FP'") AS DensityRank,
ZoopLookup.zooplankton_taxon, Zooplankton.density_zooplankton
FROM ZoopLookup INNER JOIN Zooplankton ON ZoopLookup.zooplookup_id =
Zooplankton.zooplookup_id
WHERE (((Zooplankton.sample_type_zooplankton)="FP"))
ORDER BY Zooplankton.event_id, Zooplankton.top_depth_zooplankton,
Zooplankton.density_zooplankton;
 
Perfect! That runs a lot faster than my attempt at it. I think I can handle
the queries based on this one now.

Thank you again!
 
Back
Top