Crosstab query - data lost

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

Does anyone know of a bug when using crosstab queries? I
am getting different results if I add a long description
field (255 char) and group by it, as opposed to using
First or not including it, even though I am already
grouping by the key of this table.

The SQL that works is:

TRANSFORM Sum(TimeCostCalcs.Cost) AS SumOfCost
SELECT TimeCostCalcs.Project, TimeCostCalcs.JobCode
FROM TimeCostCalcs INNER JOIN tlkpJobCode
ON (TimeCostCalcs.JobCode = tlkpJobCode.JobCode)
AND (TimeCostCalcs.Project = tlkpJobCode.ParentProject)
WHERE (((TimeCostCalcs.Project)="fz2000")
AND ((TimeCostCalcs.WorkGroup) Like "sv*"))
GROUP BY TimeCostCalcs.Project, TimeCostCalcs.JobCode
PIVOT TimeCostCalcs.WorkGroup;

The SQL with far fewer results is:

TRANSFORM Sum(TimeCostCalcs.Cost) AS SumOfCost
SELECT TimeCostCalcs.Project, TimeCostCalcs.JobCode
FROM TimeCostCalcs INNER JOIN tlkpJobCode ON
(TimeCostCalcs.Project = tlkpJobCode.ParentProject) AND
(TimeCostCalcs.JobCode = tlkpJobCode.JobCode)
WHERE (((TimeCostCalcs.Project)="fz2000") AND
((TimeCostCalcs.WorkGroup) Like "sv*"))
GROUP BY TimeCostCalcs.Project, TimeCostCalcs.JobCode,
tlkpJobCode.JobCodeName
PIVOT TimeCostCalcs.WorkGroup;

The JobCodeName is 255 char. The key of the tlkpJobCode is
just Jobcode.

If anyone can tell me any more I would be grateful

Ta
 
Hi Ian,

I don't know if this will help, but....

First, I am not sure what "fewer results"
means. Fewer records, fewer WorkGroup columns,
fewer non-null Sums,...

If I was faced with this dilemna, I might do 2 quick
reports on the select portion of your query.

SELECT
t1.Project,
t1.JobCode,
t1.WorkGroup,
t1.Cost,
t2.ParentProject,
t2.JobCode,
t2.JobCodeName
FROM TimeCostCalcs As t1
INNER JOIN
tlkpJobCode As t2
ON
(t1.Project = t2.ParentProject)
AND
(t1.JobCode = t2.JobCode)
WHERE (((t1.Project)="fz2000")
AND
((t1.WorkGroup) Like "sv*"));

In report's Sorting and Grouping,
group by t1.Project, then t1.JobCode.
Put rest of fields in detail.
In t1.JobCode's footer, Sum(Cost).

Then run another report grouping by
t1.Project, t1.JobCode, then t2.JobCodeName.
Put rest of fields in detail.
In t2.JobCodeName footer, Sum(Cost).

Then compare the reports.

Maybe there is something there that
you did not expect.

Sorry I could not be of more help.

Good luck,

Gary Walter
 
Hi Gary
Firstly, thanks for taking the time to look at this, I
appreciate it.

Sorry I didnt explain fully, - by fewer results I mean
that there are more null results, the number of rows and
columns is the same. What I am suggesting is that by
grouping by a long field it messes up the query.(I know
this sounds crazy, but have you tried it?)

I have done another query that groups by a shorter field
and a constant string concatinated together, and I can get
the query to go wrong by changing the lenght of the string.

I tried your reports, but they didnt show any differences.
Should also say I am using Access 97.

If you have any other ideas I would be very interested.
Thanks
 
Back
Top