Trouble with creating a simple query with calculated fields.

Hi,I want to creat a query that would look like the following. The onlytrouble is I am not able to get the calculated fields to calculate thevalues. It gives me #ErrorNames | Contribution | Difference | remainder | Payables----------------------------------------------------------------------------------------------John	55	35	67.5	32.5Smith	10	80	67.5	-12.5Cooper	15	75	67.5	-7.5Toby	10	80	67.5	-12.5---------------------------------------------------------------------------------------------	90	270---------------------------------------------------------------------------------------------Rules:TotalExp => Sum(Contribution)Difference => TotalExp - ContributionTotalDiff => Sum(Difference)Remainder => TotalDiff * (1/4)Payable => Remainder - DifferenceI want to do this in a Query.. Thank youRex
0
Rex
3/9/2007 2:42:26 AM
access 16762 articles. 3 followers. Follow

3 Replies
479 Views

Similar Articles

[PageSpeed] 1

What's the underlying Table data? What's the SQL view of your Query? Your description seems to be of a Totals Query, but it's not obvious to me from the example that's what you are intending.  I hope you were not expecting to be able to use a Query to do sequential processing through your Table --  that's not what Queries in databases are intended for. Larry Linson Micosoft Access MVP"Rex" <rakeshv01@gmail.com> wrote in message news:1173408146.387705.120810@v33g2000cwv.googlegroups.com...> Hi,>> I want to creat a query that would look like the following. The only> trouble is I am not able to get the calculated fields to calculate the> values. It gives me #Error>>> Names | Contribution | Difference | remainder | Payables> ----------------------------------------------------------------------------------------------> John 55 35 67.5 32.5> Smith 10 80 67.5 -12.5> Cooper 15 75 67.5 -7.5> Toby 10 80 67.5 -12.5> ---------------------------------------------------------------------------------------------> 90 270> --------------------------------------------------------------------------------------------->> Rules:>> TotalExp => Sum(Contribution)> Difference => TotalExp - Contribution> TotalDiff => Sum(Difference)> Remainder => TotalDiff * (1/4)> Payable => Remainder - Difference>> I want to do this in a Query.. Thank you> Rex> 
0
Larry
3/9/2007 3:16:54 AM
Yes I want to do sequential processing through tablesOn Mar 9, 1:16 pm, "Larry Linson" <boun...@localhost.not> wrote:> What's the underlying Table data? What's the SQL view of your Query? Your> description seems to be of a Totals Query, but it's not obvious to me from> the example that's what you are intending.  I hope you were not expecting= to> be able to use a Query to do sequential processing through your Table -- =20> that's not what Queries in databases are intended for.>>  Larry Linson>  Micosoft Access MVP>> "Rex" <rakesh...@gmail.com> wrote in message>> news:1173408146.387705.120810@v33g2000cwv.googlegroups.com...>>>> > Hi,>> > I want to creat a query that would look like the following. The only> > trouble is I am not able to get the calculated fields to calculate the> > values. It gives me #Error>> > Names | Contribution | Difference | remainder | Payables> > -----------------------------------------------------------------------=----=AD-------------------> > John 55 35 67.5 32.5> > Smith 10 80 67.5 -12.5> > Cooper 15 75 67.5 -7.5> > Toby 10 80 67.5 -12.5> > -----------------------------------------------------------------------=----=AD------------------> > 90 270> > -----------------------------------------------------------------------=----=AD------------------>> > Rules:>> > TotalExp =3D> Sum(Contribution)> > Difference =3D> TotalExp - Contribution> > TotalDiff =3D> Sum(Difference)> > Remainder =3D> TotalDiff * (1/4)> > Payable =3D> Remainder - Difference>> > I want to do this in a Query.. Thank you> > Rex- Hide quoted text ->> - Show quoted text -
0
Rex
3/9/2007 4:18:31 AM
Clarify for us the original data in the underlying Table. And, rather than give individual "rules" based on the idea of sequential processing or how you think it might be accomplished, describe what you are _trying to accomplish_ in words. Something like the following  "For each member, total all dues payments, . . ."You can't reliably do sequential processing in an Access query because the reading/calculation may not take place when you think it should. Relational database tables are, by definition, UNordered. They are ordered by sorting (in SQL terms, using an ORDER BY clause) in the Query, but the ordering may take place after the calculation.You can sort the data in a Query and process sequentially in code, which is one approach. But, you may be able to accomplish your purpose without "sequential processing" in a Totals Query. If you can, it will be more efficient as well as simpler to implement.  Larry Linson  Microsoft Access MVP"Rex" <rakeshv01@gmail.com> wrote in message news:1173413911.170304.136750@h3g2000cwc.googlegroups.com...Yes I want to do sequential processing through tablesOn Mar 9, 1:16 pm, "Larry Linson" <boun...@localhost.not> wrote:> What's the underlying Table data? What's the SQL view of your Query? Your> description seems to be of a Totals Query, but it's not obvious to me from> the example that's what you are intending.  I hope you were not expecting > to> be able to use a Query to do sequential processing through your Table --> that's not what Queries in databases are intended for.>>  Larry Linson>  Micosoft Access MVP>> "Rex" <rakesh...@gmail.com> wrote in message>> news:1173408146.387705.120810@v33g2000cwv.googlegroups.com...>>>> > Hi,>> > I want to creat a query that would look like the following. The only> > trouble is I am not able to get the calculated fields to calculate the> > values. It gives me #Error>> > Names | Contribution | Difference | remainder | Payables> > ---------------------------------------------------------------------------�-------------------> > John 55 35 67.5 32.5> > Smith 10 80 67.5 -12.5> > Cooper 15 75 67.5 -7.5> > Toby 10 80 67.5 -12.5> > ---------------------------------------------------------------------------�------------------> > 90 270> > ---------------------------------------------------------------------------�------------------>> > Rules:>> > TotalExp => Sum(Contribution)> > Difference => TotalExp - Contribution> > TotalDiff => Sum(Difference)> > Remainder => TotalDiff * (1/4)> > Payable => Remainder - Difference>> > I want to do this in a Query.. Thank you> > Rex- Hide quoted text ->> - Show quoted text -
0
Larry
3/9/2007 9:58:36 AM
Reply:

