Query in Access2007 - Select Query Works, Crosstab Query Doesn't

[TZEM]

Hi - I'm new to Access and trying to create a complicated database that records info about system interfaces. I want to create a crosstab query that returns a field from the interface record, if a record exists for two systems.

When I run the query as a "select" query, it works fine and returns the values. When I change the query type to "crosstab", Access no longer returns any values in the query.

Here is my "select" query SQL:

SELECT Systems.System, CrossJoinWithInterface.sub_name1, Systems_1.System, CrossJoinWithInterface.sub_name2, Last(CrossJoinWithInterface.Type) AS LastOfType
FROM Systems AS Systems_1 INNER JOIN (Systems INNER JOIN CrossJoinWithInterface ON Systems.System_ID = CrossJoinWithInterface.system_id_1) ON Systems_1.System_ID = CrossJoinWithInterface.system_id_2
GROUP BY Systems.System, CrossJoinWithInterface.sub_name1, Systems_1.System, CrossJoinWithInterface.sub_name2
HAVING (((Systems_1.System)="plumbing"));
 

Here is my "Crosstab" query SQL:

TRANSFORM Last(CrossJoinWithInterface.Type) AS LastOfType
SELECT Systems.System, CrossJoinWithInterface.sub_name1
FROM Systems AS Systems_1 INNER JOIN (Systems INNER JOIN CrossJoinWithInterface ON Systems.System_ID = CrossJoinWithInterface.system_id_1) ON Systems_1.System_ID = CrossJoinWithInterface.system_id_2
WHERE (((Systems_1.System)="plumbing"))
GROUP BY Systems.System, CrossJoinWithInterface.sub_name1, Systems_1.System
PIVOT CrossJoinWithInterface.sub_name2;
 

I would appreciate any help that you can give...thank you!!

[MMcCarthy]

It's almost impossible to say without being able to work with the data. Can you set up a file with the two tables(use dummy data if data is sensitive) and the two queries and then zip up the file and attach it to your next post. I'll check it out.

One thing I did notice was you had Systems_1.System in the group by on the crosstab query but not in the select section.