Help - need Cross Tab to Sum AND Count

[amanda]

Hope someone can help me with this - I've been staring at it stupidly
for hours now, convinced there must be an easy way to achieve the
results I want:

I have a very large table recording every sale made by one of 90
salespeople (2 years of data), and the date the sale was made. I need
a report that shows a list of the salespeople down the left hand side
and then for each month the total number of sales and the total value
of sales. I can do a cross tab that works beautifully for each of
these that will group the records by month but I just can't seem to
find a way of combining the information into a useful table.

I know in an Excel Pivot table I would be able to have more than one
row for each salesperson so that I could show the value and the count
for each line but I can't seem to do this in Access. I tried a make
table query and then an append query but that jumbled up the
information into nonsense as I wasn't able to sort it by salesperson
and month.

Am I missing something really obvious here or has anyone got any
bright ideas? I need this to be a simple process that admin staff with
no knowledge of access can run.

Any help enormously appreciated,

Amanda Leslie

__________________

am****@alt1.demon.co.uk

[Bob Alston]

am****@alt1.demon.co.uk wrote:

Hope someone can help me with this - I've been staring at it stupidly
for hours now, convinced there must be an easy way to achieve the
results I want:

I have a very large table recording every sale made by one of 90
salespeople (2 years of data), and the date the sale was made. I need
a report that shows a list of the salespeople down the left hand side
and then for each month the total number of sales and the total value
of sales. I can do a cross tab that works beautifully for each of
these that will group the records by month but I just can't seem to
find a way of combining the information into a useful table.

I know in an Excel Pivot table I would be able to have more than one
row for each salesperson so that I could show the value and the count
for each line but I can't seem to do this in Access. I tried a make
table query and then an append query but that jumbled up the
information into nonsense as I wasn't able to sort it by salesperson
and month.

Am I missing something really obvious here or has anyone got any
bright ideas? I need this to be a simple process that admin staff with
no knowledge of access can run.

Any help enormously appreciated,

Amanda Leslie

__________________

am****@alt1.demon.co.uk

While there are likely other ways to do this, what I have done is create
two crosstabs, identical except one counts and the other sums. Also, I
rename the data field that will be the column in each
(newname: [oldname] + "Count"

Then in another query i simply join the two fields together by whatever
fields form the rows.

That way all of the columns of the count cross tab have a name in the
format: xxxxxCount
and all the columns of the sum cross tab have a name in the format:
yyyyySum
Hope I have stated this clearly (I get in trouble here is I mis-state
things)

Bob

[amanda]

Thanks Bob, that's great - and does the job.

However, I'm not sure where to put:

(newname: [oldname] + "Count"

?

Can you advise further!

Many thanks

Amanda Leslie

On Wed, 12 Oct 2005 12:59:48 -0500, Bob Alston
<tu****************@cox.net> wrote:

am****@alt1.demon.co.uk wrote:

Hope someone can help me with this - I've been staring at it stupidly
for hours now, convinced there must be an easy way to achieve the
results I want:

I have a very large table recording every sale made by one of 90
salespeople (2 years of data), and the date the sale was made. I need
a report that shows a list of the salespeople down the left hand side
and then for each month the total number of sales and the total value
of sales. I can do a cross tab that works beautifully for each of
these that will group the records by month but I just can't seem to
find a way of combining the information into a useful table.

I know in an Excel Pivot table I would be able to have more than one
row for each salesperson so that I could show the value and the count
for each line but I can't seem to do this in Access. I tried a make
table query and then an append query but that jumbled up the
information into nonsense as I wasn't able to sort it by salesperson
and month.

Am I missing something really obvious here or has anyone got any
bright ideas? I need this to be a simple process that admin staff with
no knowledge of access can run.

Any help enormously appreciated,

Amanda Leslie

__________________

am****@alt1.demon.co.uk

While there are likely other ways to do this, what I have done is create
two crosstabs, identical except one counts and the other sums. Also, I
rename the data field that will be the column in each
(newname: [oldname] + "Count"

Then in another query i simply join the two fields together by whatever
fields form the rows.

That way all of the columns of the count cross tab have a name in the
format: xxxxxCount
and all the columns of the sum cross tab have a name in the format:
yyyyySum
Hope I have stated this clearly (I get in trouble here is I mis-state
things)

Bob

[Bob Alston]

am****@alt1.demon.co.uk wrote:

Thanks Bob, that's great - and does the job.

However, I'm not sure where to put:

(newname: [oldname] + "Count"

?

Can you advise further!

Many thanks

Amanda Leslie

On Wed, 12 Oct 2005 12:59:48 -0500, Bob Alston
<tu****************@cox.net> wrote:

am****@alt1.demon.co.uk wrote:

Hope someone can help me with this - I've been staring at it stupidly
for hours now, convinced there must be an easy way to achieve the
results I want:

I have a very large table recording every sale made by one of 90
salespeople (2 years of data), and the date the sale was made. I need
a report that shows a list of the salespeople down the left hand side
and then for each month the total number of sales and the total value
of sales. I can do a cross tab that works beautifully for each of
these that will group the records by month but I just can't seem to
find a way of combining the information into a useful table.

I know in an Excel Pivot table I would be able to have more than one
row for each salesperson so that I could show the value and the count
for each line but I can't seem to do this in Access. I tried a make
table query and then an append query but that jumbled up the
information into nonsense as I wasn't able to sort it by salesperson
and month.

Am I missing something really obvious here or has anyone got any
bright ideas? I need this to be a simple process that admin staff with
no knowledge of access can run.

Any help enormously appreciated,

Amanda Leslie

__________________

am****@alt1.demon.co.uk

While there are likely other ways to do this, what I have done is create
two crosstabs, identical except one counts and the other sums. Also, I
rename the data field that will be the column in each
(newname: [oldname] + "Count"

Then in another query i simply join the two fields together by whatever
fields form the rows.

That way all of the columns of the count cross tab have a name in the
format: xxxxxCount
and all the columns of the sum cross tab have a name in the format:
yyyyySum
Hope I have stated this clearly (I get in trouble here is I mis-state
things)

Bob

I use it to rename the field that I want to appear as columns.

Bob