Columnheadings

J

Jon Rowlan

I am desperately trying to get a query to work as a subreport.

but each time I run it I get "Data Type Mismatch in Criteria Expression"
error.

Without the headings the query runs fine.

This fails :

TRANSFORM Sum([Sales By Rep By Month].SalesValue) AS SumOfSalesValue
SELECT [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep By
Month].CompanyName, [Sales By Rep By Month].Year, Sum([Sales By Rep By
Month].SalesValue) AS [Total Of SalesValue]
FROM [Sales By Rep By Month]
GROUP BY [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep By
Month].CompanyName, [Sales By Rep By Month].Year
PIVOT [Sales By Rep By Month].month In
("slm_slsperson","slr_slspname","slm_custcode","CompanyName","Year","Total
Of SalesValue",1,2,3,4,5,6,7,8,9,10,11,12);

but strangely this does not ...

TRANSFORM Sum([Sales By Rep By Month].SalesValue) AS SumOfSalesValue
SELECT [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep By
Month].CompanyName, [Sales By Rep By Month].Year, Sum([Sales By Rep By
Month].SalesValue) AS [Total Of SalesValue]
FROM [Sales By Rep By Month]
GROUP BY [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep By
Month].CompanyName, [Sales By Rep By Month].Year
PIVOT [Sales By Rep By Month].month In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18);

Can anyone tell me where I am going wrong please?

many thanks,

jON
 
D

Duane Hookom

Try change the Column Headings property:
PIVOT [Sales By Rep By Month].month In
("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18");
 
J

Jon Rowlan

Yes Duane, that works.

In fact

("1",2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18);

works but this doesn't !

TRANSFORM Sum([Sales By Rep By Month].SalesValue) AS SumOfSalesValue
SELECT [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep By
Month].CompanyName, [Sales By Rep By Month].Year, Sum([Sales By Rep By
Month].SalesValue) AS [Total Of SalesValue]
FROM [Sales By Rep By Month]
GROUP BY [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep By
Month].CompanyName, [Sales By Rep By Month].Year
PIVOT [Sales By Rep By Month].month in
("slm_slsperson","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18");

It seems to have something to do with the fact that the column name can be
translated into a numeric or not I think.

Problem is, I need to be able to set fixed column names to be able to use
the query on a subreport with a parent-child relationship.

jON



Duane Hookom said:
Try change the Column Headings property:
PIVOT [Sales By Rep By Month].month In
("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18");

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
I am desperately trying to get a query to work as a subreport.

but each time I run it I get "Data Type Mismatch in Criteria Expression"
error.

Without the headings the query runs fine.

This fails :

TRANSFORM Sum([Sales By Rep By Month].SalesValue) AS SumOfSalesValue
SELECT [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year, Sum([Sales By Rep By
Month].SalesValue) AS [Total Of SalesValue]
FROM [Sales By Rep By Month]
GROUP BY [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year
PIVOT [Sales By Rep By Month].month In
("slm_slsperson","slr_slspname","slm_custcode","CompanyName","Year","Total
Of SalesValue",1,2,3,4,5,6,7,8,9,10,11,12);

but strangely this does not ...

TRANSFORM Sum([Sales By Rep By Month].SalesValue) AS SumOfSalesValue
SELECT [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year, Sum([Sales By Rep By
Month].SalesValue) AS [Total Of SalesValue]
FROM [Sales By Rep By Month]
GROUP BY [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year
PIVOT [Sales By Rep By Month].month In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18);

Can anyone tell me where I am going wrong please?

many thanks,

jON
Click to expand...
Click to expand...
 
D

Duane Hookom

You only need to add the column names that are derived. You don't need
"slm_slsperson" in the Column Headings property.

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
Yes Duane, that works.

In fact

("1",2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18);

works but this doesn't !

TRANSFORM Sum([Sales By Rep By Month].SalesValue) AS SumOfSalesValue
SELECT [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year, Sum([Sales By Rep By
Month].SalesValue) AS [Total Of SalesValue]
FROM [Sales By Rep By Month]
GROUP BY [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year
PIVOT [Sales By Rep By Month].month in
("slm_slsperson","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18");

It seems to have something to do with the fact that the column name can be
translated into a numeric or not I think.

Problem is, I need to be able to set fixed column names to be able to use
the query on a subreport with a parent-child relationship.

jON



Duane Hookom said:
Try change the Column Headings property:
PIVOT [Sales By Rep By Month].month In
("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18");

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
I am desperately trying to get a query to work as a subreport.

but each time I run it I get "Data Type Mismatch in Criteria Expression"
error.

Without the headings the query runs fine.

This fails :

TRANSFORM Sum([Sales By Rep By Month].SalesValue) AS SumOfSalesValue
SELECT [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year, Sum([Sales By Rep
By Month].SalesValue) AS [Total Of SalesValue]
FROM [Sales By Rep By Month]
GROUP BY [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year
PIVOT [Sales By Rep By Month].month In
("slm_slsperson","slr_slspname","slm_custcode","CompanyName","Year","Total
Of SalesValue",1,2,3,4,5,6,7,8,9,10,11,12);

but strangely this does not ...

TRANSFORM Sum([Sales By Rep By Month].SalesValue) AS SumOfSalesValue
SELECT [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year, Sum([Sales By Rep
By Month].SalesValue) AS [Total Of SalesValue]
FROM [Sales By Rep By Month]
GROUP BY [Sales By Rep By Month].slm_slsperson, [Sales By Rep By
Month].slr_slspname, [Sales By Rep By Month].slm_custcode, [Sales By Rep
By Month].CompanyName, [Sales By Rep By Month].Year
PIVOT [Sales By Rep By Month].month In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18);

Can anyone tell me where I am going wrong please?

many thanks,

jON
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help With a Query; Using Expression Builder 12
Difficult Query; Trying to Show only One Record for only Two Field 1
Parameter in Crosstab Query 4
Queries & calculations 4
Grouping in a comparative query 14
Parameter Report with a Chart in the footer 1
query construction 7
Calculation needed maybe a pivot table - Thank you 2

Top