Toby, I Don't think this query is very complex, however I may be wrong. As I have stated can calculated fields be totaled within the same query they are be created. can someone Help..
Armando Torres --- In [email protected], "Hoffman, Tobi K \(DYS\)" <[EMAIL PROTECTED]> wrote: > 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 --------------------~--> Most low income households are not online. Help bridge the digital divide today! http://us.click.yahoo.com/cd_AJB/QnQLAA/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/
