Hello,

I'll say right off the bat, that here is a good chance i won't be able to
explain my problem well. It would be much easier if I could just show someone
my database.

Anyway, I have two tables: tblQuadrat_Data and tblSpecies_Data.

tblQuadrat_Data has a primary key: Quadrat_ID which is related to the
Quadrat_ID field in tblSpecies_Data.

tblQuadrat_Data also has a field called Transect_No.


I have a crostab query that tabulates data from tblSpecies_Data table, but
it also grabs information form the tblQuadrat_Data table, specifically
Transect_No.

I would like to make a report that
A)For each transect_No, shows data from the tblQuadrat_Data table and
B) still within that trasact_No group (indented), shows data from the
crosstab query

I have can do this successfully if I include the Quadrat_ID in the crosstab
query (which is how the crosstab query is related to the tblQuadrat_Data
table).

The problem:

If I include the Quadrat_ID in the crosstab query, the query is no longer
tabular/crosstab. It breaks the records up into many more rows. This is
because there is more than one Quadrat_ID for each record in the crosstab
query.

But, I need the Quadrat_ID in the crosstab query in order to link to the
tblQuadrat_Data table.

What I think i should do, but can't:

Link the Transact_No in the crosstab query to the Transact_No in the
tblQuadrat_Data table. I can't do this because:
A) There is already a relationship between the two tables (Quadrat_ID to
Quadrat_ID)
B) Transact_No is not the primary key in tblQuadrat_Data.

Again, what I would like to do:

a report that:
A)For each transect_No, shows data from the tblQuadrat_Data table and
B) still within that trasact_No group (indented), shows data from the
crosstab query

I doubt anyone can understand all this jibberish, but I thought it was worth
a shot.

-Ben

Post the SQL of your crosstab and what other fields you want from
tblQuadrat_Data.

--
Build a little, test a little.


"Ben8765" wrote:

> Hello,
>
> I'll say right off the bat, that here is a good chance i won't be able to
> explain my problem well. It would be much easier if I could just show someone
> my database.
>
> Anyway, I have two tables: tblQuadrat_Data and tblSpecies_Data.
>
> tblQuadrat_Data has a primary key: Quadrat_ID which is related to the
> Quadrat_ID field in tblSpecies_Data.
>
> tblQuadrat_Data also has a field called Transect_No.
>
>
> I have a crostab query that tabulates data from tblSpecies_Data table, but
> it also grabs information form the tblQuadrat_Data table, specifically
> Transect_No.
>
> I would like to make a report that
> A)For each transect_No, shows data from the tblQuadrat_Data table and
> B) still within that trasact_No group (indented), shows data from the
> crosstab query
>
> I have can do this successfully if I include the Quadrat_ID in the crosstab
> query (which is how the crosstab query is related to the tblQuadrat_Data
> table).
>
> The problem:
>
> If I include the Quadrat_ID in the crosstab query, the query is no longer
> tabular/crosstab. It breaks the records up into many more rows. This is
> because there is more than one Quadrat_ID for each record in the crosstab
> query.
>
> But, I need the Quadrat_ID in the crosstab query in order to link to the
> tblQuadrat_Data table.
>
> What I think i should do, but can't:
>
> Link the Transact_No in the crosstab query to the Transact_No in the
> tblQuadrat_Data table. I can't do this because:
> A) There is already a relationship between the two tables (Quadrat_ID to
> Quadrat_ID)
> B) Transact_No is not the primary key in tblQuadrat_Data.
>
> Again, what I would like to do:
>
> a report that:
> A)For each transect_No, shows data from the tblQuadrat_Data table and
> B) still within that trasact_No group (indented), shows data from the
> crosstab query
>
> I doubt anyone can understand all this jibberish, but I thought it was worth
> a shot.
>
> -Ben
>