Error 3070 and a CrossTab Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Betti - 22 Feb 2008 18:04 GMT

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

Reply to this Message

Jerry Whittle - 22 Feb 2008 19:24 GMT

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.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Good day ,
>
[quoted text clipped - 30 lines]
>
> Any help is appricated

Reply to this Message

laheebbetti - 25 Feb 2008 14:19 GMT

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
[quoted text clipped - 5 lines]
>>
>> Any help is appricated

Reply to this Message

Jerry Whittle - 27 Feb 2008 16:30 GMT

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.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Good day Jerry,
>
[quoted text clipped - 11 lines]
> >>
> >> Any help is appricated

Reply to this Message

Betti - 27 Feb 2008 19:49 GMT

Still the same after removing the space.

>SELECT DISTINCT [ Volume By BusUnit - Vendor].FirstDayOfWeek
>
[quoted text clipped - 5 lines]
>> >>
>> >> Any help is appricated

Reply to this Message