Similar Artilces:

Removing users pager field
Hello, I want to remove visability of the pager field for users. In this field we have a pinnumber unique to every user. When someone opens a contact in OWA or outlook, that pinnumber is visible to everyone. Is there a policy to hide the pager field from the contact's properties? grt Jorin Howdie! Jorin van Mourik wrote: > I want to remove visability of the pager field for users. In this field we > have a pinnumber unique to every user. When someone opens a contact in OWA or > outlook, that pinnumber is visible to everyone. Is there a policy to hide the >...

How to know when to use category and series fields correctly
I am trying to figure out what options are available when you plug in specifice information to fields. I need to be able to look at specifice information by month and by procedure code. So does that need to be on the series axis or category or etc.? I keep getting all the information for the year listed and I am not able to list it for individual months. When I try to group for a month it says group is not allowed. Can someone tell me what I am doing wrong? Thanks -- ultrasounds4u Not 100% sure of the problem, do you have a number of rows each with a month in one column and pr...

Updating a field's data to another field in same table???
I hope I make sense. What and how is the best way to update a field with data from another field from the same table? Everything from Field 1 to Field 2. Thank you in advance for any help you can provide. See: Calculated fields at: http://allenbrowne.com/casu-14.html The article describes how - and, more importantly, when - to store a field that depends on another field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TotallyConfused" <...

view field descriptions in the database documenter
is there a way to view field descriptions in the database documenter? i need to create a datadictionary that contains this information? tia, mcnewsxp You need to check the box to display the field properties. I'm not sure what version of Access you are using but there is an article at http://www.access.hookom.net/Documenter/ which explains how to create your own reports from the results of the documenter. -- Duane Hookom Microsoft Access MVP "mcnews" wrote: > is there a way to view field descriptions in the database documenter? > i need to create ...

having trouble logging out of OWA
Hi, I am having trouble logging out of OWA (Exchange 2000 server). When I hit the logoff button, it brings up a sign-in screen asking for ID & password again. Whether I cancel it or fill it in and type OK, it then gives me a screen that says "access denied". I never get a screen which says that logout was successful. Thu, 11 Nov 2004 03:14:31 -0500 Spin: wrote > Hi, > > I am having trouble logging out of OWA (Exchange 2000 server). When I hit > the logoff button, it brings up a sign-in screen asking for ID & password > again. Whether I cancel it or f...

ldap query #7
how can I see, using ldap query, if a forward is setted from a mailbox to a contact? -- Dambo Hello, I did some work on this a while ago. Here the attributes you're looking for: 1. Forwarding = AltRecipient If this is not used it is <not set> If forwarding is setup then it uses the DistinguishedName of the forwarding object. Also: The forwarding object will also have it's AltRecipientBL attribute set to the DistinguishedName of the mailbox set to forward items to it. 2. Deliver messages to both forwarding address and mailbox = DeliverandRedirect Please note this ...

