If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab query problems
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! |
#2
|
|||
|
|||
Crosstab query problems
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" wrote: 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! |
#3
|
|||
|
|||
Crosstab query problems
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" wrote: 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" wrote: 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! |
#4
|
|||
|
|||
Crosstab query problems
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] ... -- Build a little, test a little. "Cathleen" wrote: 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! |
#5
|
|||
|
|||
Crosstab query problems
I am in error.
-- Build a little, test a little. "Cathleen" wrote: 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! |
#6
|
|||
|
|||
Crosstab query problems
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" wrote: 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" wrote: 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" wrote: 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! |
#7
|
|||
|
|||
Crosstab query problems
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! "Duane Hookom" wrote: 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 |
#8
|
|||
|
|||
Crosstab query problems
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,...? -- Duane Hookom Microsoft Access MVP "Cathleen" wrote: 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! "Duane Hookom" wrote: 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 |
#9
|
|||
|
|||
Crosstab query problems
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! "Duane Hookom" wrote: 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,...? -- Duane Hookom Microsoft Access MVP "Cathleen" wrote: 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! "Duane Hookom" wrote: 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 |
#10
|
|||
|
|||
Crosstab query problems
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; "Cathleen" wrote: 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! "Duane Hookom" wrote: 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,...? -- Duane Hookom Microsoft Access MVP "Cathleen" wrote: 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! "Duane Hookom" wrote: 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 |
|
Thread Tools | |
Display Modes | |
|
|