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
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
PortletPaul
Use a LEFT JOIN instead of INNER JOIN
ASKER
Thanks, Paul.
I substituted LEFT JOIN for INNER JOIN.
Access throws an error, "JOIN expression not supported.". Ugh!
I substituted LEFT JOIN for INNER JOIN.
Access throws an error, "JOIN expression not supported.". Ugh!
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:
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;ASKER
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
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
ASKER
Sorry, the link for the Access database for testing is:
https://www.dropbox.com/s/
https://www.dropbox.com/s/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Paul, you are a Legend!
Greetings from Blackburn/Melbourne.
Regards,
Leigh
Greetings from Blackburn/Melbourne.
Regards,
Leigh
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 :)
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 :)
ASKER
Life in Williamstown sounds good to me...