Query Total from two different tables

[Richard Davis]

I have two tables:

Table 1: Has agent information with primary Key of S/Q# and a Scheduled
number of calls.

Table 2: Has some of the same information no primary key but S/Q# can be
multiple entries and field called Calls Re-Allocated

I want to display the total from table 1 scheduled number of calls and Calls
Re-allocated if the toal is below 130

PROBLEM / ERROR: I can get the number from table 1 if the total is below
130, and when I run the query I get 36 records. But when I add table two and
join the field S/Q# I get no records.

Please help!

 

[JohnFol]

That means the value of S/Q# cannot be found in both tables.

 

[Richard Davis]

Yes table two right now is blank, however I want this data in Table 1 to be
included in the query even if there is no matching record in Table 2, how
would I do that?

 

[JohnFol]

Double click on the join line and you will get 3 options. the default option
means the field has to be in both, but the other 2 allow you to specify it
only needs to be in 1 table.

 

[Richard Davis]

Thanks John, but I still am not getting the total if there is a maching
record in the other table.

 

[Van T. Dinh]

1. It sounds to me that you want a set of 2 Queries:

* Q1: a Total / Group By Query based on Table2 to work out the
TotalCallsReallocated per [S/Q#]

* Q2: base on Table1 and Q1 with Left Outer Join from Table1 to Q1 with
linking Field [S/Q#].


2. You can combine the 2 Queries into 1 using the "derived Table". This is
fairly complex but the SQL String should be something like:

****Untested****
SELECT T1.[S/Q#], T1.ScheduledCalls, Q1.TotalCallsReallocated
FROM
[Table1] As T1 LEFT JOIN
(
SELECT T2.[S/Q#], Sum([CallsReallocated]) As TotalCallsReallocated
FROM [Table2] As T2
GROUP BY [S/Q#]
HAVING (Sum([CallsReallocated]) < 130)
)
As Q1 ON T1.[S/Q#] = Q1.[S/Q#]


3. [S/Q#] has special characters which force the use of the "name"
delimiters square brackets. Suggest yo change the Field name if it is still
possible.