Sharper Focus On Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK, so, for example,

SELECT [CB Budget ].SERVICE_TYPE,
Sum([QUARTER1_CHARGE]+[QUARTER2_CHARGE]+[QUARTER3_CHARGE]+[QUARTER4_CHARGE])
AS CBTotal
FROM [CB Budget ] LEFT JOIN ACTccs ON [CB Budget ].TO_CC = ACTccs.CC
GROUP BY [CB Budget ].SERVICE_TYPE;

is NOT what I want. I'd like the results where [CB Budget].TO_CC <> ACTccs.CC

I'm not sure that a LEFT JOIN quite works for this. But, what would be the
query syntax if I want the "difference?" That is, not what is represented by
a LEFT JOIN where the criteria is =. I hope I'm making myself clear.
 
Could you be more specific? I'm afraid just a WHERE statement doesn't help.

scubadiver said:
I think you need a "WHERE" statement after "FROM"

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


Ray S. said:
OK, so, for example,

SELECT [CB Budget ].SERVICE_TYPE,
Sum([QUARTER1_CHARGE]+[QUARTER2_CHARGE]+[QUARTER3_CHARGE]+[QUARTER4_CHARGE])
AS CBTotal
FROM [CB Budget ] LEFT JOIN ACTccs ON [CB Budget ].TO_CC = ACTccs.CC
GROUP BY [CB Budget ].SERVICE_TYPE;

is NOT what I want. I'd like the results where [CB Budget].TO_CC <> ACTccs.CC

I'm not sure that a LEFT JOIN quite works for this. But, what would be the
query syntax if I want the "difference?" That is, not what is represented by
a LEFT JOIN where the criteria is =. I hope I'm making myself clear.
Click to expand...
Click to expand...
 
yeah, sorry!

I am not an SQL expert so I don't quite understand code as it is read. I do
the query in "design" view and change to SQL.

If you look at the query in "design" view and add the following to the
criteria line of the "ACTccs.CC" field.

<> [CB Budget].TO_CC

Sorry if I am not making myself too clear.


--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


Ray S. said:
Could you be more specific? I'm afraid just a WHERE statement doesn't help.

scubadiver said:
I think you need a "WHERE" statement after "FROM"

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


Ray S. said:
OK, so, for example,

SELECT [CB Budget ].SERVICE_TYPE,
Sum([QUARTER1_CHARGE]+[QUARTER2_CHARGE]+[QUARTER3_CHARGE]+[QUARTER4_CHARGE])
AS CBTotal
FROM [CB Budget ] LEFT JOIN ACTccs ON [CB Budget ].TO_CC = ACTccs.CC
GROUP BY [CB Budget ].SERVICE_TYPE;

is NOT what I want. I'd like the results where [CB Budget].TO_CC <> ACTccs.CC

I'm not sure that a LEFT JOIN quite works for this. But, what would be the
query syntax if I want the "difference?" That is, not what is represented by
a LEFT JOIN where the criteria is =. I hope I'm making myself clear.
Click to expand...
Click to expand...
Click to expand...
 
is NOT what I want. I'd like the results where [CB Budget].TO_CC <> ACTccs.CC
Click to expand...

If you want records where there is a value in [CB Budget] but no corresponding
record in ACTccs, then you need a "frustrated outer join" query:

SELECT [CB Budget].SERVICE_TYPE,
Sum([QUARTER1_CHARGE]+[QUARTER2_CHARGE]+[QUARTER3_CHARGE]+[QUARTER4_CHARGE])
AS CBTotal
FROM [CB Budget ] LEFT JOIN ACTccs ON [CB Budget ].TO_CC = ACTccs.CC
WHERE ACTccs.CC IS NULL
GROUP BY [CB Budget ].SERVICE_TYPE;

This assumes that the quarter charges exist in the CB Budget table.

The logic is a bit convoluted. An INNER JOIN would return all records which
exist in both tables. A LEFT JOIN by itself will return all records in CB
Budget, regardless of whether there is a match in ACTccs; if there is a match,
then CC will be the matching value, and if there isn't, then CC will be NULL.
The IS NULL criterion selects only those records - the ones which don't have a
match.

John W. Vinson [MVP]
 
Back
Top