Defining Column Headings in Crosstab Query - Access

[worthwhile]

I have a crosstab query in Access that works well, except I'd like to have the column headings to match all of the rows contained in a table field, not just those that have data that is extracted by the query.

Is there a way to put a select statement in the column headings property in the query?

The query that works right now is:

TRANSFORM Count(qryActiveLeads.CountOfSupplierID) AS CountOfCountOfSupplierID
SELECT qryActiveLeads.Clients, Count(qryActiveLeads.CountOfSupplierID) AS [Total Of CountOfSupplierID]
FROM qryActiveLeads
GROUP BY qryActiveLeads.Clients
PIVOT qryActiveLeads.AgentsNames;

What I need, though, is to have there be a column for all of tblAgents.AgentsNames so that they will have a column even if there is no data.

I could put a list in the property, but I'd have to change it each time agents change.

I've been looking through all of the past posts, but I get lost in the code (being pretty green at this).

Thanks!

 

[MichaelRed]

There are several / many post re this specific topic in these fora, perhaps even this specific one. The usual technique is to have an outter join to a rs which includes the field to use for hte col headings. Please use search with appropiate keywords to get the details / example.



MichaelRed