Coalesce

[kbreiss]

I'm new with the term of Coalesce, but I am wanting to include all the values from the left table even if they are null. Does access support this "Coalesce"

What I'm doing is taking a facility table that lists all of the facility IDs. I am then linking it to a Monthly table that gives counts for each facility for that month.

I would like to use the facility table as my base table. So far example, if in my base table I have fac_id 200 and for the the month of January in my monthly table there are none for fac_id 200 I would like for it to display FAC_ID 200 0 (I want it to display zero for that facility)


Any help would be much appreciated.

Thanks in advance,
Kacy
________
WENDIE 99

 

[FoFa]

Create a query with your two tables, drag/drop the fields that join the two tables, right click on the line that forms the join and select which option you want (outter join is what you are doing).

 

[kbreiss]

Here is the query that I am using. I did that with the outer join, but still it does not give me all the records from my base table.

SELECT FACILITIES.FAC_ID, COALESCE([2001 HEADER].[HEADER COUNT],0)
FROM FACILITIES LEFT JOIN [2001 HEADER] ON FACILITIES.FAC_ID = [2001 HEADER].FAC_ID
WHERE ((([2001 HEADER].MONTH)=200101));
________
Oxygen Vaporizer