PivotTables

[Ian Mac]

All,
I decided it's about time I looked at using these PivotTable thingies.
I've ran into problem already.

I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

The Fields/Headings I'm importing are:

{"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


An explaination of the fields I need to look at currently are:

calldate: is the Date AND Time stamp, combined, of the record
operator: User NT login
DMCYN: Always Y or N

I've been through the PivotTable wizard.

I selected the range tblCallLog, which is a Dynamic Named Range.
I placed:
calldate in Row
operator in Column
and Count of DMCYN

Finish.

Now for my question.
the calldate is in the following format

24/04/2002 18:17:02

therefore every seccond in accounted for.

how do I Group them so the Row part reads

12:00
13:00
14:00

and has the count of TIME>=12:00 and <13:00

Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

this is not very well explained but I can answer any questions as required.

All your help will be greatly received.

Many thanks

[Chris Davison]

On 2002-04-30 06:08, Ian Mac wrote:
All,
I decided it's about time I looked at using these PivotTable thingies.
I've ran into problem already.

I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

The Fields/Headings I'm importing are:

{"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


An explaination of the fields I need to look at currently are:

calldate: is the Date AND Time stamp, combined, of the record
operator: User NT login
DMCYN: Always Y or N

I've been through the PivotTable wizard.

I selected the range tblCallLog, which is a Dynamic Named Range.
I placed:
calldate in Row
operator in Column
and Count of DMCYN

Finish.

Now for my question.
the calldate is in the following format

24/04/2002 18:17:02

therefore every seccond in accounted for.

how do I Group them so the Row part reads

12:00
13:00
14:00

and has the count of TIME>=12:00 and <13:00

Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

this is not very well explained but I can answer any questions as required.

All your help will be greatly received.

Many thanks

Hi Ian,

you can add "calculated fields" in your pivot tables which may help..... I've not used them much myself, so you'd proabbly be on the same learnign curve if you attempted to add some to determine the times

(I've seen your posts...you know your stuff !)

Alternatively, could you pop in an extra column in your data to strip the time range from the calldate and include this is the table ?

Similarly, another column in your data with the date stripped out from the call date would allow you an extra field with which to pop into the page field....

apologies if I'm stating the obvious !

(I usually stay well clear of any questions about dates and times, as they are not my fortes by *any* stretch of the imagination!)

[Chris Davison]

On 2002-04-30 06:08, Ian Mac wrote:
All,
I decided it's about time I looked at using these PivotTable thingies.
I've ran into problem already.

I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

The Fields/Headings I'm importing are:

{"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


An explaination of the fields I need to look at currently are:

calldate: is the Date AND Time stamp, combined, of the record
operator: User NT login
DMCYN: Always Y or N

I've been through the PivotTable wizard.

I selected the range tblCallLog, which is a Dynamic Named Range.
I placed:
calldate in Row
operator in Column
and Count of DMCYN

Finish.

Now for my question.
the calldate is in the following format

24/04/2002 18:17:02

therefore every seccond in accounted for.

how do I Group them so the Row part reads

12:00
13:00
14:00

and has the count of TIME>=12:00 and <13:00

Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

this is not very well explained but I can answer any questions as required.

All your help will be greatly received.

Many thanks

Hi Ian,

you can add "calculated fields" in your pivot tables which may help..... I've not used them much myself, so you'd proabbly be on the same learnign curve if you attempted to add some to determine the times

(I've seen your posts...you know your stuff !)

Alternatively, could you pop in an extra column in your data to strip the time range from the calldate and include this is the table ?

Similarly, another column in your data with the date stripped out from the call date would allow you an extra field with which to pop into the page field....

apologies if I'm stating the obvious !

(I usually stay well clear of any questions about dates and times, as they are not my fortes by *any* stretch of the imagination!)

[Jack in the UK]

Chis my best mate


tell me whats wrong with times and date, anyone would have thought you dont like them!!!

I would never ask you such a question ............... would i?



_________________
If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

Thank you -

Rdgs
======

[ This Message was edited by: Jack in the UK on 2002-04-30 14:23 ]

[Ian Mac]

On 2002-04-30 14:23, Jack in the UK wrote:
Chis my best mate


tell me whats wrong with times and date, anyone would have thought you dont like them!!!

I would never ask you such a question ............... would i?



_________________
If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

Thank you -

Rdgs
======

[ This Message was edited by: Jack in the UK on 2002-04-30 14:23 ]

Sorry Jack but what has this got to do with my question/post? I think nothing.

If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

but if you just want to talk to your mate...........

But seriously, if any one can think of a way to do this without striping the date/time, (Chris I hadn't thought of it til a lot later. ). The problem I have now is that I wish to do the same with another set of data within the same table working on 20000 rows, which would add a further 40000 enteries to an already fairly heavy file (i.e MSQuery to Excel to pivot).
on the other hand what I have currently is running a lot quick than the SUMPRODUCT() solution I've been experimenting with.

Many thanks,

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-04-30 15:27 ]