Runs Ok, EXPORT fails with:Cannot define Field more than once

[Phil Smith]

I can open this query, and it workds perfectly well. If I try to export
it directly to Excel, I get an error:
Cannot define field more than once.

While doing some websearch, I came across one contention that Access can
only export 10 characters worth of fieldname. I definitely have a few
entries which have the 1st ten characters the same. Is this really a
(truly incredibly unjustifiably lame,)limitation on the part of Access 2003?

------------

TRANSFORM
Sum([item_warehouse_link]![onhand_qty]*[item_warehouse_link]![average_cost])
AS Expr1
SELECT category.name AS Category, item.old_sku, item.item_id,
item.short_desc
FROM category RIGHT JOIN ((item_warehouse_link RIGHT JOIN item ON
item_warehouse_link.item_id = item.item_id) LEFT JOIN warehouse ON
item_warehouse_link.warehouse_id = warehouse.warehouse_id) ON
category.category_id = item.master_category_id
GROUP BY category.name, item.old_sku, item.item_id, item.short_desc
ORDER BY category.name, item.old_sku, item.item_id, item.short_desc
PIVOT warehouse.name;

 

[Jeanette Cunningham]

Hi,
Sounds as if you have the same field twice in your query.
Check your query to see if you have put the same field in twice.

Jeanette Cunningham

 

[Phil Smith]

I did and I don't. A couple are similar, up to about the first 10
characters or so, but none are the same.

Hi,
Sounds as if you have the same field twice in your query.
Check your query to see if you have put the same field in twice.

Jeanette Cunningham

I can open this query, and it workds perfectly well. If I try to export it
directly to Excel, I get an error:
Cannot define field more than once.

While doing some websearch, I came across one contention that Access can
only export 10 characters worth of fieldname. I definitely have a few
entries which have the 1st ten characters the same. Is this really a
(truly incredibly unjustifiably lame,)limitation on the part of Access
2003?

------------

TRANSFORM
Sum([item_warehouse_link]![onhand_qty]*[item_warehouse_link]![average_cost])
AS Expr1
SELECT category.name AS Category, item.old_sku, item.item_id,
item.short_desc
FROM category RIGHT JOIN ((item_warehouse_link RIGHT JOIN item ON
item_warehouse_link.item_id = item.item_id) LEFT JOIN warehouse ON
item_warehouse_link.warehouse_id = warehouse.warehouse_id) ON
category.category_id = item.master_category_id
GROUP BY category.name, item.old_sku, item.item_id, item.short_desc
ORDER BY category.name, item.old_sku, item.item_id, item.short_desc
PIVOT warehouse.name;

 

[Jeanette Cunningham]

Hi,
I haven't come across this error in the many exports I have done.
Not sure what to suggest - maybe there is a problem with the actual field
names, ie the characters in them?
I assume you are exporting this using TransferSpreadsheet or OutputTo.

One approach I try myself to track down this sort of thing is to go
stepwise.
Make a query with just one of the fields you want to export. Export it, keep
repeating this process until you find the field or fields that give the
error. The solution often becomes apparent when you can see which field
gives the error. Hope this helps.

Jeanette Cunningham

I did and I don't. A couple are similar, up to about the first 10
characters or so, but none are the same.

Hi,
Sounds as if you have the same field twice in your query.
Check your query to see if you have put the same field in twice.

Jeanette Cunningham

I can open this query, and it workds perfectly well. If I try to export
it directly to Excel, I get an error:
Cannot define field more than once.

While doing some websearch, I came across one contention that Access can
only export 10 characters worth of fieldname. I definitely have a few
entries which have the 1st ten characters the same. Is this really a
(truly incredibly unjustifiably lame,)limitation on the part of Access
2003?

------------

TRANSFORM
Sum([item_warehouse_link]![onhand_qty]*[item_warehouse_link]![average_cost])
AS Expr1
SELECT category.name AS Category, item.old_sku, item.item_id,
item.short_desc
FROM category RIGHT JOIN ((item_warehouse_link RIGHT JOIN item ON
item_warehouse_link.item_id = item.item_id) LEFT JOIN warehouse ON
item_warehouse_link.warehouse_id = warehouse.warehouse_id) ON
category.category_id = item.master_category_id
GROUP BY category.name, item.old_sku, item.item_id, item.short_desc
ORDER BY category.name, item.old_sku, item.item_id, item.short_desc
PIVOT warehouse.name;

 

[Jeanette Cunningham]

Hi again,
I just checked in one of my apps that uses crosstab queries and exports them
to excel.
I use a saved crosstab query that gets its data from a temporary table that
is used to format data for export.

Jeanette Cunningham

I did and I don't. A couple are similar, up to about the first 10
characters or so, but none are the same.

Hi,
Sounds as if you have the same field twice in your query.
Check your query to see if you have put the same field in twice.

Jeanette Cunningham

I can open this query, and it workds perfectly well. If I try to export
it directly to Excel, I get an error:
Cannot define field more than once.

