Should I use a crosstab for this?

[newguy]

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.

[Fred Zuckerman]

"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

[newguy]

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.

[newguy]

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.

[New Guy]

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.

[New Guy]

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.

[New Guy]

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.

[Fred Zuckerman]

"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