Getting query to show 0's if no data was found

[BRZ-Ryan]

I plugged your queries into my database and I ended up with 4 NYC's, 4 Atlanta's, and 2 LA's.

I was able to reproduce the issue in the test DB but on a smaller scale by adding some data into the data tables:


Result:

LocationName	RepType	AvgOfNewA1	AvgOfNewC	AvgOfNewH	AvgOfNewD1	AvgOfNewE1	NewC	NewW	NewH
Atlanta	Type1	0	0	0	0	0	0	0	0
Atlanta	Type1	323	0	0	116	27.5	0	0	0
Atlanta	Type2	22	21	132	23	23	21	878	132
LA	Type1	23	45	123	43	34	45	544	123
LA	Type2	34	0	0	546	56	0	0	0
NYC	Type1	5	200	6545	4	4	200	545	6545
NYC	Type2	100	300	231	2	2	300	321	231

tblAgents:

ID	AgentName	RepType	OfficeID	SupervisorID
380	Mike Smith	1	1	1
381	John Doe	2	1	1
382	Tom Jones	1	2	1
383	Adam Reynolds	2	2	1
385	Paul Hodgkins	1	3	1
386	Barry Thomas	2	3	1
387	Bernie Patel	1	2	1

tblEntries

ID	AgentID	TimePeriod	A1	B1	C1	D1	E1
28	380	Jan 2015	5	10	4	4	4
30	381	Jan 2015	100	200	2	2	2
31	382	Jan 2015	100	200	1	1	1
32	383	Jan 2015	22	23	32	23	23
33	385	Jan 2015	23	34	656	43	34
34	386	Jan 2015	34	4563	56	546	56
35	387	Jan 2015	546	156	51	231	54

tblEfficiency

ID	AgentID	TimePeriod	C	W	H
4598	380	Jan 2015	200	545	6545
4599	381	Jan 2015	300	321	231
4601	383	Jan 2015	21	878	132
4603	385	Jan 2015	45	544	123

[BRZ-Ryan]

I'd also like to only average values > 0, DAvg() I think works well for this, right?

[BRZ-Ryan]

Finally figured it out after hours of trial and error. Thanks for your time!