Problem with Calculated Fields in Query...

S

Sean Massey

I'm trying to put together a business sources report for my real estate
database.

The problem I am having is a result of the requirements of the report.
The report needs to show the total number of each business source for
listings and for accepted offers on the properties. Since the data is
located in two different tables, I'm creating two subqueries that I am
attempting to combine into a single query.

Each subquery will count the number of each business source in the
Property or Offer Table.

What I need to do is add those two fields together to get that total
number, but I run into a problem trying this. When I combine the
queries, it shows the data in the count columns, but the fields in the
calculated field remain blank.

The table structure for this is as follows:

A business sources table that has the two letter code and business
source name. It links to two other tables.

A business source field in the Property Table that identifies the source
the listing came from. This is linked with the ID field on the business
source table.

An offer source field in the Offer Table that identifies the business
source the offer came from. This is linked with the ID field on the
business source table.

Both the Property and Offer tables use a lookup to get the correct value
for the business source.

The subqueries are as follows:

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Count(Property.Business_Source_ID)
AS CountOfBusiness_Source_ID
FROM Business_Source LEFT JOIN (Property LEFT JOIN Offer ON
Property.Property_ID=Offer.Property_ID) ON
Business_Source.Business_Source_ID=Property.Business_Source_ID
GROUP BY Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Property.Property_Status,
Offer.Offer_Status
HAVING (((Property.Property_Status)="Fall-Through") AND
((Offer.Offer_Status)="A/O"));

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Count(Offer.Offer_Source_ID) AS
CountOfOffer_Source_ID
FROM (Business_Source LEFT JOIN Offer ON
Business_Source.Business_Source_ID = Offer.Offer_Source_ID) LEFT JOIN
Property ON Offer.Property_ID = Property.Property_ID
GROUP BY Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Property.Property_Status,
Offer.Offer_Status
HAVING (((Property.Property_Status)="Fall-Through") AND
((Offer.Offer_Status)="A/O"));

My Attempt to join these two queries together is done through the
following query:

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, [Business Source Query Part
2].CountOfBusiness_Source_ID, [Business Source Query Part
2A].CountOfOffer_Source_ID, [Business Source Query Part
2]![CountOfBusiness_Source_ID]+[Business Source Query Part
2A]![CountOfOffer_Source_ID] AS Total
FROM ([Business Source Query Part 2] RIGHT JOIN Business_Source ON
[Business Source Query Part 2].Business_Source_ID =
Business_Source.Business_Source_ID) LEFT JOIN [Business Source Query
Part 2A] ON Business_Source.Business_Source_ID = [Business Source Query
Part 2A].Business_Source_ID;

What am I doing wrong that results in the total column remaining blank,
and how can I correct it?
 
S

Sean Massey

Sean said:
I'm trying to put together a business sources report for my real estate
database.

The problem I am having is a result of the requirements of the report.
The report needs to show the total number of each business source for
listings and for accepted offers on the properties. Since the data is
located in two different tables, I'm creating two subqueries that I am
attempting to combine into a single query.

Each subquery will count the number of each business source in the
Property or Offer Table.

What I need to do is add those two fields together to get that total
number, but I run into a problem trying this. When I combine the
queries, it shows the data in the count columns, but the fields in the
calculated field remain blank.

The table structure for this is as follows:

A business sources table that has the two letter code and business
source name. It links to two other tables.

A business source field in the Property Table that identifies the source
the listing came from. This is linked with the ID field on the business
source table.

An offer source field in the Offer Table that identifies the business
source the offer came from. This is linked with the ID field on the
business source table.

Both the Property and Offer tables use a lookup to get the correct value
for the business source.

The subqueries are as follows:

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Count(Property.Business_Source_ID)
AS CountOfBusiness_Source_ID
FROM Business_Source LEFT JOIN (Property LEFT JOIN Offer ON
Property.Property_ID=Offer.Property_ID) ON
Business_Source.Business_Source_ID=Property.Business_Source_ID
GROUP BY Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Property.Property_Status,
Offer.Offer_Status
HAVING (((Property.Property_Status)="Fall-Through") AND
((Offer.Offer_Status)="A/O"));

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Count(Offer.Offer_Source_ID) AS
CountOfOffer_Source_ID
FROM (Business_Source LEFT JOIN Offer ON
Business_Source.Business_Source_ID = Offer.Offer_Source_ID) LEFT JOIN
Property ON Offer.Property_ID = Property.Property_ID
GROUP BY Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Property.Property_Status,
Offer.Offer_Status
HAVING (((Property.Property_Status)="Fall-Through") AND
((Offer.Offer_Status)="A/O"));

My Attempt to join these two queries together is done through the
following query:

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, [Business Source Query Part
2].CountOfBusiness_Source_ID, [Business Source Query Part
2A].CountOfOffer_Source_ID, [Business Source Query Part
2]![CountOfBusiness_Source_ID]+[Business Source Query Part
2A]![CountOfOffer_Source_ID] AS Total
FROM ([Business Source Query Part 2] RIGHT JOIN Business_Source ON
[Business Source Query Part 2].Business_Source_ID =
Business_Source.Business_Source_ID) LEFT JOIN [Business Source Query
Part 2A] ON Business_Source.Business_Source_ID = [Business Source Query
Part 2A].Business_Source_ID;

What am I doing wrong that results in the total column remaining blank,
and how can I correct it?

No one has any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top