crosstab query questions

[Slez via AccessMonster.com]

I have just created my first ever crosstab query and have a couple of
questions.

1. It seems as though any fields in a crosstab query that don't have a
selection made in the "Crosstab:" row in the design view won't show up...
although it appears they are used to retrieve the proper data. Is there a
way to view other fields? They aren't available when you go to the "Unhide
Columns" list. As an example: One of my Group By fields is ProjectID, but I
would also like to view ProjectName when I run the query. It exists in
design view, but does not display in the results. I just want to view this
so it's easier to verify the query is performing properly.

2. Can I then pull the crosstab query data into another query? Can I pull
data from 2 queries into another query? Ultimately I'm building a query to
be used as a record source for a fairly complex report, and I want to do most
of the calculation in the underlying queries. A very vague question I know,
but as long as the subquery(s) have a common field this should work, right?

Thanks in advance!
I appreciate any advice!

 

[Guest]

Post the SQL for your query.

 

[Guest]

If you want to display a field in a column set it as a Row Heading. A query
can generally be created from multiple tables, queries, or a combination.

 

[Slez via AccessMonster.com]

I tried adding my crosstab query to my source query, qryStatClientMain, along
with my tables named tblProjectClient, Project, and Bid. The query returns
alot of repeat data, but there doesn't seem to be a set pattern to why it
returns what it does. I posted my SQL below for review. My question #2 in
my original post was if a crosstab query could be pulled into another query.
Is this something that I can't do? Thanks once again!

SELECT qryStatClientTotals.GCName, qryStatClientTotals.ProjectID, Project.
ProjectName, tblProjectClient.AwardGC, qryStatClientTotals.BidNumber, Bid.
BidType, Bid.BidDate, Bid.BidStatus, qryStatClientTotals.[Base Bid],
qryStatClientTotals.Alternate, qryStatClientTotals.Budget, Bid.SalesTaxAmount,
Bid.UseTaxAmount
FROM ((Project INNER JOIN tblProjectClient ON Project.ProjectID =
tblProjectClient.ProjectID) INNER JOIN Bid ON Project.ProjectID = Bid.
ProjectID) INNER JOIN qryStatClientTotals ON Project.ProjectID =
qryStatClientTotals.ProjectID
ORDER BY qryStatClientTotals.GCName, qryStatClientTotals.ProjectID,
qryStatClientTotals.BidNumber;

If you want to display a field in a column set it as a Row Heading. A query
can generally be created from multiple tables, queries, or a combination.

I have just created my first ever crosstab query and have a couple of
questions.

[quoted text clipped - 16 lines]

Thanks in advance!
I appreciate any advice!

 

[Guest]

I'm not exactly sure what you mean by "pulled into another query" however a
crosstab can be used in another query like any other select query. I would
expect you to have tried this which might have prompted this question. If so,
what issue did you find?
--
Duane Hookom
Microsoft Access MVP

I tried adding my crosstab query to my source query, qryStatClientMain, along
with my tables named tblProjectClient, Project, and Bid. The query returns
alot of repeat data, but there doesn't seem to be a set pattern to why it
returns what it does. I posted my SQL below for review. My question #2 in
my original post was if a crosstab query could be pulled into another query.
Is this something that I can't do? Thanks once again!

SELECT qryStatClientTotals.GCName, qryStatClientTotals.ProjectID, Project.
ProjectName, tblProjectClient.AwardGC, qryStatClientTotals.BidNumber, Bid.
BidType, Bid.BidDate, Bid.BidStatus, qryStatClientTotals.[Base Bid],
qryStatClientTotals.Alternate, qryStatClientTotals.Budget, Bid.SalesTaxAmount,
Bid.UseTaxAmount
FROM ((Project INNER JOIN tblProjectClient ON Project.ProjectID =
tblProjectClient.ProjectID) INNER JOIN Bid ON Project.ProjectID = Bid.
ProjectID) INNER JOIN qryStatClientTotals ON Project.ProjectID =
qryStatClientTotals.ProjectID
ORDER BY qryStatClientTotals.GCName, qryStatClientTotals.ProjectID,
qryStatClientTotals.BidNumber;

If you want to display a field in a column set it as a Row Heading. A query
can generally be created from multiple tables, queries, or a combination.

I have just created my first ever crosstab query and have a couple of
questions.

[quoted text clipped - 16 lines]

Thanks in advance!
I appreciate any advice!

 

[Slez via AccessMonster.com]

The issue I have is as stated in my previous post. It duplicates alot of
records. I posted my SQL in the previous post. It doesn't seem like the
Join is working properly.

I have since created another crosstab query which gathers the correct data,
but there again...when I try to add another query to it, it results in
duplicate data. There again it seems like I have something incorrect with my
Join. I was hoping to resolve one prior to posting questions about another.

Thanks
Slez

I'm not exactly sure what you mean by "pulled into another query" however a
crosstab can be used in another query like any other select query. I would
expect you to have tried this which might have prompted this question. If so,
what issue did you find?

I tried adding my crosstab query to my source query, qryStatClientMain, along
with my tables named tblProjectClient, Project, and Bid. The query returns

[quoted text clipped - 22 lines]

 

[Guest]

I expect your joins include 1 to many and the same 1 to a different many. We
can't see your data so it is very difficult to determine why you might be
getting duplicates.

--
Duane Hookom
Microsoft Access MVP

The issue I have is as stated in my previous post. It duplicates alot of
records. I posted my SQL in the previous post. It doesn't seem like the
Join is working properly.

I have since created another crosstab query which gathers the correct data,
but there again...when I try to add another query to it, it results in
duplicate data. There again it seems like I have something incorrect with my
Join. I was hoping to resolve one prior to posting questions about another.

Thanks
Slez

I'm not exactly sure what you mean by "pulled into another query" however a
crosstab can be used in another query like any other select query. I would
expect you to have tried this which might have prompted this question. If so,
what issue did you find?

I tried adding my crosstab query to my source query, qryStatClientMain, along
with my tables named tblProjectClient, Project, and Bid. The query returns

[quoted text clipped - 22 lines]

Thanks in advance!
I appreciate any advice!