Wrong Data type for Pivot Table 1

[alectek]

Hi! I have a problem to build Pivot table in Excel 2003.
I’m creating this table base on the “External Data” which is a MS Access 2003 table.
The table has 50 fields and about 500.000 records. (This is the reason, I can’t just export table to Excel and then do pivoting). One of the fields of this table has a “text” type but stored numbers. Excel does not allowed me do Sum or Max function with this field – it needs to have Number data type.
I receiving this table “from outside”, so I can’t get the right data type from the beginning. If I’m trying simply opening the table, before, using as a data source for Pivot Table, in Design mode and just change data type from text to number, I have an error: "Microsoft Access can't change the data type. There isn't enough disk space or memory."
Any advice, how to change Data type in existing table using queries or something else what can help me to solve this problem.

Thank you very much at advance.



Thanks.
Alec.

 

[dhookom]

Can you base your pivot table on a query that uses CLng() or Val() or similar to convert the text to a number?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[alectek]

Dhoomkom, thank you for your suggestion.
However, val()or CLng() return the numbers contained in a string as a numeric value. It is not changing the field data type. Than Excel reads it like a text any way.


Thanks.
Alec.

 

[dhookom]

Can you explain what steps you used? Is your pivot connected to your query? What is the SQL view of your query? Which field are you having an issue with? Did you refresh/requery your pivot?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[alectek]

dhookom, thanks again for your help.

Here is the solution I got from another forum and it works.

<A query-based way to solve it is craete another field in your table with the type you want, and then run this query :

update <YOURTABLENAME> set <YOURNEWFIELD> = val(<YOURNUMBERASTEXTFIELD>)

where <YOURTABLENAME> is the name of your table,
<YOURNEWFIELD> is the name of your new field,
<YOURNUMBERASTEXTFIELD> is the name of the field where the numbers are stored as text.>


Thanks.
Alec.

 

[dhookom]

I was assuming you couldn't change the table structure based on your comment "so I can’t get the right data type from the beginning". If you can add a new field and then update it, that would certainly be one solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]