Hi All,

I understand how to group data when it's transferred to Excel as pivot-
table. However I was wondering if it's possible to group before the
data is returned to excel (i.e. from the wizard directly up-front).

The problem is that I'm trying to query, externally via ODBC, in a
pivot-table to simply group the number of cases in our incident system
by Month/Year. I know I could do this in Access as well, however this
isn't standard on our client build so need a way to do this in Excel.
However the number of records is now exceeding 70,000.

As I only want month/year and a count of the cases by 'Date Created'
field I was wondering if you can do this directly within the wizard so
Excel can return/display this successfully within the pivot-table?

If not, does anyone know how to only return this 'summed' view of data
within a MS Query so automatically I'd only get the Month/Year and
count of Date Created field?

Thanks in advance, Al.

I am a little lost here. If your pivot table is hooked directly back to your
ODBC data source then you are no longer bound to 65k records. The pivot cache
is only bound by memory. I have done pivot talbles with more than a million
records.

--
HTH...

Jim Thomlinson


" wrote:

Hi All,

I understand how to group data when it's transferred to Excel as pivot-
table. However I was wondering if it's possible to group before the
data is returned to excel (i.e. from the wizard directly up-front).

The problem is that I'm trying to query, externally via ODBC, in a
pivot-table to simply group the number of cases in our incident system
by Month/Year. I know I could do this in Access as well, however this
isn't standard on our client build so need a way to do this in Excel.
However the number of records is now exceeding 70,000.

As I only want month/year and a count of the cases by 'Date Created'
field I was wondering if you can do this directly within the wizard so
Excel can return/display this successfully within the pivot-table?

If not, does anyone know how to only return this 'summed' view of data
within a MS Query so automatically I'd only get the Month/Year and
count of Date Created field?

Thanks in advance, Al.

Al,

Just to clarify, you are returning rows from an external data source to XL,
and then you have a pivot table based on those rows?

What is your external data source? MS Access? SQL Server? Other?

I don't think it is possible to do what you are asking by using the Query
Wizard, but if you can open MS Query, copy your SQL statement, and paste it
here, I *MIGHT* be able to come up with a SQL statement that will do what
you are asking.

Just let me know what fields/columns you want to group by, which ones you
want to count, which ones to sum, etc...

HTH,

Conan




wrote in message
...
Hi All,

I understand how to group data when it's transferred to Excel as pivot-
table. However I was wondering if it's possible to group before the
data is returned to excel (i.e. from the wizard directly up-front).

The problem is that I'm trying to query, externally via ODBC, in a
pivot-table to simply group the number of cases in our incident system
by Month/Year. I know I could do this in Access as well, however this
isn't standard on our client build so need a way to do this in Excel.
However the number of records is now exceeding 70,000.

As I only want month/year and a count of the cases by 'Date Created'
field I was wondering if you can do this directly within the wizard so
Excel can return/display this successfully within the pivot-table?

If not, does anyone know how to only return this 'summed' view of data
within a MS Query so automatically I'd only get the Month/Year and
count of Date Created field?

Thanks in advance, Al.

Hi Conan,

Appreciate your help. Query is below:
SELECT "Request"."Case ID", "Request"."Create Date"
FROM "Request" "Request"

This query is 'auto' created based on manually just selecting the
fields within MS Query. Thanks for any help you can give.

Al.

On 19 Feb, 20:43, "Conan Kelly"
wrote:
Al,

Just to clarify, you are returning rows from an external data source to XL,
and then you have a pivot table based on those rows?

What is your external data source? *MS Access? *SQL Server? *Other?

I don't think it is possible to do what you are asking by using the Query
Wizard, but if you can open MS Query, copy your SQL statement, and paste it
here, I *MIGHT* be able to come up with a SQL statement that will do what
you are asking.

Just let me know what fields/columns you want to group by, which ones you
want to count, which ones to sum, etc...

HTH,

Conan

wrote in message

...



Hi All,

I understand how to group data when it's transferred to Excel as pivot-
table. *However I was wondering if it's possible to group before the
data is returned to excel (i.e. from the wizard directly up-front).