While doing some websearch, I came across one contention that Access can
only export 10 characters worth of fieldname. I definitely have a few
entries which have the 1st ten characters the same. Is this really a
(truly incredibly unjustifiably lame,)limitation on the part of Access
2003?

------------

TRANSFORM
Sum([item_warehouse_link]![onhand_qty]*[item_warehouse_link]![average_cost])
AS Expr1
SELECT category.name AS Category, item.old_sku, item.item_id,
item.short_desc
FROM category RIGHT JOIN ((item_warehouse_link RIGHT JOIN item ON
item_warehouse_link.item_id = item.item_id) LEFT JOIN warehouse ON
item_warehouse_link.warehouse_id = warehouse.warehouse_id) ON
category.category_id = item.master_category_id
GROUP BY category.name, item.old_sku, item.item_id, item.short_desc
ORDER BY category.name, item.old_sku, item.item_id, item.short_desc
PIVOT warehouse.name;

 

[John Spencer]

One thing you can try is to open the query in datasheet view and copy
the entire result (Or just the first row) then paste that into a new
spreadsheet.

That should let you examine the column titles for duplication. I have
not seen the problem you are getting.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi again,
I just checked in one of my apps that uses crosstab queries and exports them
to excel.
I use a saved crosstab query that gets its data from a temporary table that
is used to format data for export.

Jeanette Cunningham

I did and I don't. A couple are similar, up to about the first 10
characters or so, but none are the same.

Hi,
Sounds as if you have the same field twice in your query.
Check your query to see if you have put the same field in twice.

Jeanette Cunningham




I can open this query, and it workds perfectly well. If I try to export
it directly to Excel, I get an error:
Cannot define field more than once.

While doing some websearch, I came across one contention that Access can
only export 10 characters worth of fieldname. I definitely have a few
entries which have the 1st ten characters the same. Is this really a
(truly incredibly unjustifiably lame,)limitation on the part of Access
2003?

------------

TRANSFORM
Sum([item_warehouse_link]![onhand_qty]*[item_warehouse_link]![average_cost])
AS Expr1
SELECT category.name AS Category, item.old_sku, item.item_id,
item.short_desc
FROM category RIGHT JOIN ((item_warehouse_link RIGHT JOIN item ON
item_warehouse_link.item_id = item.item_id) LEFT JOIN warehouse ON
item_warehouse_link.warehouse_id = warehouse.warehouse_id) ON
category.category_id = item.master_category_id
GROUP BY category.name, item.old_sku, item.item_id, item.short_desc
ORDER BY category.name, item.old_sku, item.item_id, item.short_desc
PIVOT warehouse.name;

 

[Gary Walter]

Hi Phil,

I wonder if this does not have something to do with

-- use of "name" for fields in 2 tables (reserved word)

and/or

-- aliasing category.name with same name as table

Easy test, try...

SELECT category.[name] AS CatName, item.old_sku, item.item_id,

and

put brackets around all ".[name]"

 

[Phil Smith]

I did all that even before I came here. There are no duplicates, and no
special characters.

One thing you can try is to open the query in datasheet view and copy
the entire result (Or just the first row) then paste that into a new
spreadsheet.

That should let you examine the column titles for duplication. I have
not seen the problem you are getting.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi again,
I just checked in one of my apps that uses crosstab queries and
exports them to excel.
I use a saved crosstab query that gets its data from a temporary table
that is used to format data for export.

Jeanette Cunningham

I did and I don't. A couple are similar, up to about the first 10
characters or so, but none are the same.


Jeanette Cunningham wrote:

Hi,
Sounds as if you have the same field twice in your query.
Check your query to see if you have put the same field in twice.

Jeanette Cunningham




I can open this query, and it workds perfectly well. If I try to
export it directly to Excel, I get an error:
Cannot define field more than once.

While doing some websearch, I came across one contention that
Access can only export 10 characters worth of fieldname. I
definitely have a few entries which have the 1st ten characters the
same. Is this really a (truly incredibly unjustifiably
lame,)limitation on the part of Access 2003?

------------

TRANSFORM
Sum([item_warehouse_link]![onhand_qty]*[item_warehouse_link]![average_cost])
AS Expr1
SELECT category.name AS Category, item.old_sku, item.item_id,
item.short_desc

FROM category RIGHT JOIN ((item_warehouse_link RIGHT JOIN item ON

item_warehouse_link.item_id = item.item_id) LEFT JOIN warehouse ON
item_warehouse_link.warehouse_id = warehouse.warehouse_id) ON
category.category_id = item.master_category_id
GROUP BY category.name, item.old_sku, item.item_id, item.short_desc
ORDER BY category.name, item.old_sku, item.item_id, item.short_desc
PIVOT warehouse.name;

 

[DoncoMania]

I noticed you said some of your fields had similar characters up through the first 10 or so meaning you probably have some pretty long field names. I had the same issue and by shortening the field names I was able to get it to export correctly.