Toby, If the value in Commission1 is 39.00 and 0.00 in Commission2 the result in TotalCom will display 39.00. "correct" So, TotalCom will display or sum correctly if there is only one value greater then zero in either Commission1 or Commission2. If there is a value in both Commission Fields TotalCom will display #Error. Wierd.... What is wrong Can Someone explain this........
Armando Torres --- In [email protected], "torres0157" <[EMAIL PROTECTED]> wrote: > 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/