Trouble with "Time and Billing Database" template
Hello, I'm new to using online templates but need a new database that this template is set up well to use, but I'm not a expert in Access, I understand the basics, but not the more complicated code. As I said, I like the general setup of this template but have noticed a couple of bugs that I'm not sure how to fix. (There's a Dlookup on the timecard report that isn't functioning correctly and the timecard report doesn't print correctly). I just started getting into this (to set it up for my business) and noticed these bugs. Does anyone have any advice? Should I ...

CRM Email Template not showing Dynamic Field values
I am running Windows 7, CRM 4.0 and IE 8. When I create a workflow that has a trigger to send out an email with dynamic fields the dynamic fields are not populating but instead the name of the field is ex.: CCN # {Case Number(Case)} for {Customer(Case)} has been resolved. The actual case and customer should be populated from the Case form that was filled out. I have other email temlates associated with the same workflow and some work, some don't. I have created the workflows on differant machines and came up with possibly IE8 being the issue - has anyone run into this and ...

Excel Filter/Query
Hi, I have a table of data laid out in a spreadsheet. I would some wa of filtering out the rows that have a zero in one of the columns. filter works, but I need something different.. because I am exportin this data and it includes the filtered out rows as well. And it need to be somewhat automated, if possible... maybe it flows into anothe sheet. Any ideas? I put query in the title because it would retur results similar to an access query.. basically create a new table on new sheet. Thanks in advance -- proben93 ----------------------------------------------------------------------- p...

Unable to create dest msg on exchange 2000
One of my users has created inbox rules now every time she opens her mailbox get error message "unable to create dest msg" Its on an exchange 2000 box with SP3 Has anyone seen this problem. ...

Funny characters in my query results
Why do I get what looks like Wingding characters in some columns of my query results? You may have a corrupt database. Tony Toews has an excellent web page on database corruption. Http://www.granite.ab.ca/access/corruptmdbs.htm Allen Brown also has excellent info on corruption. Http://allenbrowne.com/ser-47.html I have a white paper in a Word document named Fix Corrupt Access Database. Click on "Whittle, Jerry" on this page: http://www.rogersaccesslibrary.com/forum/default.asp?C=1 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bont...

create multiple rows using a variable in a cell as source
Is there a way, to create multiple rows using a variable in a cell as source to indicate the number replicated rows needed? In Excel, I have a name and address and another field to indicate how many "rows" I need for that record. All the quantity of replications will be different, that is to say, I may need one of this one, but 4 of that one, and 10 of yet another. How can I use the quantity variable to replicate the row in the worksheet? Ideally, I want to use a source worksheet to create a new worksheet or better, a workbook, of the replicated rows. Any resources or...

Create dynamically a ListView
Hi, I would like to create dynamically a listview. How can i do it ? i've seen some example with CListCtrl only, some others with CListView MFC class, but none of them create a complete ListView....they are on partial code. Should i create a CListView object and after use its Create function ? or can i directly create a CListCtrl object and use its Create function ? It's quite confusing in documentation. Even if i have the feeling that everything is managed by CListCtrl. thanks a lot, Alain --== Alain ==-- wrote: > Hi, > > I would like to create dynamically a listvi...

