Possible Crosstab Querry?

[Doctor]

I have a table of organizations. And I also have a related table of
directors of the organizations. The table is related because often there is
more than one director, but usually not more than 4.

I am trying to create a spreadsheet like export of all of this information.
But this one part is holding me up. If there is more than one director I
would like a new column added to the query like Director_1, Director_2, etc.

What I have now:

tblOrganizations
OrgID
OrgName

tblDirectors
DirID
Name
OrgID

If there are more than one directors I would like the query to output
something like:
OrgID, OrgName, Director_1, Director_2, Director_3
1 Cass Ted Sam John
2 Fremont Alex Dan Mark

How can I accomplish this? Tried crosstabs. Haven't worked yet.

Thanks in advance.

 

[Duane Hookom]

You can create this in a couple ways. One uses DCount() to create the number
for the column heading. This example uses the Employee table in Northwind:

TRANSFORM Max(Employees.FirstName) AS MaxOfFirstName
SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title
ORDER BY Employees.Title
PIVOT "Employee_" & DCount("*","Employees","Title=""" & [Title] & """ AND
FirstName <=""" & [FirstName] & """");

 

[Doctor]

Duane, thanks for the help. This is what I was looking for. Works great in
Northwind.

But I'm getting the ol' Data type mismatch in my query. I've checked
everything that I know to check. SQL is pasted below. It is based on another
query that finds contacts marked as directors.

ContactChurch is a number field
Name is a text field


**SQL**
TRANSFORM Max(qryCharterRenewal_Directors.Name) AS MaxOfFirstName
SELECT qryCharterRenewal_Directors.ContactChurch
FROM qryCharterRenewal_Directors
GROUP BY qryCharterRenewal_Directors.ContactChurch
ORDER BY qryCharterRenewal_Directors.ContactChurch
PIVOT "Employee_" &
DCount("*","qryCharterRenewal_Directors","ContactChurch=""" & [ContactChurch]
& """ AND Name <=""" & [Name] & """");

You can create this in a couple ways. One uses DCount() to create the number
for the column heading. This example uses the Employee table in Northwind:

TRANSFORM Max(Employees.FirstName) AS MaxOfFirstName
SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title
ORDER BY Employees.Title
PIVOT "Employee_" & DCount("*","Employees","Title=""" & [Title] & """ AND
FirstName <=""" & [FirstName] & """");

--
Duane Hookom
Microsoft Access MVP

I have a table of organizations. And I also have a related table of
directors of the organizations. The table is related because often there is
more than one director, but usually not more than 4.

I am trying to create a spreadsheet like export of all of this information.
But this one part is holding me up. If there is more than one director I
would like a new column added to the query like Director_1, Director_2, etc.

What I have now:

tblOrganizations
OrgID
OrgName

tblDirectors
DirID
Name
OrgID

If there are more than one directors I would like the query to output
something like:
OrgID, OrgName, Director_1, Director_2, Director_3
1 Cass Ted Sam John
2 Fremont Alex Dan Mark

How can I accomplish this? Tried crosstabs. Haven't worked yet.

Thanks in advance.

 

[Doctor]

Never Mind. I figured it out. After I realized that the row field in your
example was text instead of a number, I took out the extra quotes and voila!

Thanks again.

You can create this in a couple ways. One uses DCount() to create the number
for the column heading. This example uses the Employee table in Northwind:

TRANSFORM Max(Employees.FirstName) AS MaxOfFirstName
SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title
ORDER BY Employees.Title
PIVOT "Employee_" & DCount("*","Employees","Title=""" & [Title] & """ AND
FirstName <=""" & [FirstName] & """");

--
Duane Hookom
Microsoft Access MVP

I have a table of organizations. And I also have a related table of
directors of the organizations. The table is related because often there is
more than one director, but usually not more than 4.

I am trying to create a spreadsheet like export of all of this information.
But this one part is holding me up. If there is more than one director I
would like a new column added to the query like Director_1, Director_2, etc.

What I have now:

tblOrganizations
OrgID
OrgName

tblDirectors
DirID
Name
OrgID

If there are more than one directors I would like the query to output
something like:
OrgID, OrgName, Director_1, Director_2, Director_3
1 Cass Ted Sam John
2 Fremont Alex Dan Mark

How can I accomplish this? Tried crosstabs. Haven't worked yet.

Thanks in advance.