It looks very long to me; I've dealt with long queries that had to be split up, and it did help. But with all the fields that have "Total" in their names, it does look to me like it should be managed differently as far as data structure goes, using a table named tblTotals referencing all the types of totals you need, and a linking table referencing tblTotals and your base table (Tbl_CSCPricingAdvice?). This will often help in simplifying the queries and help make data more manageable in the long run. Are these fields holding something calculated within the database? If so, you don't need the fields, as you can always get that total from the other data. I very much think you need to look at the database structure rather than try to have a query do as much as you are trying to have that one do.
How are you using this query? If it is for a report, it might be better to have the calculation done in a textfield in the report rather than in the query. Tobi (and I am NOT Toby -- he's someone else!) -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 Sent: Thursday, September 15, 2005 1:49 PM To: [email protected] Subject: [AccessDevelopers] Re: Message in Query #Error 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 ------------------------ 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/
