Report Question...

[Sean Massey]

I'm trying to build a series of reports for a property tracking database
that I am building that will list information about the property and the
real estate agent that did the listing or made the sale.

My problem is that I need the column headers to remain in place even if
that agent doesn't have a sale or listing. If there is no data for that
column, I get an error message saying Jet doesn't recognize that field.

I need the headers and the columns to remain in place, even if the agent
doesn't have a listing or a sale because the users of this database
won't have the skills to modify the report.

My query for this report is a cross-tab query (need to count the agents
so the totals can be added up at the end of the report) that takes data
from the Property and Offer table. The column header in the cross-tab
query is a "calculated" field that combines the Agent's first and last
name as a string.

My SQL statement for one of the cross-tabs is as follows:

TRANSFORM Count([Active Property Query for CrossTab].Agent_ID) AS
CountOfAgent_ID
SELECT [Active Property Query for CrossTab].Address, [Active Property
Query for CrossTab].Property_PIN, [Active Property Query for
CrossTab].Business_Source_ID, [Active Property Query for
CrossTab].Property_List_Date, [Active Property Query for
CrossTab].Property_Expire_Date
FROM [Active Property Query for CrossTab]
GROUP BY [Active Property Query for CrossTab].Address, [Active Property
Query for CrossTab].Property_PIN, [Active Property Query for
CrossTab].Business_Source_ID, [Active Property Query for
CrossTab].Property_List_Date, [Active Property Query for
CrossTab].Property_Expire_Date
PIVOT [Active Property Query for CrossTab].[Agent Name];

I've tried using a subquery to get the agent names, but I get an error
statement.

 

[Marshall Barton]

I'm trying to build a series of reports for a property tracking database
that I am building that will list information about the property and the
real estate agent that did the listing or made the sale.

My problem is that I need the column headers to remain in place even if
that agent doesn't have a sale or listing. If there is no data for that
column, I get an error message saying Jet doesn't recognize that field.

I need the headers and the columns to remain in place, even if the agent
doesn't have a listing or a sale because the users of this database
won't have the skills to modify the report.

My query for this report is a cross-tab query (need to count the agents
so the totals can be added up at the end of the report) that takes data
from the Property and Offer table. The column header in the cross-tab
query is a "calculated" field that combines the Agent's first and last
name as a string.

My SQL statement for one of the cross-tabs is as follows:

TRANSFORM Count([Active Property Query for CrossTab].Agent_ID) AS
CountOfAgent_ID
SELECT [Active Property Query for CrossTab].Address, [Active Property
Query for CrossTab].Property_PIN, [Active Property Query for
CrossTab].Business_Source_ID, [Active Property Query for
CrossTab].Property_List_Date, [Active Property Query for
CrossTab].Property_Expire_Date
FROM [Active Property Query for CrossTab]
GROUP BY [Active Property Query for CrossTab].Address, [Active Property
Query for CrossTab].Property_PIN, [Active Property Query for
CrossTab].Business_Source_ID, [Active Property Query for
CrossTab].Property_List_Date, [Active Property Query for
CrossTab].Property_Expire_Date
PIVOT [Active Property Query for CrossTab].[Agent Name];

I've tried using a subquery to get the agent names, but I get an error
statement.

You need to use an outer Join from the agents table to the
other data in the active property query. This way all
agents will appear in the crosstab.

BUT, what will you do when you add a new agent, an agent
leaves or changes her name? This kind of data should not be
used as column headings because of the problems you are
seeing. If possible, you should use something like a
sequential number that can be used in the report to
lookup/calculate the name.

 

[Sean Massey]

I'm trying to build a series of reports for a property tracking database
that I am building that will list information about the property and the
real estate agent that did the listing or made the sale.

My problem is that I need the column headers to remain in place even if
that agent doesn't have a sale or listing. If there is no data for that
column, I get an error message saying Jet doesn't recognize that field.

I need the headers and the columns to remain in place, even if the agent
doesn't have a listing or a sale because the users of this database
won't have the skills to modify the report.

My query for this report is a cross-tab query (need to count the agents
so the totals can be added up at the end of the report) that takes data
from the Property and Offer table. The column header in the cross-tab
query is a "calculated" field that combines the Agent's first and last
name as a string.

My SQL statement for one of the cross-tabs is as follows:

TRANSFORM Count([Active Property Query for CrossTab].Agent_ID) AS
CountOfAgent_ID
SELECT [Active Property Query for CrossTab].Address, [Active Property
Query for CrossTab].Property_PIN, [Active Property Query for
CrossTab].Business_Source_ID, [Active Property Query for
CrossTab].Property_List_Date, [Active Property Query for
CrossTab].Property_Expire_Date
FROM [Active Property Query for CrossTab]
GROUP BY [Active Property Query for CrossTab].Address, [Active Property
Query for CrossTab].Property_PIN, [Active Property Query for
CrossTab].Business_Source_ID, [Active Property Query for
CrossTab].Property_List_Date, [Active Property Query for
CrossTab].Property_Expire_Date
PIVOT [Active Property Query for CrossTab].[Agent Name];

I've tried using a subquery to get the agent names, but I get an error
statement.

You need to use an outer Join from the agents table to the
other data in the active property query. This way all
agents will appear in the crosstab.

Thanks. That worked like a charm.

BUT, what will you do when you add a new agent, an agent
leaves or changes her name? This kind of data should not be
used as column headings because of the problems you are
seeing. If possible, you should use something like a
sequential number that can be used in the report to
lookup/calculate the name.

I brought that up with the guy who does IT here full-time. He said
agents don't come or go too often, so I shouldn't worry about it. So
I'm not gonna worry about it. )