Yikes! My advice is to divide and conquer; the query may simply be too complex. Create queries that pull together some of the totals, then pull those into the original query replacing the tables that originally had those fields. Either that, or you need to look at normalizing your data, creating a related table that holds some of the fields.
Tobi (with an eye, not a why!) -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 Sent: Thursday, September 15, 2005 12:33 PM To: [email protected] Subject: [AccessDevelopers] Re: Message in Query #Error Toby, here is the SQL view. I think I was to paste this info here. The results in TotalCom is #Error. SELECT Tbl_CSCPricingAdvice.ProjectNo, Tbl_CSCPricingAdvice.MemberN0, Tbl_CSCPricingAdvice.Invoice_No, [NNU_Total]+[SP_Total]+[SS_Total]+[SHD_Total]+[CPPE_Total]+ [CMSSHD_Total]+[CMSNTP_Total]+[CMSCE_Total]+[ECMD_Total]+[CP_Total]+ [Matrix_Total]+[SL_Total]+[CPS_Total]+[RR_Total]+[ED_Total]+ [DA_Total]+[HCCF_Total]+[Beacon_Total]+[Beacon2_Total]+ [Beacon3_Total]+[CS_Total]+[CS2_Total]+[CS3_Total]+[ECMD1_Total]+ [ECMD2_Total]+[ECMD3_Total]+[ECMDE_Total]+[BNI_Total]+[BUP_Total]+ [Archive_Total]+[FFFO_Total]+[NCOA_Total]+[RSL_Total]+[RSDE_Total]+ [RSD_Total]+[RSAP_Total]+[Other_Total]+[Other2_Total] AS ProjTotal, Tbl_CSCPricingAdvice.NotEquifaxPro, [ProjTotal]+[NotEquifaxPro] AS AdjTotal, [AdjTotal]*1 AS GrandTotal, Tbl_CSCPricingAdvice.AM_SSNumber, Tbl_AccountManager.AM_Name, Tbl_CSCPricingAdvice.Status, Tbl_Company.Location, Tbl_Company.Vertical, Tbl_CSCPricingAdvice.AM1_Name, Tbl_CSCPricingAdvice.AM2_Name, Commissions.GrossBilling1, Commissions.ListAmount1, IIf([Grossbilling1]/[ListAmount1]>=1,1,(IIf ([Grossbilling1]>0,Format([Grossbilling1]/[ListAmount1],"#.##"),0))) AS CommissionNo1, IIf([Grossbilling1]>0,Format([CommissionNo1] *0.05,"#.###"),0) AS CommissionPer1, IIf([Grossbilling1]>0,IIf ([GrossBilling1]*[CommissionPer1]>5000,5000,Format([GrossBilling1]* [CommissionPer1],"$#.##")),0) AS ECMSCommission1, Commissions.GrossBilling2, Commissions.ListAmount2, IIf ([Grossbilling2]>0,Format([Grossbilling2]/[ListAmount2],"#.##"),0) AS CommissionNo2, IIf([Grossbilling2]>0,Format([CommissionNo2] *0.05,"#.###"),0) AS CommissionPer2, IIf([Grossbilling2]>0,IIf ([GrossBilling2]*[CommissionPer2]>5000,5000,Format([GrossBilling2]* [CommissionPer2],"$#.##")),0) AS ECMSCommission2, Tbl_AccountManager.AM_Location, Tbl_AccountManager.AM_Vertical, Commissions.Comm_MonthEnd, Commissions.DateSubmitted, Tbl_CSCPricingAdvice.AM2_location, Tbl_CSCPricingAdvice.AM2_Vertical, Tbl_Vertical.Vertical_Desc, Tbl_Location.Location_Desc, Commissions.DOS_ApprovalAM1, Commissions.DOS_ApprovalAM2, Tbl_CSCPricingAdvice.ProjectDesc, [ECMSCommission1]+[ECMSCommission2] AS TotalCom FROM (Tbl_Location INNER JOIN (Tbl_Vertical INNER JOIN Tbl_AccountManager ON Tbl_Vertical.Vertical_No = Tbl_AccountManager.AM_Vertical) ON Tbl_Location.Location_No = Tbl_AccountManager.AM_Location) INNER JOIN ((Tbl_CSCPricingAdvice INNER JOIN Tbl_Company ON Tbl_CSCPricingAdvice.MemberN0 = Tbl_Company.[Member#]) LEFT JOIN Commissions ON Tbl_CSCPricingAdvice.ProjectNo = Commissions.ProjectNo) ON Tbl_AccountManager.AM_SSNumber = Tbl_CSCPricingAdvice.AM_SSNumber WHERE (((Tbl_CSCPricingAdvice.Invoice_No) Is Null)); --- In [email protected], "Hoffman, Tobi K \(DYS\)" <[EMAIL PROTECTED]> wrote: > Sorry, what I think you need is: > > TotalCom: [Commission1]+[Commission2} > > (I was thinking in terms of forms rather than queries.) See if this > works. If it doesn't, you could get the SQL view up for the query and > let us see that. > > Tobi > > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > Sent: Thursday, September 15, 2005 11:17 AM > To: [email protected] > Subject: [AccessDevelopers] Re: Message in Query #Error > > > Tobi, > I tried what you suggested but still got #error in the result field > TotalCom in the query. What I did was right click the Field name on > the form (Commission1)and entered Txt in front of the field > name which changed to TxtCommission1 and displays in the Text Box. I > also Changed this field names in the query commission1 and > commission2. > TotalCom: [Txtcommission1]+[TxtCommission2} > Do I need to also changed the control source > > What am I doing wrong. > > Thanks > Armando Torres > > > > --- In [email protected], "Hoffman, Tobi K \(DYS\)" > <[EMAIL PROTECTED]> wrote: > > Have you given the textbox or query field a different name from > the fields it references? If you start by pulling a field from the > field list onto the form (or use the wizard in starting to make the > form), it will have the field's name as the textbox name. Then when > you make it a calculated field, it looks to Access like a circular > reference, so you have to rename the textbox or field. If you > don't, Access gives that unhelpful "#Error# when you go to Form or > Datasheet view. I generally add "txt" as a prefix on a form, or > some other variant of the field name, and that's enough. > > > > Tobi > > > > -----Original Message----- > > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > > Sent: Thursday, September 15, 2005 9:48 AM > > To: [email protected] > > Subject: [AccessDevelopers] Re: Message in Query #Error > > > > > > Yes, > > I think the problem is the fields I am trying to total are > > calculated fields. Is this true??? > > > > Armando Torres > > > > > > --- In [email protected], "robsims99" > > <[EMAIL PROTECTED]> wrote: > > > Hi, > > > > > > Something a bit basic, but the field in the form or the field in > > the > > > table you're getting all the data from or entering into are all > > the > > > same data type? > > > > > > cheers, > > > > > > Rob S. > > > > > > > > > --- In [email protected], "torres0157" > > <[EMAIL PROTECTED]> > > > wrote: > > > > I keep getting an error message #error when adding a > commission > > > > total in a query to a grand total. > > > > > > > > any suggestions > > > > > > > > Armando Torres > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. Yahoo! > > Groups Links > > > > > > > Please zip all files prior to uploading to Files section. > Yahoo! Groups Links Please zip all files prior to uploading to Files section. Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
