Help with Crosstab Query

Hi Experts,

I have a working Crosstab query:

TRANSFORM Count(Items.ItemCategory) AS CountOfItemCategory
SELECT CLIENTS3.ClientID
FROM Items INNER JOIN (CLIENTS3 LEFT JOIN [Item Transactions] ON CLIENTS3.ClientID = [Item Transactions].CustomerID) ON Items.ItemID = [Item Transactions].ItemID
GROUP BY CLIENTS3.ClientID
PIVOT Items.ItemCategory;

The output looks OK, but I want the query to also include records in CLIENTS3 for which there are no linked records in [Item Transactions].

How can I include these extra records?

Please let me know if you need extra information.

Regards,
Leigh
LVL 1
LeighWardleAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
This worked for me:
TRANSFORM Count(CLIENTS3.ClientID) AS CountOfClientID
SELECT CLIENTS3.ClientID
FROM CLIENTS3 LEFT JOIN ([Item Transactions] LEFT JOIN Items ON [Item Transactions].ItemID = Items.ItemID) ON CLIENTS3.ClientID = [Item Transactions].CustomerID
GROUP BY CLIENTS3.ClientID
PIVOT Items.ItemCategory;

Open in new window


+ You need one row heading, one column heading and one value in the design viewaccess-xtab.PNGWhich produced:access-xtab-result.PNG
0
 
PortletPaulfreelancerCommented:
Use a LEFT JOIN instead of INNER JOIN
0
 
LeighWardleAuthor Commented:
Thanks, Paul.

I substituted LEFT JOIN for INNER JOIN.

Access throws an error, "JOIN expression not supported.".  Ugh!
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
PortletPaulfreelancerCommented:
mmmm, clearly Access does support left joins as you are already using one.

However, I would suggest using the clients table first, then left join the transactions.
This is my best guess without the ability to test:
TRANSFORM Count(Items.ItemCategory) AS CountOfItemCategory
SELECT CLIENTS3.ClientID
FROM ((CLIENTS3
LEFT JOIN [Item Transactions] ON CLIENTS3.ClientID = [Item Transactions].CustomerID)
LEFT JOIN Items  ON [Item Transactions].ItemID = Items.ItemID)
PIVOT Items.ItemCategory;

Open in new window

0
 
LeighWardleAuthor Commented:
Thanks, Paul, for your efforts.

Access gives this error: "Syntax error in TRANSFORM statement."

I've created an Access database for testing.

If the query outputs all the customers, the first row should have CustomerID=1.

Regards,
Leigh
0
 
LeighWardleAuthor Commented:
Sorry, the link for the Access database for testing is:
https://www.dropbox.com/s/j8mzhwutwq32wvq/Crosstab%20Query%20Test.zip?dl=0
0
 
LeighWardleAuthor Commented:
Paul, you are a Legend!

Greetings from Blackburn/Melbourne.

Regards,
Leigh
0
 
PortletPaulfreelancerCommented:
Many years since I was in Blackburn...

was a lad from Mt Evelyn/Lilydale travelling by train to school (Melb. High)
Blackburn was the station used by several (very attractive) girls

ah, memories

Now live in Williamstown and rarely use a train :)
0
 
LeighWardleAuthor Commented:
Life in Williamstown sounds good to me...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.