Queries
Hope this explains it....I have a table called Class Listing (classid, name, etc ), another called registrations (actual registrations, class id, date reg. , etc.). I have classid linked between the tables and my queries run just fine....I need to be able to change the info in the registration table (either change or delete) on a case by case basis...right now I have the info pulling up from a query which I cannot make any changes to. What is the best way to handle this? (i.e...parents register their children for a class, then a few days pass and they call and want to switch to a dif...

Calculate Figures in a column if ajacent column has particular da
How do i total number of cells if the row fall within a particular month and the adjacent column has a "Y" in it. Then Convert this to a 3-d Pie Then the same as above but if the adjacent column is "Blank" instead of a "Y" in it. Shaggy wrote: > How do i total number of cells if the row fall within a particular > month and the adjacent column has a "Y" in it. Then Convert this to a > 3-d Pie > > Then the same as above but if the adjacent column is "Blank" instead > of a "Y" in it. Hi Shaggy, I think you could u...

Age Calculation
I have the following sample field and record Age ApprovedDate 40 15-05-2000 Age-number field that shows the current age in 2010 Approveddate - date field when the application was approved. How to calculate the age of the applicants at the point of approval. In the above example the age would be 30... Thanks On Thu, 13 May 2010 17:41:01 -0700, zyus <zyus@discussions.microsoft.com> wrote: >I have the following sample field and record > >Age ApprovedDate >40 15-05-2000 > >Age-number field that shows the current age in 2010 >A...

Problem creatting charts with VBA
In Excel 2007, when I create a new chart using Charts.Add I get a chart that has all the series of a previously created chart. How can I prevent this? I want a clean slate. I know that in the past I could not avoid having at least one series when creating a chart. I simply redefine the attributes of series one. And this seems to be true of 2007 as well. TheWizEd - Jon Peltier's web page, "Quick Excel Chart VBA Examples," might be useful, at http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html Note that he uses ChartObjects.Add instead of Charts.A...

Query with header, detail and total
Is there a way to do ODBC query in Excel with header, detail lines and totals? Regards, Boris Boris, there are two possibilities, neither of which quite accomplishes what you want, but here they are: 1-You can pull in the external data, then use Excel Subtotal feature to total your data by groups; or 2-You can use the external data to create a pivot table, which will allow you to group the data and show totals. There's a good overview of pivot tables at http://www.peltiertech.com/Excel/Pivots/pivottables.htm. DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing....

Error on Crystal Report when using custom field
When I run one of the canned crystal reports (like Case Summary) and try to use one of my custom built fields, I always get this error: Error in formula This field name is not known.. File a4c34756b Is there something obvious I'm missing? All I'm doing to add the fields is to go to Deployment Manager and adding a schema field. Am I supposed to sync that with a crystal db or something like that? Thanks for anyone who can help. ...

Link a calculation to a field item
I am currently designing a database and two of the field criterion are going to be safe and unsafe. I want to assign 5 pts for every safe when observations are entered into the database. The safes need to be total and averaged out of the total possible points after each audit entry. How do I link this up to safes, total and average? Please help Regards Dubbs Hi Dubbs, One of the important rules of database design is that totals and averages are not stored in tables in the database. When you want to show total and average safes in a form or report, base the form or report on a qu...

how do I create a 2 year follow up active log
I am new to this and I am creating my own business and would like some info on creating a 2 year log follow up for clients I am signing on such as periodic updates and birthdays any help greatly appreciated You can either use the Calendar or Tasks, depeding on what works best for you. Tasks sounds like it would be better as the calendar can get clogged up pretty quickly. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. ...

hide the 'drop page fields here' from a pivotchart
I created a pivotchart from a pivot table, and the 'Drop Page Fields Here' shows up on the pivotchart. I do not have any fields to drop into this section on the pivot table; is there anyway to hide it or remove it from the pivotchart? Hi, See http://www.contextures.com/xlfaqPivot.html#DropPage Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "lawandgrace" <lawandgrace@discussions.microsoft.com> wrote in message news:D13E1C78-19FD-47AD-95AD-F174FB5804B1@microsoft.com... >I created a pivotchart from a pivot table, and the 'Drop Pag...

Combo Box and Two Fields
To add a new record in a form, I am using a combo box to load a table from another table. The table I am loading to has two fields that I’d like to load with one combo box. I like to choose two fields in the combo box and load both fields in the record I am creating. =?Utf-8?B?cmFwNDM=?= <rap43@discussions.microsoft.com> wrote in news:525BB8B4-7D99-4CC6-9A45-0B54D4CF2D78@microsoft.com: > To add a new record in a form, I am using a combo box to load > a table from another table. The table I am loading to has two > fields that I’d like to load with one combo box. I like to &...

Queries not reliable
Hi, How is it possible that a query view that goes "07" = yes, OR "08" = yes, AND "09" = no Old account bit attributes "07" = yes, "08" = yes, "09" = no Newly created account bit attributes "07" = yes, "08" = yes, "09" = no Would only show the newly created account? This is from an organization that has redeployed. Thanks for any help or insight. Hi; Your results could be resticted by CRM security - run the query as a user with the system admin role and see what results you get. Dave Ireland ...

from: field won't go away
Hi, I have a problem with my outlook - I have two accounts set up, one is with exchange and the other is with pop3. All of a sudden all of my emails default with the from: field on and filled in with my exchange name. This is a problem because any mail I send through my pop3 account is telling people to reply to my exchange account which I do not want. I can clear out my name there and everything works, but thats a pita, I want that to be off like it was before. I checked my other computer and it works fine, so its something local to my machine. I also tried deleting my email profile and ...