Crosstab Query

[hinchi1]

Hello



I have done a crosstab query and I get the results I was looking for. However, there is a column which shows up in the query and I am unsure why. It is the column with <>., highlighted in yellow. I can't seem to delete the column and when I print to Excel this column is present, which I don't want. I have tried hiding the column but it still is displayed in Excel. The SQL generated is below:



TRANSFORM Last(qCoursesAttendedReport.[Course Check]) AS [LastOfCourse Check]
SELECT qCoursesAttendedReport.LastName
FROM qCoursesAttendedReport
GROUP BY qCoursesAttendedReport.LastName
PIVOT qCoursesAttendedReport.EventName;

[Micron]

The source of your crosstab is another query (qCoursesAttendedReport)? Suggest you show that sql as well as it may be caused by that one.

[hinchi1]

Hello

I have done a crosstab query and I get the results I was looking for. However, there is a column which shows up in the query and I am unsure why. It is the column with <>., highlighted in yellow. I can't seem to delete the column and when I print to Excel this column is present, which I don't want. I have tried hiding the column but it still is displayed in Excel. The SQL generated is below:



TRANSFORM Last(qCoursesAttendedReport.[Course Check]) AS [LastOfCourse Check]
SELECT qCoursesAttendedReport.LastName
FROM qCoursesAttendedReport
GROUP BY qCoursesAttendedReport.LastName
PIVOT qCoursesAttendedReport.EventName;

SELECT DISTINCT tblMembers.LastName, tblMembers.FirstName, tblEvents.EventName, tblJunction.DateCompleted, tblJunction.RenewalDate, IIf([DateCompleted]<=Date(),"✓") AS [Course Check]
FROM tblMembers LEFT JOIN (tblEvents RIGHT JOIN tblJunction ON tblEvents.Events_PK = tblJunction.Events_FK) ON tblMembers.Members_PK = tblJunction.Members_FK;

[Micron]

Hard to see a relationship between the ct results and the first query in the stack but I guess it's there. I would try replacing the check within the IIF function with "Y" or a number like 0 and test that.

[hinchi1]

Hard to see a relationship between the ct results and the first query in the stack but I guess it's there. I would try replacing the check within the IIF function with "Y" or a number like 0 and test that.

Will give it a try. I have coded the column out when exporting to Excel, so not such a problem, but would be good to if what you suggested works.

[June7]

That column reflects records that don't have a value in EventName.

[Micron]

That column reflects records that don't have a value in EventName.

I realize the field names in the ct query will come from transposed groups of values but how does that give the field a name of "<>"?

[June7]

Access just does that automatically. There is no data for the header so it just uses <>. Try it.

[Micron]

Thanks, but I'll take your word for it. Can't recall ever seeing that before. Would have thought it would just eliminate the column.

[June7]

Nope. The field has Null (or empty string - which I never allow in tables) and the CT still has to aggregate the record(s). If CT used IN() function then the records could be ignored.