Perplexing Query Results

[That Crazy Hockey Dood]

Good Morning Folks..

I have a problem that I cannot wrap my head around. Please evaluate the
following SQL:

SELECT
Sum([EOSetups]+[FBOrders]+[NewAccounts]+[NCR]+[OTML]+[Pieces]+[ShowsPacked]+[ILSQuotes]+[INTLQuotes]+[DoNotExceed]+[Emails]+[Orders]+[NACallbacks]+[KeyAcctSales]+[Notes]+[FollowUp]+[ICSIndexed]+[Mail]+[CreditProcessing]+[ConsumerCatalogs]+[ReturnedMail]+[PRP]+[HeldOrders]+[VCommerce]+[QAEdits]+[QMEvaluations]+[eBooks])
AS AdminTotal,
Sum([CallCenterCalls]+[OperationsCalls]+[AdministrationCalls]+[CostcoCalls]+[CSEmail]+[ILSEmail]+[INTLEmail]+[FaxOrders]+[EmlOrders]+[ILSFaxandEmailOrders]+[CDFEmail]+[CustCareEmail])
AS CallCenterTotal,
Sum([ProcessedTracers]+[OpsEmail]+[PIF]+[WIF]+[FaxedPL]+[Callbacks]+[CALetters]+[NoteCodeChanges]+[ShipdeskFU]+[NotesandFU]+[RSPONSEFU]+[FrtQuotes]+[HFForms]+[OSForms]+[CriticalCare]+[Returns]+[CCOPStoIC]+[PTOExpedited]+[PTOCancels]+[ACO]+[BNAirReq]+[SalesSupportCalls]+[SalesSupportEmail])
AS OpsTotal,
Sum([WorkOrders]+[BinderyShipments]+[BPNotes]+[FollowUps]+[EmailOrders]+[EmailCS]+[iPageOrders]+[TaschenYourPay]+[Allocations]+[IPSNewAcct]+[IPSFax]+[IPSEmail]) AS PubCareTotal, Sum(tblPayroll.Payroll) AS SumOfPayroll
FROM [Admin Activity_tbl], [Call Center Activity_tbl], CCOPS_tbl, [Pub
Care_tbl], tblPayroll
WHERE ((([Admin Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND (([Call
Center Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND
((CCOPS_tbl.Yr)=[Forms]![Monthly CPT]![Yr]) AND (([Pub
Care_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND
((tblPayroll.Yr)=[Forms]![Monthly CPT]![Yr]));

Here is what should be happening. I should be getting back the summed
results for each "container". The results are not even close to what they
should be. What really throws me for a loops is that the following SQL
returns the correct results:

SELECT
Sum([EOSetups]+[FBOrders]+[NewAccounts]+[NCR]+[OTML]+[Pieces]+[ShowsPacked]+[ILSQuotes]+[INTLQuotes]+[DoNotExceed]+[Emails]+[Orders]+[NACallbacks]+[KeyAcctSales]+[Notes]+[FollowUp]+[ICSIndexed]+[Mail]+[CreditProcessing]+[ConsumerCatalogs]+[ReturnedMail]+[PRP]+[HeldOrders]+[VCommerce]+[QAEdits]+[QMEvaluations]+[eBooks]) AS AdminTotal
FROM [Admin Activity_tbl]
WHERE ((([Admin Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]));

This tells me that I have something wrong with the first query. I guess
that I could use separate queries if need be but that doesn't seem efficient
to me. Help.

Thanks,
Jim

 

[Stefan Hoffmann]

hi,

FROM [Admin Activity_tbl], [Call Center Activity_tbl], CCOPS_tbl, [Pub
Care_tbl], tblPayroll
WHERE ((([Admin Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND (([Call
Center Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND
((CCOPS_tbl.Yr)=[Forms]![Monthly CPT]![Yr]) AND (([Pub
Care_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND
((tblPayroll.Yr)=[Forms]![Monthly CPT]![Yr]));
This tells me that I have something wrong with the first query. I guess
that I could use separate queries if need be but that doesn't seem efficient
to me. Help.

You have a full join in your query without further restrictions in the
WHERE clause.

1. Create a new query, switch to the design view.
2. Add your tables.
3. Build the relationships between these tables.
4. Activate the grouping and enter your Sum() formula.

The relation should be at least between the year fields.


mfG
--> stefan <--

 

[Michel Walsh]

Make separate queries.


The GROUP/aggregate action logically occurs after the JOIN. In the first
query, your FROM clause does not ONLY mentions which tables will be
involved, but HOW they are to be involved between themselves, and the way it
is done, from the supplied statement, that generates a huge abominable CROSS
join ... which is not what you want at all (from what I understand).

Separate queries will (probably) run, in total, much (human perceptible)
faster than what you experienced with the actual (wrong) first query you
presented.



Vanderghast, Access MVP

Good Morning Folks..

I have a problem that I cannot wrap my head around. Please evaluate the
following SQL:

SELECT
Sum([EOSetups]+[FBOrders]+[NewAccounts]+[NCR]+[OTML]+[Pieces]+[ShowsPacked]+[ILSQuotes]+[INTLQuotes]+[DoNotExceed]+[Emails]+[Orders]+[NACallbacks]+[KeyAcctSales]+[Notes]+[FollowUp]+[ICSIndexed]+[Mail]+[CreditProcessing]+[ConsumerCatalogs]+[ReturnedMail]+[PRP]+[HeldOrders]+[VCommerce]+[QAEdits]+[QMEvaluations]+[eBooks])
AS AdminTotal,
Sum([CallCenterCalls]+[OperationsCalls]+[AdministrationCalls]+[CostcoCalls]+[CSEmail]+[ILSEmail]+[INTLEmail]+[FaxOrders]+[EmlOrders]+[ILSFaxandEmailOrders]+[CDFEmail]+[CustCareEmail])
AS CallCenterTotal,
Sum([ProcessedTracers]+[OpsEmail]+[PIF]+[WIF]+[FaxedPL]+[Callbacks]+[CALetters]+[NoteCodeChanges]+[ShipdeskFU]+[NotesandFU]+[RSPONSEFU]+[FrtQuotes]+[HFForms]+[OSForms]+[CriticalCare]+[Returns]+[CCOPStoIC]+[PTOExpedited]+[PTOCancels]+[ACO]+[BNAirReq]+[SalesSupportCalls]+[SalesSupportEmail])
AS OpsTotal,
Sum([WorkOrders]+[BinderyShipments]+[BPNotes]+[FollowUps]+[EmailOrders]+[EmailCS]+[iPageOrders]+[TaschenYourPay]+[Allocations]+[IPSNewAcct]+[IPSFax]+[IPSEmail])
AS PubCareTotal, Sum(tblPayroll.Payroll) AS SumOfPayroll
FROM [Admin Activity_tbl], [Call Center Activity_tbl], CCOPS_tbl, [Pub
Care_tbl], tblPayroll
WHERE ((([Admin Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND (([Call
Center Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND
((CCOPS_tbl.Yr)=[Forms]![Monthly CPT]![Yr]) AND (([Pub
Care_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND
((tblPayroll.Yr)=[Forms]![Monthly CPT]![Yr]));

Here is what should be happening. I should be getting back the summed
results for each "container". The results are not even close to what they
should be. What really throws me for a loops is that the following SQL
returns the correct results:

SELECT
Sum([EOSetups]+[FBOrders]+[NewAccounts]+[NCR]+[OTML]+[Pieces]+[ShowsPacked]+[ILSQuotes]+[INTLQuotes]+[DoNotExceed]+[Emails]+[Orders]+[NACallbacks]+[KeyAcctSales]+[Notes]+[FollowUp]+[ICSIndexed]+[Mail]+[CreditProcessing]+[ConsumerCatalogs]+[ReturnedMail]+[PRP]+[HeldOrders]+[VCommerce]+[QAEdits]+[QMEvaluations]+[eBooks])
AS AdminTotal
FROM [Admin Activity_tbl]
WHERE ((([Admin Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]));

This tells me that I have something wrong with the first query. I guess
that I could use separate queries if need be but that doesn't seem
efficient
to me. Help.

Thanks,
Jim

 

[That Crazy Hockey Dood]

Stefan..

Thank you sir. That is what I was doing wrong and suspected that the lack
of a relationship between fields was an issue. Funny thing though.. Adding
a relationship between the Yr field did not work. It took the addition of
the Mth field to make this work correctly. Any additional thoughts/insight
on why that may be the case?

Thanks,
Jim
--
If it works then you are doing something right!!

hi,

FROM [Admin Activity_tbl], [Call Center Activity_tbl], CCOPS_tbl, [Pub
Care_tbl], tblPayroll
WHERE ((([Admin Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND (([Call
Center Activity_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND
((CCOPS_tbl.Yr)=[Forms]![Monthly CPT]![Yr]) AND (([Pub
Care_tbl].Yr)=[Forms]![Monthly CPT]![Yr]) AND
((tblPayroll.Yr)=[Forms]![Monthly CPT]![Yr]));
This tells me that I have something wrong with the first query. I guess
that I could use separate queries if need be but that doesn't seem efficient
to me. Help.

You have a full join in your query without further restrictions in the
WHERE clause.

1. Create a new query, switch to the design view.
2. Add your tables.
3. Build the relationships between these tables.
4. Activate the grouping and enter your Sum() formula.

The relation should be at least between the year fields.


mfG
--> stefan <--

 

[John W. Vinson]

Here is what should be happening. I should be getting back the summed
results for each "container". The results are not even close to what they
should be.

Might some of these fields be NULL? If so, any arithmetic expression involving
a NULL will return NULL - so if there ia (e.g.) a record with a NULL value in
any one of the fields you're adding, that record will not be included in the
SUM.

You can wrap each field in a call to the NZ() - Null To Zero - function to get
around this, e.g.

(A + B + C)

will be NULL if any of the fields is null, but

(NZ(A) + NZ(B) + NZ(C))

will give the sum of all the non-null values.