Crosstab query only displays rows 1-2276 out of 3541? 1

[MrMode]

I have a cross tab query that works except that it is only providing the information for rows 1 - 2276, there are 3541 rows that need to be created.

Why is dropping these additional rows and how do I fix it?

TRANSFORM First([AdvancedCompanyInfo].Advinfo) AS FieldValue
SELECT tb_company_basic.company_id
FROM (tb_company_basic INNER JOIN [AdvancedCompanyInfo] ON tb_company_basic.company_id = [AdvancedCompanyInfo].company_id) INNER JOIN tb_hmc_fields ON [AdvancedCompanyInfo].web_id = tb_hmc_fields.web_id
GROUP BY tb_company_basic.company_id
PIVOT tb_hmc_fields.name;

The table with the rows that I want to transform is tb_hmc_fields which is linked to AdvanceCompanyInfo via web_id key. AdvanceCompanyInfo has the company_id reference needed to link the values in tb_hmc_fields to tb_company_basic.

 

[SkipVought]

hi,

except that it is only providing the information for rows 1 - 2276, there are 3541 rows that need to be created.

How do you know that?

[pre]
Please post a data sample from the 3 SOURCE TABLES that [highlight #FCE94F]IS[/highlight] being reported

AND

Please post a data sample from the 3 SOURCE TABLES that [highlight #FCE94F]IS NOT[/highlight] being reported
[/pre]
Please annotate youor data in order to make it perfectly clear which data samples are in which table & category.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MrMode]

OK,I have put the information on the attached, I think I have done what you asked.

 

[SkipVought]

No, actually that document is on your hard drive and inaccessible.

Please post directly here, using the [tt][ignore][pre]...[/pre][/ignore][/tt] TGML tags to post your tabular data and [highlight #CC0000]Preview[/highlight] your post to assure its readablity before you [highlight #CC0000]Submit Post[/highlight].

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MrMode]

Second attempt.

This is the source table (tb_advanced_info_correlation)
[pre]company_id web_id Value
2276 1 1929
2276 2 plc
2276 3 halfen
2276 4 CRH plc
2276 6 uk head office Dunstable
2276 22 Y
2276 23 high
2276 24 Y
2276 25 IS09001
2276 26 Y
2276 27 high
2276 28 1
2278 1 1994
2278 2 ltd
2278 3
2278 4
2278 6 Bristol
2278 22 Y
2278 23 Chas
2278 24 N
2278 25
2278 26 Y
2278 27 Chas
2278 28 3[/pre]

Which is then transformed into this:

[pre]company_id 1 2 3 4 5 6 22 23 24 25 26 27 28
2276 1929 plc halfen CRH plc 76,500 uk head office Dunstable Y high Y IS09001 Y high 1 [/pre]

The third table provides the column names, but leaving it out does not seem to fix the issue, so let's work with it this way for now.

There are 1046 of the 2111 rows that I am expecting (Company id up to 2776 out of 3541) in the crosstab query that I will then turn into a table.

 

[SkipVought]

Your example is not coherent!

The Transform does not correspond to the data that you posted in tb_advanced_info_correlation!!!

I asked for [highlight #FCE94F]an EXAMPLE that corresponds with your reality[/highlight]: tb_advanced_info_correlation that appears in the transform and tb_advanced_info_correlation that does NOT appear in the transform, so that when anyone woudl run your SQL, the results could be investigated.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MrMode]

Writing out the problem again revealed the answer.

The tables I am using (not mine) have company_id as the key.

The pivot was limited to only those companies with company_id in both tables, while this is worrying for a different reason, it means the pivot was doing exactly what it was asked to.

Thanks for trying to help.

 

[SkipVought]

You did EXACTLY what I expected might happen when you track back to your source data. Its the sort of sleuthing that an analyst does.

The data, or lack of data, tells the story.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MrMode]

Yes, wood for the trees all too often in my case, thanks again!

 

[PHV]

So, now you use LEFT joins ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

it has revealed a bigger issue with a dataset I have been asked to work with - orphaned records. Dangerous to assume that what someone tells you is accurate!