MONSTER QUERY

Hi Guys,
I need help with this. I needed to display all transactions in an outfit. So 
I designed a union query to combine all the transactions. The result worked, 
but only after a long lunch break so the query would open; it was that slow.
So I decided to break the query into smaller bits and use them in sub-forms. 
I made a smaller union query combining cash donations (positive figures) with 
cash expenses as negative. When I used this union query to build another one 
so that I could retrieve the sum (or net) of the cash column, I got two 
records, one each for the donations and expenses. I had expected only one 
record, representing the result of all the additions. Is there a way I can 
make the query return just one record after doing the additions and 
substractions?
Secondly, I used the query to build a form. The two records displayed 
alright. But a textbox I placed with the record source as =Sum([Value]) 
returned #Error. Why is that?
-- 
Glint
0
Utf
1/19/2008 11:13:00 AM
access 16762 articles. 3 followers. Follow

1 Replies
762 Views

Similar Articles

[PageSpeed] 37

On Sat, 19 Jan 2008 03:13:00 -0800, Glint <Glint@discussions.microsoft.com>
wrote:

>Hi Guys,
>I need help with this. I needed to display all transactions in an outfit. So 
>I designed a union query to combine all the transactions. The result worked, 
>but only after a long lunch break so the query would open; it was that slow.
>So I decided to break the query into smaller bits and use them in sub-forms. 
>I made a smaller union query combining cash donations (positive figures) with 
>cash expenses as negative. When I used this union query to build another one 
>so that I could retrieve the sum (or net) of the cash column, I got two 
>records, one each for the donations and expenses. I had expected only one 
>record, representing the result of all the additions. Is there a way I can 
>make the query return just one record after doing the additions and 
>substractions?

Yes; correct the error in your query (which you chose not to post and which we
cannot see).

>Secondly, I used the query to build a form. The two records displayed 
>alright. But a textbox I placed with the record source as =Sum([Value]) 
>returned #Error. Why is that?

