Include all weekdays even if there is no data on that particular day

[klallen810]

I am trying to create a query that will give me the number of reports each employee did each day for the week. I have a query that pulls these results just fine, but there are some days where there are no reports submitted. I would like to still have those days listed in the query, with the number of reports at 0. I am going to use this query to export into a pivot table in excel. Currently, this is the pivot table that I am getting:

	Tue	Wed	Thu	Grand Total
D. Kassim		5		5
J. Prickett	6	3		9
M. Spence			1	1
Grand Total	6	8	1	15

And I want it to look like this:

	Mon	Tue	Wed	Thu	Fri	Grand Total
D. Kassim	0	0	5	0	0	5
J. Prickett	0	6	3	0	0	9
M. Spence	0	0	0	1	0	1
Grand Total	0	6	8	1	0	15

I have my weekly volume query that results in this information:

qWeekly_Volume
Day	Done By	Comparisons
Tue	J. Prickett	6
Wed	D. Kassim	5
Wed	J. Prickett	3
Thu	M. Spence	1

I tried making a table with Mon-Fri listed for each employee:

tWeeklyVolume
ID	DayOfWeek	EmpName	ComparisonsCompleted
11	Mon	J. Prickett
12	Tue	J. Prickett
13	Wed	J. Prickett
14	Thu	J. Prickett
15	Fri	J. Prickett
16	Mon	D. Kassim
17	Tue	D. Kassim
18	Wed	D. Kassim
19	Thu	D. Kassim
20	Fri	D. Kassim
21	Mon	M. Spence
22	Tue	M. Spence
23	Wed	M. Spence
24	Thu	M. Spence
25	Fri	M. Spence

And created a select query that joined the day and the employee name together and then displayed the number of comparisons completed on a particular day for a particular employee, with an iif(IsNull( statement that would return a 0 if the person did not complete any comparisons that day. This is my SQL:

SELECT tWeeklyVolume.DayOfWeek, tWeeklyVolume.EmpName, IIf(IsNull([qWeekly_Volume].[Comparisons]),0,[qWeekly_Volume].[Comparisons]) AS Comparisons



FROM tWeeklyVolume LEFT JOIN qWeekly_Volume ON (tWeeklyVolume.EmpName = qWeekly_Volume.[Done By]) AND (tWeeklyVolume.DayOfWeek = qWeekly_Volume.Day);

It keeps giving me the error: “The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.”

I have no idea where this error is coming from or how to fix it. I tried making this an update query that would update the ComparisonsCompleted column in the tWeeklyVolume table, but this doesn't work either. The error comes from the day to day join, not the employee name join. Does anyone have any suggestions?

[alansidman]

It would be more beneficial if you provided sample data of your table. If you want to upload your database for analysis, click on the Go Advanced button and follow the wizard