The problem is that I'm trying to query, externally via ODBC, in a
pivot-table to simply group the number of cases in our incident system
by Month/Year. *I know I could do this in Access as well, however this
isn't standard on our client build so need a way to do this in Excel.
However the number of records is now exceeding 70,000.

As I only want month/year and a count of the cases by 'Date Created'
field I was wondering if you can do this directly within the wizard so
Excel can return/display this successfully within the pivot-table?

If not, does anyone know how to only return this 'summed' view of data
within a MS Query so automatically I'd only get the Month/Year and
count of Date Created field?

Thanks in advance, Al.- Hide quoted text -

- Show quoted text -

Al,

In your original post you mentioned "the number of records is now exceeding
70,000". Are you returning records to XL and then creating a pivot table on
those records?

If that is the case, you have an unnecessary step in there. You do not need
to return records to XL in this case. Cut out the middle-man. Create your
pivot table on an external source. When you do that, it will take you
through the exact same process as if you were creating an external query.

But I will adjust your SQL statement so that it MIGHT work with MS Access
(you didn't tell me which DB system you are working with). Adjusted SQL
Statements are below.




In the following query, a new field is calculated. This new field is
'MBDate' (Month Begin Date). The MBDate is set to the first day of the
month of "Create Date" for each record. Then this query is "Grouped By"
this MBDate and counts the "Case ID" field...in other words, one record is
returned for each MBDate with a count of Case ID's that have the same MBDate
(counts all records in the same month):

SELECT dateserial(year("Request"."Create Date"),month("Request"."Create
Date"),1) AS 'MBDate', Count("Request"."Case ID")
FROM "Request" "Request"
GROUP BY dateserial(year("Request"."Create Date"),month("Request"."Create
Date"),1)


The following query is the exact same as the previous one except, I used the
last day of the month (MEDate--Month End Date) instead of MBDate:

SELECT dateserial(year("Request"."Create Date"),month("Request"."Create
Date")+1,0) AS 'MEDate', Count("Request"."Case ID")
FROM "Request" "Request"
GROUP BY dateserial(year("Request"."Create Date"),month("Request"."Create
Date")+1,0)

In the results, you can change the format of the date cells to "mmm yyyy"
(or "mm-yyyy", "yyyy-mm", "mmm-yyyy", "mmmm yyyy", etc....what ever suits
your needs) so that it will display just the month and year.

I did test these in an external DB query to Access and it did work, although
my syntax was slightly different. I hope these will work for you.

HTH,

Conan







wrote in message
...
Hi Conan,

Appreciate your help. Query is below:
SELECT "Request"."Case ID", "Request"."Create Date"
FROM "Request" "Request"

This query is 'auto' created based on manually just selecting the
fields within MS Query. Thanks for any help you can give.

Al.

On 19 Feb, 20:43, "Conan Kelly"
wrote:
Al,

Just to clarify, you are returning rows from an external data source to
XL,
and then you have a pivot table based on those rows?

What is your external data source? MS Access? SQL Server? Other?

I don't think it is possible to do what you are asking by using the Query
Wizard, but if you can open MS Query, copy your SQL statement, and paste
it
here, I *MIGHT* be able to come up with a SQL statement that will do what
you are asking.

Just let me know what fields/columns you want to group by, which ones you
want to count, which ones to sum, etc...

HTH,

Conan

wrote in message

...



Hi All,

I understand how to group data when it's transferred to Excel as pivot-
table. However I was wondering if it's possible to group before the
data is returned to excel (i.e. from the wizard directly up-front).

The problem is that I'm trying to query, externally via ODBC, in a
pivot-table to simply group the number of cases in our incident system
by Month/Year. I know I could do this in Access as well, however this
isn't standard on our client build so need a way to do this in Excel.
However the number of records is now exceeding 70,000.

As I only want month/year and a count of the cases by 'Date Created'
field I was wondering if you can do this directly within the wizard so
Excel can return/display this successfully within the pivot-table?

If not, does anyone know how to only return this 'summed' view of data
within a MS Query so automatically I'd only get the Month/Year and
count of Date Created field?

Thanks in advance, Al.- Hide quoted text -

- Show quoted text -