Display rows with no data

I have a query which is grouped by year, facility name, and billing type and needs to display several columns of aggregate values for each of these, e.g. count of cases, sum of cases reviewed, average length of stay, etc. It would look like this:

2003 ABC Hospital Billed DRG 123 22 6.5
2003 ABC Hospital Billed Per Diem 23 0 4.2
2003 DEF Hospital Billed DRG 456 120 12.3
2003 DEF Hospital Billed Per Diem 56 10 2.1

However, if a facility does not have any Per Diem billings for example, how can I generate a row for it anyway? I cannot use a crosstab since I have multiple different values to display. I tried the following union query:

SELECT YR,FacilityName,BilledDRG,NumberOfCases,AverageLengthOfStay,CasesReviewed,CasesDenied,AvgOflos,[Billing Eligibility] FROM qryDistHIVCasesStatsDAC
UNION SELECT YR,FacilityName,BilledDRG,0,0,0,0,0,null FROM qryDistHIVCasesStatsDACDRG
UNION SELECT YR,FacilityName,BilledDRG,0,0,0,0,0,null FROM qryDistHIVCasesStatsDACPD
ORDER BY YR, FacilityName, BilledDRG;

where qryDistHIVCasesStatsDAC
is the original query,

qryDistHIVCasesStatsDACDRG shows the year, facility name, and "Billed DRG"
qryDistHIVCasesStatsDACPD shows the year, facility name, and "Billed Per Diem"

but then I would get e.g.
2003 ABC Hospital Billed DRG 123 22 6.5
2003 ABC Hospital Billed DRG 0 0 0
2003 ABC Hospital Billed Per Diem 23 0 4.2
2003 ABC Hospital Billed Per Diem 0 0 0
2003 GHI Hospital Billed DRG 456 120 12.3
2003 GHI Hospital Billed DRG 0 0 0
2003 GHI Hospital Billed Per Diem 0 0 0

what I want is:
2003 ABC Hospital Billed DRG 123 22 6.5
2003 ABC Hospital Billed Per Diem 23 0 4.2
2003 GHI Hospital Billed DRG 456 120 12.3
2003 GHI Hospital Billed Per Diem 0 0 0

In other words, for each year, each facility should have a row "Billed DRG" and a row "Billed Per Diem" and only one row of each.

Thanks for your assistance. I hope I have explained this adequately.

instead of union select, i would keep it to one select statement. what you need to learn is how to use the nz function.

for instance, if i have a=5, and b=6, so it follows that a+b=11. what if a is null and i do a+b=? it will be null and not show, because null can't be added to b. if you add nz(a) + nz(b) = 6, because now you are adding 0+6=6


see if you can apply this logic.

sam

SamDeMan,

Thanks for your reply. I used nz in textboxes on the report which is based on this query (the original query without union). It did not work. I think it would have if these rows appeared in the query with null values. But the rows do not appear in the query results at all. I am trying to find out how to get the entire row to appear in the query results or on the report.

maybe you'll get help from someone more experienced than me, but in the meantime, try this.

go to the first query, qryDistHIVCasesStatsDAC, and add nz to the following fields: BilledDRG,NumberOfCases,AverageLen gthOfStay,CasesReviewed,CasesDenied,AvgOflos
(i don't know what the billing eligibility is)

sam

You need to use a left join to get what you want. Create a query that retrieves all of your hospital names. Add this query into your qryDistHIVCasesStatsDACDRG and qryDistHIVCasesStatsDACPD. Join the Hospital Name query to your other tables in the query using a left join. This means that your query will return a record for every hospital name, even if there is no corresponding data. Then union these two queries as before. Use Nz() to turn the nulls into zeros or whatever you need.

Thanks for your suggestions. I got what I needed though, by putting the union query into another query which summed each field. I also changed the average length of stay field to Total LOS (sum of LOS) and calculated the average in the report.