Error 3070 and a CrossTab Query

[Betti via AccessMonster.com]

Good day ,

I have been trying to find a fix for my problem with no luck. I am trying to
run a CrossTab query that is based on another nested query and I keep on
getting Error 3070. I don't have a parameter in any of my queries and I
changed the name of my fields so I am not useing a reserved word. The
followning is my Query:

TRANSFORM Count([Volume By BusUnit - Vendor Top BusUnit].[Request Count]) AS
[CountOfRequest Count]
SELECT [Volume By BusUnit - Vendor Top BusUnit].FirstDayOfWeek
FROM [Volume By BusUnit - Vendor Top BusUnit]
GROUP BY [Volume By BusUnit - Vendor Top BusUnit].FirstDayOfWeek
PIVOT [Volume By BusUnit - Vendor Top BusUnit].[Business Unit];

I am getting an error on [Volume By BusUnit - Vendor Top BusUnit].
FirstDayOfWeek

which is based on (This query runs without a problem) :

SELECT DISTINCT [ Volume By BusUnit - Vendor].FirstDayOfWeek,
Max([Volume By BusUnit - Vendor].BusUnitShortName) AS [Business Unit],
[Volume By BusUnit - Vendor].[Request Count]
FROM [Volume By BusUnit - Vendor]
GROUP BY [Volume By BusUnit - Vendor].FirstDayOfWeek,
[Volume By BusUnit - Vendor].[Request Count]
HAVING ((([Volume By BusUnit - Vendor].[Request Count]) In (select top 5
[Request Count] from [Volume By BusUnit - Vendor] I2 where [Volume By BusUnit
- Vendor].[FirstDayOfWeek] = I2.[FirstDayOfWeek] order by I2.[Request Count]
desc)))
ORDER BY [Volume By BusUnit - Vendor].FirstDayOfWeek,
[Volume By BusUnit - Vendor].[Request Count] DESC

Any help is appricated

 

[Jerry Whittle]

In the [Volume By BusUnit - Vendor Top BusUnit] query there is both a GROUP
BY and DISTINCT clause. As both should return only unique records, I'm
thinking that the crosstab is getting confused. Try removing the DISTINCT
clause.

 

[laheebbetti via AccessMonster.com]

Good day Jerry,

I removed the DISTINCT but I am still getting the same error.

Thanks,

In the [Volume By BusUnit - Vendor Top BusUnit] query there is both a GROUP
BY and DISTINCT clause. As both should return only unique records, I'm
thinking that the crosstab is getting confused. Try removing the DISTINCT
clause.

Good day ,

[quoted text clipped - 30 lines]

Any help is appricated

 

[Jerry Whittle]

SELECT DISTINCT [ Volume By BusUnit - Vendor].FirstDayOfWeek

In your original query, I found the above. Is it a typo as there is a space
between [ and V.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Good day Jerry,

I removed the DISTINCT but I am still getting the same error.

Thanks,

In the [Volume By BusUnit - Vendor Top BusUnit] query there is both a GROUP
BY and DISTINCT clause. As both should return only unique records, I'm
thinking that the crosstab is getting confused. Try removing the DISTINCT
clause.

Good day ,

[quoted text clipped - 30 lines]

Any help is appricated

 

[Betti via AccessMonster.com]

Still the same after removing the space.

SELECT DISTINCT [ Volume By BusUnit - Vendor].FirstDayOfWeek

In your original query, I found the above. Is it a typo as there is a space
between [ and V.

Good day Jerry,

[quoted text clipped - 11 lines]