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

[BRZ-Ryan]

I am writing a query that summarizes averages from multiple tables. We have 3 office locations and 3 types of agents, I need the table to display averages for data that is in the tables, and there is no data for a particular office\agent type, show 0 in that location.



ie. NYC\Agent 2 has no data, same for Atlanta Agent 2, and LA Agent2 and Agent3.

NYC Agent1 34 354 33
NYC Agent2 0 0 0
NYC Agent3 45 664 44
Atlanta Agent1 0 454 34
Atlanta Agent2 0 0 0
Alanta Agent3 345 545 4
LA Agent1 34 35 656
LA Agent2 0 0 0
LA Agent3 0 0 0

Currently, if no data exists it will not show the location\agent type line at all:
NYC Agent1 34 354 33
NYC Agent3 45 664 44
Atlanta Agent1 0 454 34
Alanta Agent3 345 545 4
LA Agent1 34 35 65

I remember in the past reading something to the effect of creating a skeleton table with the above layout and make the query pick from the skeleton table if no valid data can be found from the averaging query.

I'm ok at SQL, just having a hard time with this one. I can write the query, I just need a little guidance. Please let me know if any additional info is needed.

What I have so far:

SELECT tblOffices.ID, tblRepTypes.ID, tblOffices.LocationName, Avg(A) AS AAvg, Avg(C) AS CAvg, Avg(H) AS HAvg 
FROM (((tblEfficiency INNER JOIN tblAgents 
ON tblEfficiency.AgentID = tblAgents.ID)
INNER JOIN tblOffices 
ON tblAgents.OfficeID = tblOffices.ID)
INNER JOIN tblRepTypes 
ON tblRepTypes.ID = tblAgents.RepType)
INNER JOIN tblEntries 
ON tblEntries.AgentID = tblAgents.ID
WHERE (Month(tblEfficiency.TimePeriod)=Month(forms!frmOfficeAvgs!cmbTimePeriod)) And (Year(tblEfficiency.TimePeriod)=Year(forms!frmOfficeAvgs!cmbTimePeriod)) And tblAgents.OfficeID=tblOffices.ID
GROUP BY tblOffices.ID, tblRepTypes.ID, tblOffices.LocationName; 

[lfpm062010]

Use "LEFT JOIN" and "NZ" should solve your issue.

[BRZ-Ryan]

Use "LEFT JOIN" and "NZ" should solve your issue.

LEFT JOIN with my skeleton table, or LEFT JOIN instead if INNER JOIN as I have above?

[lfpm062010]

What is your Skeleton table?

I think if you replace the "INNER JOIN" with "LEFT JOIN" should work.

The "LEFT JOIN" will give you the missing Agents. The "NZ" on the "Avg" will give you 0 instead of NULL.

If you still have issue, can you post your database for eval. Remove any confidential information.

[BRZ-Ryan]

Not working. Skeleton table is below, hoping to pull in those 0's if no data is found for avging for that location\agent type combo. I'll post db file in a few once I get a chance to clean it up.

ID	OfficeID	Location	RepType	A	C	H
1	1	NYC	1	0	0	0
2	1	NYC	2	0	0	0
3	2	Atlanta	1	0	0	0
4	2	Atlanta	2	0	0	0
5	3	LA	1	0	0	0
6	3	LA	2	0	0	0

[BRZ-Ryan]

Ok it's partially working now, but it's leaving out one office\agent type combo for some reason. (Location=LA/Agent type=1).

This I believe is due to no agents defined as type=1 from LA. Can I get these to display?

It has to always display all offices and agent types, even if no agents are defined for that location OR no data found for averaging. Basically if all averaging data was empty and no agents defined, it should result in my skeleton table.

[BRZ-Ryan]

I've found that it's leaving the group out when there are no agents defined for that type\office OR there is no cooresponding data to be averaged for an agent in that combo. How can I always include office\agent type, then find avg\0 for the data?

[lfpm062010]

Without knowing how your tables are structured, it is difficult to make any recommendation.

[BRZ-Ryan]

Here's the query I have so far, I had to remove it from the file to make it small enough to post. When it prompts you for time period, enter "jan 2015"

SELECT tblOffices.LocationName, tblRepTypes.RepType, Nz(Avg([C]),0), Nz(Avg([W]),0), Nz(Avg([H]),0), Nz(Avg([A1]),0), Nz(Avg([B1]),0), Nz(Avg([C1]),0), Nz(Avg([D1]),0) AS Expr1, Nz(Avg([E1]),0) AS Expr2
FROM (((tblEfficiency LEFT JOIN tblAgents ON tblEfficiency.AgentID = tblAgents.ID) LEFT JOIN tblOffices ON tblAgents.OfficeID = tblOffices.ID) LEFT JOIN tblRepTypes ON tblAgents.RepType = tblRepTypes.ID) LEFT JOIN tblEntries ON tblAgents.ID = tblEntries.AgentID
WHERE (((Month([tblEfficiency].[TimePeriod]))=Month([forms]![frmOfficeAvgs]![cmbTimePeriod])) AND ((Year([tblEfficiency].[TimePeriod]))=Year([forms]![frmOfficeAvgs]![cmbTimePeriod])) AND ((tblAgents.OfficeID)=[tblOffices].[ID]))
GROUP BY tblOffices.LocationName, tblRepTypes.RepType; 

[lfpm062010]

What is the expected output for the Test Copy?

What is the value for [forms]![frmOfficeAvgs]![cmbTimePeriod]? (I used 1/1/2015).

[BRZ-Ryan]

The times used throughout the DB is month\year, so just enter Jan 2015 when prompted. That combo contains 2-3 years of month\year combinations.

[BRZ-Ryan]

What is the expected output for the Test Copy?

See post 5 above. If no data is there for averaging, it should look like that (ie. tblEntries and tblEfficiency were empty aka no avg data). When average data is available, there should be an average instead of 0 for that location. The layout should always be constant though, always showing all 3 offices, and both agent types...total of 6 rows.

[BRZ-Ryan]

Sorry double post

[lfpm062010]

Test - Copy.zip
See attached!

I created a 3 queries:

1) qry_AllOfficeRepLoc: List all the available Office and Rep Locations.
2) qry_AllAgents: List all the possible Agents and its Efficient and Entries data and Time Period.
3) qry_AllAvgAgents: List all the Avg for Time Period.

[BRZ-Ryan]

Test - Copy.zip
See attached!

I created a 3 queries:

1) qry_AllOfficeRepLoc: List all the available Office and Rep Locations.
2) qry_AllAgents: List all the possible Agents and its Efficient and Entries data and Time Period.
3) qry_AllAvgAgents: List all the Avg for Time Period.

I appreciate the effort I'll let you know how it works out! Thanks