Help Combining Two Queries...

S

Sean Massey

I'm nearing the end of my project, and I have a query problem that is
holding up one report - the Business Sources report.

I work in a real estate company that tracks their business sources.
They have an annual report they run that provides the total number of
listings and accepted offers for each business source. The problem I am
running into is that the report doesn't track whether the business
source was from a listing or an offer - it just provides a total.

The problem I'm having is combining two queries to get the total result.
The information I need is spread between three linked tables:
Business Sources Table - contains business source ID and description
Property Table - Listing Source ID
Offer Table - Offer Source ID

The Offer Source ID and Listing Source ID are linked to the Business
Source ID.

I have two queries constructed that gather the information I need from
the Property and Offer tables. The queries count the number of each
business source based on the property status.

The final step of this is to create a query that gives me the total
number of business sources for that property status by adding the counts
from the sub-queries. Unfortunately, the queries I have tried all have
a blank total column - for some reason, Access appears to not want to
add the data up.

How can I get this query to work so I can finish the report?

Thank you in advance,

Sean
 
O

OfficeDev18 via AccessMonster.com

Sean,

Are these three queries in VBA code or are they in Access' Query Design
screen? Sometimes putting them in the Design Screen gives the query a certain
amount of visibility that's missing in non-visual VBA. If they are already in
the Query Design screen and they don't work, please copy and paste the SQL so
we can take a look at it.

Sam
 
S

Sean Massey

Sam,

The queries are built in Design View, and the SQL code is as follows:

First Query (selects business sources from Property table where status
is "fall-Through" and Offer Status is "Accepted Offer":

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Property.Business_Source_ID,
Property.Property_Status
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.Business_Source_ID,
Property.Property_Status, Offer.Offer_Status
HAVING (((Property.Property_Status)="Fall-Through") AND
((Offer.Offer_Status)="A/O"));

Second Query (selects business source from offer table where property
status is "fall-through" and offer status is "accepted offer.":

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

The query where I try to bring the two above together and create a field
where I add the business sources together is:

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, [Business Source Query Part
2_Crosstab].[Fall-Through], [Business Source Query Part
2A_Crosstab].[Fall-Through], ([Business Source Query Part
2_Crosstab]![Fall-Through]+[Business Source Query Part
2A_Crosstab]![Fall-Through]) AS Total
FROM ([Business Source Query Part 2_Crosstab] RIGHT JOIN Business_Source
ON [Business Source Query Part 2_Crosstab].Business_Source_ID =
Business_Source.Business_Source_ID) LEFT JOIN [Business Source Query
Part 2A_Crosstab] ON Business_Source.Business_Source_ID = [Business
Source Query Part 2A_Crosstab].Business_Source_ID;

Sean
 
S

Sean Massey

Oops...

I posted the wrong queries. Those were just some test queries I had
done to see if I could do it as a cross-tab.

Here are the ones I was actually having trouble with:

Query 1 (counts business sources from Property table where status is
"fall-Through" and Offer Status is "Accepted Offer"):

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"));

Query Two (counts business source from offer table where property status
is "fall-through" and offer status is "accepted offer."):

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 LEFT JOIN Property ON
Offer.Property_ID = Property.Property_ID) ON
Business_Source.Business_Source_ID = Offer.Offer_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"));

Final 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 LEFT JOIN [Business Source Query Part 2] ON
Business_Source.Business_Source_ID = [Business Source Query Part
2].Business_Source_ID) LEFT JOIN [Business Source Query Part 2A] ON
Business_Source.Business_Source_ID = [Business Source Query Part
2A].Business_Source_ID;
 
S

Sean Massey

I posted the wrong queries here. Please disregard this part of the thread.

Sean said:
Sam,

The queries are built in Design View, and the SQL code is as follows:

First Query (selects business sources from Property table where status
is "fall-Through" and Offer Status is "Accepted Offer":

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, Property.Business_Source_ID,
Property.Property_Status
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.Business_Source_ID,
Property.Property_Status, Offer.Offer_Status
HAVING (((Property.Property_Status)="Fall-Through") AND
((Offer.Offer_Status)="A/O"));

Second Query (selects business source from offer table where property
status is "fall-through" and offer status is "accepted offer.":

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

The query where I try to bring the two above together and create a field
where I add the business sources together is:

SELECT Business_Source.Business_Source_ID,
Business_Source.Business_Source_Name, [Business Source Query Part
2_Crosstab].[Fall-Through], [Business Source Query Part
2A_Crosstab].[Fall-Through], ([Business Source Query Part
2_Crosstab]![Fall-Through]+[Business Source Query Part
2A_Crosstab]![Fall-Through]) AS Total
FROM ([Business Source Query Part 2_Crosstab] RIGHT JOIN Business_Source
ON [Business Source Query Part 2_Crosstab].Business_Source_ID =
Business_Source.Business_Source_ID) LEFT JOIN [Business Source Query
Part 2A_Crosstab] ON Business_Source.Business_Source_ID = [Business
Source Query Part 2A_Crosstab].Business_Source_ID;

Sean

Sean,

Are these three queries in VBA code or are they in Access' Query Design
screen? Sometimes putting them in the Design Screen gives the query a
certain
amount of visibility that's missing in non-visual VBA. If they are
already in
the Query Design screen and they don't work, please copy and paste the
SQL so
we can take a look at it.

Sam
Click to expand...
Click to expand...
 
O

OfficeDev18 via AccessMonster.com

Sean,

I had a similar problem recently, and I had to make a table to fix it. It's
not hard to modify your two preliminary queries to append to a table (the
first time you run the first query, it'll have to be a create table query;
after the first run, you'll want to change it to an append query) that holds
the accumulated data. Then you can modify the upper query to query the table,
instead of querying the two sub-queries.

Of course, every time the queries run, you'll want to wipe out the previous
contents of the table. It should be fairly easy to run from VBA.

There's another way. You can modify your lower queries to add a Boolean field
identifying the records that adhere to the criteria. Your upper query can
then simply have a WHERE clause that will look for a True in that field.

Hope this helps,

Sam

Sean said:
Oops...

I posted the wrong queries. Those were just some test queries I had
done to see if I could do it as a cross-tab.

Here are the ones I was actually having trouble with:

Query 1 (counts business sources from Property table where status is
"fall-Through" and Offer Status is "Accepted Offer"):

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"));

Query Two (counts business source from offer table where property status
is "fall-through" and offer status is "accepted offer."):

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 LEFT JOIN Property ON
Offer.Property_ID = Property.Property_ID) ON
Business_Source.Business_Source_ID = Offer.Offer_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"));

Final 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 LEFT JOIN [Business Source Query Part 2] ON
Business_Source.Business_Source_ID = [Business Source Query Part
2].Business_Source_ID) LEFT JOIN [Business Source Query Part 2A] ON
Business_Source.Business_Source_ID = [Business Source Query Part
2A].Business_Source_ID;
Click to expand...
 

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

Similar Threads

Problem with Calculated Fields in Query... 1
Combining Date queries 1
Counting multiple memberships 1
Combine Duplicates in Query 0
Need to Count by two different criteria in same Totals query 4
Returning multiply entries on Query 7
Report Summary 2
Need description instead of code to show 3

Top