Sum of two fields from seperate queries (1 Viewer)

R

RyLane

Registered User.

Local time

Yesterday, 23:39

Joined

Feb 4, 2014

Messages

60

• Dec 16, 2014

• #1

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?

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 13:39

Joined

Jan 20, 2009

Messages

12,851

• Dec 16, 2014

• #2

The problem starts with your denormalized data structure.

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

 

R

RyLane

Registered User.

Local time

Yesterday, 23:39

Joined

Feb 4, 2014

Messages

60

• Dec 16, 2014

• #3

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.

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 13:39

Joined

Jan 20, 2009

Messages

12,851

• Dec 16, 2014

• #4

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.

 

R

RyLane

Registered User.

Local time

Yesterday, 23:39

Joined

Feb 4, 2014

Messages

60

• Dec 17, 2014

• #5

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?

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 13:39

Joined

Jan 20, 2009

Messages

12,851

• Dec 17, 2014

• #6

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

 

R

RyLane

Registered User.

Local time

Yesterday, 23:39

Joined

Feb 4, 2014

Messages

60

• Dec 18, 2014

• #7

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.

 

R

RyLane

Registered User.

Local time

Yesterday, 23:39

Joined

Feb 4, 2014

Messages

60

• Dec 18, 2014

• #8

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;