For one thing, Value is a reserved word (every control has a Value property!).
Is [Value] the name of the field in the form's recordsource query? Did you put
the Sum textbox in the detail section of the form (won't work!) or in the form
Footer (where it will)?

             John W. Vinson [MVP]
0
John
1/19/2008 6:06:20 PM
Reply:

Similar Artilces:

Query error after converting
I'm running Access 2003. I have a working database. It seems as if the database is in Access 2000 format, because when I go to Convert, the 'Convert to Access 2000' option is grayed out. I need to convert this database to both Access 2002-2003 and Access 97. I have this part of VB code, and it works fine in the current version, as well as in Access 2002-2003 (after converting to Access 2002-2003). Dim rstRoutes As Recordset Dim strSQL As String ...... strSQL = "SELECT DISTINCT [Route] " & _ "FROM Highways_Info " & _ "WHERE ...

Problem with a form, a listbox, an sql query and a subroutine
Hello, I’m having an interesting problem with a form and hope I can adequately explain it. Here goes. I have 5 objects 1. Form1 2. Listbox1 (in Form1) 3. Some sql code that is in Listbox1 4. Table1 5. MySub1 Form1 is the main form that does some tasks I need to accomplish Form1 contains Listbox1. Listbox1 gets data from Table1 using an SQL query that links to Table1 Every time Form1 loads, the Form Load subroutine runs and that sub runs MySub1 which is located in a module, and is supposed to update Table1. the update is accomplished by deleting all the data in Table 1 and then re...

LDAP Query-Based Security Groups
I've seen SmartDL and ActiveGroups, but has anybody seen any other tools to keep up-to-date security groups. Query-Based DL's won't help me here since I'm looking to permission public folders based on the group membership. I'm looking for something that is very low cost, also. ...

Cannot edit MS-Query from Excel 'External Data menu
I have successfully created and saved a query. I can load the query directly from MS-Query application, and Modify it... save it, etc... I have 'run saved query' (same query) using excel's Data\external data menu and returned data to excel sheet. with data range cell selected, I can see the Data\External Data\"Edit Query" menu item.. Choosing Edit Query does not return the query that I intend to edit... any hints as to what the issue is.. Thnaks... Lark ...

Crosstab Query For Fiscal Year Totals
I want to write a crosstab query for FY totals that will show each month regardless of wether there is data or not. Any help on this is greatly appreciated. Thanks! The trick is column headings. Open the query in Design View. Right click up where the tables go and select Properties. In there is column headings. Put in the expected column names in order. Something like: "Jan","Feb","Mar" and so on. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "DevlinM" wrote: > I...

Check if query exists
How can I check, via code, that a query exists or not? I want to delete a temporary query created in the program, using the CreateQueryDef property, but it is not always that it has been created. Thanks in advance, Paulo See http://www.devhut.net/index.php?lang=en&pid=0000000003#DoesQryExist for a function to check if a query exists or not. Your alternative is to simply try and use the query and trap any errors that arise in the case that it does not. -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devh...

Query on a text file..help needed
I am trying to query a large text file and I'm beginning to wonder if it is just impossible to do. I used the excel query wizard but because I am working with a fixed length file I am not coming up with the columns I need. The wizard picks up each row as a solid block of information. Is there a way to pick out certain information from the text file into the query? so if my text line reads: 0110172006johndoe234street excel could pick out 01 10/17/2006 John Doe 234 street I know someone ran a query on my file and put it into the most beautiful excel spread sheet but it just isn't ...

Returning A Varibale from a query
I have a form that needs to create a unique ID upon entry. Like a new Sales Order Number for a new sale. What I want to do is the run a query that return the MAX value for a field. "SELECT MAX(SALNUM) FROM TABLE" I can then add 1 to that number and create and a new key. I am having a problem trying to determine how the return the result to a variable. strA = SELECT ...... or some such thing I have tried to look this up in the manual (RTFM) but have not had much luck. Any help appreciated. Thanks Wes Search on DMax for your need. -- Build a...

RelationShips Queries and joins
Not sure where to start and the posts I have read I do not fully understand I created 2 tables (remitt and Billing) and for each table I created a query--Because I needed to create a commmon expression (1st 10 digits of a name & a w/e date & Pay code). The names never match 100% I want to make a final query and bring over the Billing info into the remitt and create a final query Can I do this? Post the SQL of the two queries you have now by opening in design view, click on VIEW - SQL View, highlight all in the window that opens, copy, and paste in a post. -...

Don't understand posted update query answers
Read a lot of posts but ... One table called Address has field cell and field email. no data in those fields. Second table called Cell has field cell and field email with data. Address table has 2000+records. Cell table only has 136 records. How do I get Cell table data in Address table? Then I'll delete Cell table. Terry, Before you you update the table, how are they related? And, are you sure this is a one to one table relationship. I mean it's possible it's a one to many or a many to many... Some people might have two cell numbers, one personal and one f...

The kerberos client received a KRB_AP_ERR_MODIFIED error and Failed to query SPN registration on DC 'hostname_ho.domainname.local'
We have various branches connected to our main branch, but one of the domain controller from one of the small branches is having issue and is not replicating with DCs in the main office. It is also generating the event ID #4: "The kerberos client received a KRB_AP_ERR_MODIFIED error from the server host/name_host.domainname.local. The target name used was host2/ name.host.domainname.local. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named server accounts in the tar...

Query Help : accounts which are not members of existing marketing list
Hey all, I have few marketing lists and having lot of accounts as members . Now i am interested to make a new marketing list with new members , which are not member of any of existing marketing list. Is there any way to see which accounts are not member of existing marketing list ? Thanks Aami Sure AAmi, Not within CRM but you can run this in the SQL Query Analyzer to find them: select * from accountbase A left join ListMemberBase L on A.accountid = L.entityID where L.entityID is null HTH, Dave Aami wrote: > Hey all, > > I have few marketing lists and having lot of accounts...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

ACCESS QUERY 06-13-07
Is there any way in Access that I can combine two columns into one -- Gabriela "Gabriela" <gabriela_calouro@raytheon.com(donotspam)> wrote in message news:6000C576-1A58-4768-86DC-ACF038CD2594@microsoft.com... > Is there any way in Access that I can combine two columns into one > -- > Gabriela Are you wanting to simply display the two columns joined together, or are you wanting to physically combine the two columns in the table? To display two fields together, do something like this in a query: SELECT ([field1] & [field2]) AS [newfieldname] FROM [table]... ...

Summary Query
I need help with a quey. First I have a table that has different columns, two fields that I want to use are "CSR" and "Responses". these two fields are lookup and linked with two tables. Now i need a report based on query, where I can have CSR names on left, and on top responses, showing the counts. I know I can do it through Pivot Table but then i can not export it to Excel as simply text. -- Message posted via http://www.accessmonster.com How about a Crosstab Query with CSR as a Row Header, Responses as a Column Header and Count of Responses as the Value? This wi...

Check boxes and queries
Hi, I require a form that has multiple choice option boxes (I think that's what I need) that relate to several corresponding update queries. After checking options 1, 3 & 4 for example and then selecting a form button so as to run the update query 1 then 3 then 4. Any ideas please, cheers, Garry Using Options Groups you would select 1 or 3 or 4 but not all three. You get to chose only one option. You can have separate Radio Buttons where you chose as many as you wish as they are independent. -- KARL DEWEY Build a little - Test a little "Garry" wrote: > H...

Direct CRM Database Query
Can I use MS Query to read the _MSCRM database in CRM? Yes no problem at all. In fact using enterprise manager in sql and MS query is a good way to learn the structure of the CRM database "KBLawson" <klawson@aetinc.com> wrote in message news:6b62a526.0405111144.181a04f4@posting.google.com... > Can I use MS Query to read the _MSCRM database in CRM? I must be doing something wrong. I created an ODBC connection to my CRM server, told it to use the _MSCRM database. This worked fine. When I go into MSQuery to select my tables it shows lots of strange tables/views (eg. C...

link append query to combobox
I have a form (tester) that contains two unbound comboboxes. The first, cboMRSearchChart looks up a specific ID number (text datatype) for a patient, and the second (cboDOVSearchChart), displays the dates of visit associated with that ID number. The two fields are from the table "Patient Visits," where an autonumber Chart ID serves as the primary key. The selection of a date updates a subform (sbfPatientVisits), which is related to the mainform via Chart ID number. The column with Chart ID is present in the Date of Visit combobox, but is hidden from view. In the NotInList event of t...

Using Query by typing " SELECT......"
Hello, When I saw on the addresss sample database, the form Household is uing query by just mentioning SELECT...... My question is how can type SELECT then prompt the table to us. I normally use grid query, what is the difference using grid query of SQL Query ( is it SQL that using SELECT....?) Many thanks for any idea provided -- H. Frank Situmorang The RecordSource property of a form can accept: - a table name - a query name - a SQL statement. You can change the RecordSource property of the form programmatically. For example, if the form currently has a RecordSource of: SELECT ...

1) Run query through a table and 2) keep (or throw away) records
Here's what the data might look like: ID M MStatus Appl ApplStatus Art ArtStatus Appr ApprStatus -- --- -------------- ----- -------------- ------ -------------- ------ ------------ 1 M1 In Progress 1A In Progress MA1 In Progress AP1 Pending 1 M1 In Progress 1A In Progress MA1 In Progress AP2 Pending 1 M1 In Progress 1A In Progress MA1 In Progress AP2 Approved 1 M1 In Progress 1A In Progress MA1 Complete AP2 Approved 1 M1 In Progress 1A Complete MA1 In Progress AP3 Pending 1 M1 Complete ...

Help, importing most current data from web query
I have the following code but how do i edit this macro that will allo me to do a web query on the most recent data. For example, I just nee the high, low, close of stock X as of Feb 27th. So on the web query, only need the following data in my sheet: Date Open High LowClose Volume Adj. Close* Feb-26-04 49.7 50.8249.2950.5 3,342,100 50.5 Thanks in advance and any help is appreciated. Sub Stock1() Dim CoSym As String CoSym = Worksheets("Main").Range("C3").Value With Sheets("Stock 1").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/k?s=...

Problem with query dates
Hi My records date range in the database is Betwene 20/05/20010 and 22/05/20010 . When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the records less than this date, OK. But if I change the month, this date to 24/04/2010 00:00:00 shows the same records. That is even if I change the month the records that appear are always less than the 24 days without following month. There lies my problem. Note: My date style is dd-mm-yyyy and time is hh: mm: ss I really need to fix this.Seabra Dim Q1, SQL As String Dim DateTime1 As Date Dim ConnString As S...

completing a query
Hello everybody. I have this query which works fine. It takes its parameters (date from - date to - reasons) from a form. This works also fine. I sometimes need to be able to see all the reasons "causali" between specific dates. I would like to know if and how that is possible by just leaving the "causali" combobox empty. Of course any other suggestion is more than welcome Thanks -- Lisa Save the Dogs Onlus - www.savethedogs.eu Aiutaci a costruire il nuovo rifugio, dona il 5 per mille a Save The Dogs : 97394230151 Lisa If your query needs to display the &quo...

Funky Query Question
I'm building a new query that uses 2 inputs; a query and a table. The input query has 3 fields (a,b,c) that combine to be a unique identifier for each record. The input table has the same 3 fields (a,b,c) that make a unique identifier for each record. I'm trying to match records from each input source using all 3 keys (a,b,c), incldung a field from the table in the new record. But my new query output only includes the first match of the unique identifier (a,b,c), and doesn't pick up the rest of the matches (a,b,d - b,c,f - c,d,h - etc.) I've tried various combination...

Update Query
Happy New Year Everyone, I have two tables. One table needs to be update from data of another table. Here is the problem I need to use two fields to give the correct answer. TblA Fld1 Fld2 Fld3 23 AB Name 23 CD Name TblB Fld1 Fld2 Fld3 23 AB ? 23 CD ? Can you help me to create a query update that uses two fields to update one field. I can do it with one but not two. The two fields ...