By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,637 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,637 IT Pros & Developers. It's quick & easy.

Should I use a crosstab for this?

P: n/a
I am trying to get the totals of a table by client by type of income.

This query will get what I am looking for with each unique combination
as a row:

SELECT Sales.Client, BillCode.Type, Sum(Sales.Amount)
FROM Invoice_Details INNER JOIN BillCode ON Sales.BillCode =
BillCode.id
GROUP BY Client, Type;

However, what I would like to get is a report that has clients as rows
and the total sales amount for each bill code as columns.

I have tried to do a crosstab but the wizard confuses me more than
helps me.

If this in fact a good time for a crosstab, please show me what it
would look like.

If not, please tell me how to attack this.

I can get it to work with subqueries but the more fields I have, the
more complicated it gets to work with.

Also, can an access table be updated by a crosstab?

Thanks.

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"ne****@yahoo.com" <New Guy> wrote in message
news:5e********************************@4ax.com...
I am trying to get the totals of a table by client by type of income.

This query will get what I am looking for with each unique combination
as a row:

SELECT Sales.Client, BillCode.Type, Sum(Sales.Amount)
FROM Invoice_Details INNER JOIN BillCode ON Sales.BillCode =
BillCode.id
GROUP BY Client, Type;

However, what I would like to get is a report that has clients as rows
and the total sales amount for each bill code as columns.

I have tried to do a crosstab but the wizard confuses me more than
helps me.

If this in fact a good time for a crosstab, please show me what it
would look like.

If not, please tell me how to attack this.

I can get it to work with subqueries but the more fields I have, the
more complicated it gets to work with.

Also, can an access table be updated by a crosstab?

Thanks.


If I understand correctly....

Use a crosstab query. Go straight to Design View (skip the wizard).
Use the following entries in the grid:

Field - Client
Total - GroupBy
Crosstab - RowHeading

Field - Type
Total - GroupBy
Crosstab - ColumnHeading

Field - Amount
Total - Sum
Crosstab - Value

Good Luck,
Fred Zuckerman

Nov 13 '05 #2

P: n/a
On Fri, 12 Aug 2005 18:58:19 GMT, "Fred Zuckerman"
<Zu********@sbcglobal.net> wrote:
Use a crosstab query. Go straight to Design View (skip the wizard).
Use the following entries in the grid:

Field - Client
Total - GroupBy
Crosstab - RowHeading

Field - Type
Total - GroupBy
Crosstab - ColumnHeading

Field - Amount
Total - Sum
Crosstab - Value

Good Luck,
Fred Zuckerman


Thank you. That was certainly easy.
Nov 13 '05 #3

P: n/a
On Fri, 12 Aug 2005 18:58:19 GMT, "Fred Zuckerman"
<Zu********@sbcglobal.net> wrote:
If I understand correctly....

Use a crosstab query. Go straight to Design View (skip the wizard).
Use the following entries in the grid:

Field - Client
Total - GroupBy
Crosstab - RowHeading

Field - Type
Total - GroupBy
Crosstab - ColumnHeading

Field - Amount
Total - Sum
Crosstab - Value

Good Luck,
Fred Zuckerman


Is it possible to update another table with the results of a crosstab
query?

Thanks.
Nov 13 '05 #4

P: n/a
On Fri, 12 Aug 2005 19:50:58 GMT, "ne****@yahoo.com" <New Guy> wrote:
On Fri, 12 Aug 2005 18:58:19 GMT, "Fred Zuckerman"
<Zu********@sbcglobal.net> wrote:
If I understand correctly....

Use a crosstab query. Go straight to Design View (skip the wizard).
Use the following entries in the grid:

Field - Client
Total - GroupBy
Crosstab - RowHeading

Field - Type
Total - GroupBy
Crosstab - ColumnHeading

Field - Amount
Total - Sum
Crosstab - Value

Good Luck,
Fred Zuckerman

Is it possible to create a table with the results of a crosstab
query?

What would the syntax be?

Thanks.

Nov 13 '05 #5

P: n/a
On Fri, 12 Aug 2005 18:58:19 GMT, "Fred Zuckerman"
<Zu********@sbcglobal.net> wrote:

On Fri, 12 Aug 2005 21:13:29 GMT, New Guy <ne****@yahoo.com> wrote:
On Fri, 12 Aug 2005 19:50:58 GMT, "ne****@yahoo.com" <New Guy> wrote:
On Fri, 12 Aug 2005 18:58:19 GMT, "Fred Zuckerman"
<Zu********@sbcglobal.net> wrote:
If I understand correctly....

Use a crosstab query. Go straight to Design View (skip the wizard).
Use the following entries in the grid:

Field - Client
Total - GroupBy
Crosstab - RowHeading

Field - Type
Total - GroupBy
Crosstab - ColumnHeading

Field - Amount
Total - Sum
Crosstab - Value

Good Luck,
Fred Zuckerman

Is it possible to create a table with the results of a crosstab
query?

What would the syntax be?

Thanks.

After looking around with Google for a while, I found out that you can
change the Query type from Crosstab to Make Table after getting the
crosstab working properly.

However, it did not do what I was trying to get. It gave me a row for
each combination of Client and Type with the sum for each combination.
Multiple rows for each client.

What I would like to get from this is one row for each Client, with a
column for each Type, with the Sum(Amount) for each type in the
columns.

Perhaps this is not what a crosstab does? How can I accomplish this?
Subquery?

Sorry if I did not explain this correctly the first time.

Thanks.
Nov 13 '05 #6

P: n/a
On Fri, 12 Aug 2005 22:54:12 GMT, New Guy <ne****@yahoo.com> wrote:
After looking around with Google for a while, I found out that you can
change the Query type from Crosstab to Make Table after getting the
crosstab working properly.

However, it did not do what I was trying to get. It gave me a row for
each combination of Client and Type with the sum for each combination.
Multiple rows for each client.

What I would like to get from this is one row for each Client, with a
column for each Type, with the Sum(Amount) for each type in the
columns.

Perhaps this is not what a crosstab does? How can I accomplish this?
Subquery?

Sorry if I did not explain this correctly the first time.

Thanks.


It occurred to me to run a Crosstab on the table I created with the
original crosstab. The second crosstab got me what I wanted?

Is there a simpler way?

Thank you.
Nov 13 '05 #7

P: n/a
"New Guy" <ne****@yahoo.com> wrote in message
news:mb********************************@4ax.com...
On Fri, 12 Aug 2005 22:54:12 GMT, New Guy <ne****@yahoo.com> wrote:
After looking around with Google for a while, I found out that you can
change the Query type from Crosstab to Make Table after getting the
crosstab working properly.

However, it did not do what I was trying to get. It gave me a row for
each combination of Client and Type with the sum for each combination.
Multiple rows for each client.

What I would like to get from this is one row for each Client, with a
column for each Type, with the Sum(Amount) for each type in the
columns.

Perhaps this is not what a crosstab does? How can I accomplish this?
Subquery?

Sorry if I did not explain this correctly the first time.

Thanks.


It occurred to me to run a Crosstab on the table I created with the
original crosstab. The second crosstab got me what I wanted?

Is there a simpler way?

Thank you.


Well, you leave the crosstab query the way you had it (with the desired
results). Then create another query. Make the 2nd query a Make Table query
and use the Crosstab query as the source. Thus whenever you execute the 2nd
query, the 1st query will run automatically because it is the source, and
your new table will be created.
Fred Zuckerman
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.