Sum of two fields from seperate queries

[RyLane]

I have a sign up form where people select their name for the time slot they want, there are 2 spots open per time slot, so I have Interviewer A and Interviewer B. Records can then either just be a name in A or a name in A and B.
I would like to run a report that shows how many times all the interviewers have signed up in a date range.
I can do this individually by looking up one name at a time with this query:

SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted
FROM Interview_Schedule
WHERE (((Interview_Schedule.Interviewer_B)=[Forms]![Main]![NavigationSubform]![cboSup])) OR (((Interview_Schedule.Interviewer_A)=[Forms]![Main]![NavigationSubform]![cboSup]) AND ((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]));

​

My question is how do I get a report to show all agents at once with their own interview count?
I can make 2 separate queries to look up each field, for interviewer A and Interviewer B, then run a join query, but this results in 2 counts for each agent and I can't sum the two totals together....

SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted, Interview_Schedule.Interviewer_A
FROM Interview_Schedule
WHERE (((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]))
GROUP BY Interview_Schedule.Interviewer_A;
UNION SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted, Interview_Schedule.Interviewer_B
FROM Interview_Schedule
WHERE (((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]))
GROUP BY Interview_Schedule.Interviewer_B;

​

Any ideas?

 

[Galaxiom]

The problem starts with your denormalized data structure.

Use two records per time slot with an Interviewer field field indicating A or B.

 

[RyLane]

That would be too easy! haha.
I only did it this way because I'm modelling the form on an old excel sheet that was being used before and wanted to keep the layout the same (People hate change).
I wanted to see if it's possible to keep it the same way and still accomplish what I need. But yes, your suggestion would solve my problem.

 

[Galaxiom]

Incorrect structure almost invariably leads to an endless string of kludges such as the one you are contemplating now.

People get used to change when they have no alternative.

Moreover the layout of the user interface should never override the correct structure of the data. If you really must keep the layout the same then build the form to simulate it by using subforms.

 

[RyLane]

While I appreciate all that and already have various versions of it working,
I'm still curious if anyone knows of a solution to my initial question, how to sum the count of 2 fields from seperate queries?

 

[Galaxiom]

Union All the data from the two fields first, then apply the count.

 

[RyLane]

And therein lies my probelm. How do I apply a count in a union query? I'm not an SQL person and normally just build my queries in design mode but Unions don't have that luxury.

 

[RyLane]

Well I discovered subqueries and seem to have it working with this:

SELECT Sum(Interview_Schedule.Completed)*-1 AS Completed, Count(Interview_Schedule.Completed) AS SignedUp, X.Interview_Schedule.Interviewer_A
FROM (SELECT Interview_Schedule.ID, Interview_Schedule.Completed, Interview_Schedule.Interviewer_A
FROM Interview_Schedule
WHERE (((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]))
UNION ALL SELECT Interview_Schedule.ID, Interview_Schedule.Completed, Interview_Schedule.Interviewer_B
FROM Interview_Schedule
WHERE (((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]))) AS X
GROUP BY X.Interview_Schedule.Interviewer_A;