By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,994 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,994 IT Pros & Developers. It's quick & easy.

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

P: n/a
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Systems.System, CrossJoinWithInterface.sub_name1, Systems_1.System, CrossJoinWithInterface.sub_name2, Last(CrossJoinWithInterface.Type) AS LastOfType
  2. 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
  3. GROUP BY Systems.System, CrossJoinWithInterface.sub_name1, Systems_1.System, CrossJoinWithInterface.sub_name2
  4. HAVING (((Systems_1.System)="plumbing"));
Here is my "Crosstab" query SQL:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Last(CrossJoinWithInterface.Type) AS LastOfType
  2. SELECT Systems.System, CrossJoinWithInterface.sub_name1
  3. 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
  4. WHERE (((Systems_1.System)="plumbing"))
  5. GROUP BY Systems.System, CrossJoinWithInterface.sub_name1, Systems_1.System
  6. PIVOT CrossJoinWithInterface.sub_name2;
I would appreciate any help that you can give...thank you!!
Oct 7 '10 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 14,534
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.
Oct 8 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.