Crosstab queries tells lies 1

[kupe]

Something is wrong with this crosstab query. It is fed by two queries, and my join arrangement is the most likely cause.

The result should arrive with 10 columns, but it makes 15. And some of the fictitious columns have totals, so it isn't a matter of simply deleting the unwanted columns.

TRANSFORM Count(qryEmployees.Roots) AS Ethnicity
SELECT qryEmpWorkers.DailyGrind AS [Work]
FROM qryEmpWorkers INNER JOIN qryEmployees ON qryEmpWorkers.EmployeeIDFK = qryEmployees.EmployeeID
GROUP BY qryEmpWorkers.DailyGrind
PIVOT qryEmployees.Roots;

Can an Expert find the fault please?

 

[lupins46]

This is entirely a data issue. You have to look at your tables.

 

[kupe]

Thanks, lupins46. There are only two fields, and checking them over, they seem right. Where do you think a fault could lie there, please?

 

[lupins46]

Sorry, now you have said you only have two fields I have woken up!
You must have 3 fields in a crosstab, otherwise it makes no sense.
Did you write the sql directly? I don't think the crosstab wizard would allow you to create a crosstab with just two fields.

 

[kupe]

Well, well, lupins46, that's news and another problem. Only it did seem to work ok when I used it on just one drive.

Yes, I adapted the sql from this forum, I think.

I'd be grateful to hear what you experienced people would use for just two fields. Many thanks. All the best

 

[lupins46]

Just add a third column to the source. It can just be a dummy field:
Dummy:1

Then you can Count or Sum this as your statistic in your crosstab.

 

[kupe]

lupins46

Thanks very much. As I understand it, there should be 3 rows in the VBE - two that are GroupBy and the third that is Count. In order, they are RowHeading, ColumnHeading and Value. I wonder if this is what you meant?

Anyway, I have well noted the idea of the dummy field. That could be really useful.

This query had worked well in the past when it was fed by a single table. I thought the join arrangement might have been the problem now.

However, I have just discovered that some imaginative inputter has been putting-in the wrong terms, and that's where the spanner in the works lies.

Very grateful for your help, lupins46.