Join Issue

[BerkshireGuy]

Here is the problem I am having:

In a table I have the following fields:

Agency
ApplicationStatus
CountOfApplications

There are several application status: Paid, Declined, Reopened

Sometimes, an agency can have all three records, Paid, Declined or
Reopened. Or sometmes it may just have a Decline record.

What I am trying to do is return all three status (Paid,Declined,
Reopened) with their countofapplications. If one of the status isnt
there, then return the Status Name (Paid,Declined, Reopened) and the 0
for a count.

Any ideas?

Thanks

[Johnny Meredith]

I can't think of a way to do this with pure SQL (I played with putting
the 3 possible statuses into a table and using outer joins).

What you could do is write code to copy the data you needed into a
temporary table and query from that. That way, you could direct the
code to write out zeros where it makes sense.

Sorry, I know this isn't much help.

Johnny

[BerkshireGuy]

Johnny,

I tried the table and outer joins also. One would think that shoud
work.

I was doing the code, but figured there was an easier way through
queries.

Thanks!

-Brian

[lesperancer]

is 'applicationStatus' a table ?
if so, don't use any joins in your query, ie.

select tblAgency.agency, tblApplicationStatus.applicationStatus, 0 as
appCount
from tblAgency, tblApplicationStatus

this will give you a 'cartesian product', ie all combinations of agency
and status

now use this query (ie. qryAll) in a union query with your original
query
ie

select * from qryAll
union
select * from yourQuery

should get all agencies, all status with the correct count

[pietlinden]

Sure. Create a table or query that has Paid, Declined, Reopened in the
recordset (just 3 records, so you may need a DISTINCT clause). then
you can left join that to your normal table, and then group by agency
and application status. the outer join - show all records from the
Paid, Declined, Reopened table/query, and then the matching ones from
the table you have.

[jimfortune]

BerkshireGuy wrote:

Here is the problem I am having:

In a table I have the following fields:

Agency
ApplicationStatus
CountOfApplications

There are several application status: Paid, Declined, Reopened

Sometimes, an agency can have all three records, Paid, Declined or
Reopened. Or sometmes it may just have a Decline record.

What I am trying to do is return all three status (Paid,Declined,
Reopened) with their countofapplications. If one of the status isnt
there, then return the Status Name (Paid,Declined, Reopened) and the 0
for a count.

Any ideas?

Thanks

The first idea I had was to try subqueries. I don't know if the result
is what you are looking for but perhaps it will give you some ideas.
You've probably solved it by now anyway.

tblApplications
ApplicationID AgencyID ApplicationStatus
1 1 Paid
2 1 Declined
3 1 Reopened
4 2 Paid
5 2 Declined
6 3 Declined
7 3 Reopened
8 4 Reopened

qryCountPaidDeclinedReopened:
SELECT AgencyID, (SELECT COUNT(tblApplications.AgencyID) FROM
tblApplications AS A WHERE A.AgencyID = tblApplications.AgencyID AND
A.ApplicationStatus="Paid") AS PaidCount, (SELECT
COUNT(tblApplications.AgencyID) FROM tblApplications AS A WHERE
A.AgencyID = tblApplications.AgencyID AND
A.ApplicationStatus="Declined") AS DeclinedCount, (SELECT
COUNT(tblApplications.AgencyID) FROM tblApplications AS A WHERE
A.AgencyID = tblApplications.AgencyID AND
A.ApplicationStatus="Reopened") AS ReopenedCount FROM tblApplications
GROUP BY AgencyID;

! qryCountPaidDeclinedReopened:
AgencyID PaidCount DeclinedCount ReopenedCount
1 1 1 1
2 1 1 0
3 0 1 1
4 0 0 1

James A. Fortune