Datetime and powerpivot queries

[Jackrees]

Hi all

I hope someobne can help as I am relatively new to access so please excuse if this is a dumb question.

I have a larget transaction data set in access with Datetime column/filed.

I have been running pivot queries to excel to do analysis of the data but the datetime field is returning too many unique values for the pivot table to run.

What is the best way to reduce the datatime field to date only and where should this be done?

I.e. should I have a calculated field that trims datetime or should I set someohting up in Powerpivot?

Any help much appreciated!
Jack

 

[CazB]

I'd just use something like

thedate: DateValue([yourdate])

and then use that...

not sure if there's a better way?

 

[Jackrees]

Thanks Caz

Is that as a claculated field/column in Access?

DateValue doesn't seem to work there.

Thanks again

 

[CazB]

yes, put it into a query as a calculated field... and it should work! It works in mine.... (Access 2007)

 

[Jackrees]

Hi Caz

I have been running the pivots from the data table and putting the date only column as a calculated field in the table.

Should I create a query that pullas through all the fields I want trhough and then pivot this?

PS Im using access 2010 having not used access before so slightly making this up as I go along.

Thanks for your help
Jack

 

[CazB]

I guess it could be different in 2010, I'm on 2007.... but in 2007 I would using a crosstab query in Access - is that what you're doing?

 

[Jackrees]

No I'm trinig to write a calculated field in the data table of Access 2010.

I have trimmed it to the time only using left() but there is too much data to load into powerpivot.......

Any help much appreciated.

 

[pr2-eugin]

No I'm trinig to write a calculated field in the data table of Access 2010.

Very bad idea.. Calculated fields belong in Queries not Tables..

As Caz has suggested, in the Query use DateValue and use that in the Pivot.. Say for example if your Query is..

SELECT someField, [B]theDateTimeField[/B] FROM theTable;

All you have to do is..

SELECT someField, [COLOR=Red][URL="http://www.techonthenet.com/access/functions/date/datevalue.php"]DateValue[/URL]([B]theDateTimeField[/B])[/COLOR] As newShortDate FROM theTable;

Or if the time value is not required in the first place, change the format of the Field in the table as Short Date..