Re: Crosstab query - data lost
From: ian (anonymous_at_discussions.microsoft.com)
Date: 05/07/04
- Next message: Gary Walter: "Re: Cannot Open any databases without a specific error message"
- Previous message: MER: "Re: define fields"
- In reply to: Gary Walter: "Re: Crosstab query - data lost"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 7 May 2004 04:53:30 -0700
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
>-----Original Message-----
>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
>
>
>
>
>
>"Ian" <anonymous@discussions.microsoft.com> wrote
>> 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
>>
>
>
>.
>
- Next message: Gary Walter: "Re: Cannot Open any databases without a specific error message"
- Previous message: MER: "Re: define fields"
- In reply to: Gary Walter: "Re: Crosstab query - data lost"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
