Sum of a UNION Query

Hello,

I want to sum the values together that are returned from the following
two queries joined with a UNION from within the same query (instead of
creating a second query that will SUM the two values that are produced
from this one).  I tried "wrapping" this code within a SELECT SUM, but
it returns with "this is not allowing in subqueries".

SELECT Count(*) AS SubCount
FROM [TABLE1]
WHERE (([TABLE1].USER)="JOHNSMITH")
UNION ALL SELECT Count(*) AS SubCount
FROM [TABLE2]
WHERE (([TABLE2].USER)="EJONES");

The results are:

SubCount
--------------
380
45

But, as I mentioned, from with the same query I want the following:

SubCount
--------------
425

Any ideas?

Thanks in Advance!

0
Hendy88
5/21/2007 9:23:41 PM
access 16762 articles. 3 followers. Follow

0 Replies
1201 Views

Similar Articles

[PageSpeed] 35

Reply:

Similar Artilces:

Use Multi-Select List Box as Query Parameter
Is there a way to get the multiple selections from a list box into my query as the parameters? You need to use VBA. http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is an example, although personally I prefer using code more similar to http://www.mvps.org/access/reports/rpt0005.htm since it generates shorter criteria. (The first one generates a criteria like Field1 = Value1 Or Field1 = Value3 or Field1 = Value4, while the second one generates a criteria like Field1 In (Value1, Value2, Value3)) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-...

How do I write a query for all those that have an odd value?
We have a database of football players. One of the data fields is "Jersey number". How do we write a query to list out all players with an Odd jersey number. Expr2: [JerseyNumber] Mod 2 CRITERIA -- >0 This divides number by two or multiple of two and list the remainder. Any with remainder is odd. -- KARL DEWEY Build a little - Test a little "LSCTeach" wrote: > We have a database of football players. One of the data fields is "Jersey > number". How do we write a query to list out all players with an Odd jersey > number. It is called mod...

select * query excluding one field in sql server
I want to write a SELECT query for all fields except for one field that I want to exclude from the select statement. Let say I have a table containing 150 fields; I want to read 149 fields and exclude one field e.g Field_126 Is it possible to get this done without listing 149 fields explicitly? Not directly. Here are a couple of alternatives: - In SSMS you can right click a table and choose Script Table As > Select To > New Query Editor Window and let SSMS generate the query for you. Then just delete the column(s) you don't want. - You could create a view that ex...

Query Calc. is Mult. # of Table rows--Y?
I've almost completed a database for my company to process Credit Memo's. However, I have an issue which I've run out of options to solve.... I have 11 rows of data in my table called 'Credit Memo Request'. In this table I have a field for Customer #, Customer Name,......,Part # 1-10, Quantity 1-10, Item Amount 1-10. (Part #, Quantity, & Item Amount are one line item used to calculate one credit. You can have up to 10 line items per Customer #.) I don't have any totals in the table, as I've read that's a no no. However, I want some of my reports to cont...

Query Criteria 01-11-08
I have two fields. One field is a summary query for the other field and it sums the totals for the brands listed in the first field. If I set the criteria for the brands field so that the first criteria is the leading brand and the second or line is the second leading brand-what formula would I use so that the third result would display the total sums of all brands. I want to have a pivotchart with columns that shows the sum of the first leading brand in the first column, the second leading brand in the second column and the sum of all brands in the third column. -- rjk ...

Summing the numbers in a cell that conyains text
Hi...i need to add the numbers in several cells but the cells als contain text....how can i sum these numbers with out deleting the tex firs -- klatla ----------------------------------------------------------------------- klatlap's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1290 View this thread: http://www.excelforum.com/showthread.php?threadid=31936 Hi could you provide some examples of your data. e.g. - is the number in the middle, the beginning or end of this cell - fixed position of the numeric part - etc. -- Regards Frank Kabel Frankfurt, Germany &...

union query 11-28-07
Hi all, I'm trying to create a database that contain electrical performance of my companiy's product.As the objective, at last I have a report look like below: Model: OSC30TDS1 Prod.code: 71102 ------------------------------------------- Specification ------------------------------ Ampere Power Airflow SampleNo 3 10 100 ------------------------------------------- 1 2.4 8.7 89 2 2.6 8.8 90 : 5 ...

New Reporting, Budgeting & Query Tool
For those of you that haven't yet looked at XL Reporter (XLR) I encourage you to check it out. XLR has been resolving a lot of reporting & budgeting issues for customers. XLR is an Excel-based tool that has powerful analysis of queries as well as an easy to use report writer for reporting on the GL & Sub-ledger modules (Also MDA, Fixed Assets, Inv & PA for GP). XLR works on Axapa, Great Plains, Solomon & Navision (Nov-04). www.xlreporter.com for more information. ...

Query Criteria 04-28-10
I have a query where I am trying to get a count of items. The first column is: Less than 10: (IIf([Days in Queue]<10,"")) This gives a count for each queue that has been under 10 days and is working correctly. I cannot get the criteria to work for the # ranges after that. I want it to return the count for anything that has been in queue for 10 to 20 days. I know what I have below does not work. I need something for the =10-20 part. Right now I get all zeros returned. 10-20: (IIf([Days in Queue]=10-20,"")) Please help! -- Message posted via http://w...

Pass-Through Query as Record Source
1. If you are using a pass-through query as the record source for a form, can you modify the records on that form. I mean the form is a "Sales Orders" form. So the user should be able to enter, and modify the records. 2. If you can modify the data on the form, what instance or instances can a table field become un-updateable because the form is using a pass-through query. Thanks, Ben P.S. I have already posted this at microsoft.public.access.forms newsgroup also but no answers as yet. The pass-through query is not updatable. So, 1) no 2) all. That said. You must conside...

Between Dates Parameter Query
Hi Everyone, Access 2003. I have a query that I have set up so the user can type in "between" dates. They pull data for the month so the first date they enter is 3/1/10 and the end date is 3/31/10. I want all records between these dates but anything that is 3/31 doesn't show up inless I type in 4/1. Is there a way I can add something in the expression so it automatically adds the one day? Thanks, Linda Between [Enter Start Date] and DateAdd(d,1,[Enter end Date]) Better would be <=[Enter Start Date] and < DateAdd(d,1,[Enter end Date]) T...

VB Query
Hi, does anyone have an example of vb code that returns a value to a userdefined field from a lookup window. For example I have a piece of code already that on button click launches a region lookup window. I then want to return the value I select to a userdefined field. Has ayone got any ideas? Thanks adam ...

Look up combos in Query Criteria?
I am trying to build a database that narrows the record choices as different fields are selected. I believe that this would be a query, but whenever I build a query and select a field choice, the record in the main table changes. For example: Car Dealer enters car ID, make, model, and color into main database. I as a consumer, select make model and color, and the query lists only records that match. I would prefer a lookup box in the query, as we don’t want to rely on stuff being named correctly, or maybe a burgundy from the list, would suffice for a red preference. Keep in mind, ch...

How do I run a query that displays field names and not captions
How do I run a query that displays field names and not captions The simplest solution would be to open the table in design view, and clear the Caption property for your fields. If that doesn't work, you could alias the fields in the query. For example if you have FirstName showing as First Name (with a space), and you want it to actually show as FirstName, switch the query to SQL View (View menu), and change the SQL statement from: SELECT Table1.FirstName, ... to: SELECT Table1.FirstName AS FirstName, ... -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for A...

Append Query
I am trying to append data from tblitemline to tblitemlist. I only want to append new records. The unique field is ListID. I want to append 4 fields total. I can't get it to limit it to new records. Any help would be appreciated. Thanks! ...

Auto Sum unknown number of rows
Im using odbc to import data from a database. Imported is a list of products in column A and then various columns containing sales figures. Because of the import from the database, I do not know how many rows of products will appear, this week we sell 500 different products, next week 550. I need to add totals to the columns directly under the last product. I've tried creating the totals in an empty template and then inserting the imported data so the total figures move down and appear at the bottom. However the imported data moves the total cells to the right instead of down. Tried ...

Query Based Distribution Groups-Issues?
I am looking at implementing Query based groups and am curious, what are some of the problems I am might run into? Example: Outlook 2003 cached mode? OWA? Active Sync? and most of all: If I am going to replace an existing group, I am planning on, after hours, deleting the existing group and creating a query distribution group. How will this affect the users and their offline address books. Thanks, Bob As far as I can tell you can't assign permissions for example to Public Folders to Query Bases Groups - that was a big one for me. Mark "Bob Williamson" <BobWilliamso...

Filling combobox from query
hello... how can i fill a combobox by vba from a query?? i have a query in a macro that goes like: Range("E1").Select With Selection.QueryTable .Connection = Array(Array( _ "ODBC;DSN=TURMALINA;Description=TURMALINA;;;APP=Microsof Office 2003;WSID=GENERATOR;DATABASE=JDE_DEVELOPMENT;Network=DBMS" _ ), Array("SOCN;AutoTranslate=No;UseProcForPrepare=0")) .CommandText = " SELECT F58001.PDDSCA " & _ " FROM JDE_DEVELOPMENT.TESTDTA.F58001 F58001 WHER F58001.PDPRJCOD = '" & Worksheets(1).Range("AB1").Value & "'"...

Question about Query Wizard
When I create a new database query using the query wizard, sometimes it lets me edit the query by clicking the edit query button on the external data toolbar. However, in some queries it automatically opens Microsoft Query instead. Why is this? Many thanks Anita ...

Query Based Distribution List Note
We are running Exchange 2003 and using Query Based Distribution Lists (QBDL), viewable through the GAL. Our users are complaining (and building their own lists) because they can't see who is included in the list. We use desciptive names, but they need more information. All QBDLs are based on a company, a department or part of a title. I have asked our administrator to place in the "note" field of the QBDL what the parameter(s) were. For example, "people with COMPANY beginning with ABC". How do I tell my administrator(?) to edit the note field? Don In ADSIEdit, ...

Suppress Data if Sum = 0
I have a report where I am looking for errors. If the sum of several records = 0 then everything is OK. How do I supress the header, data and footer from showing for these records? I've been away from Access for several years, please be specific. Thanks for your help, Rick The simplest way to suppress the data from the report is to eliminate it from the source query. To do that means creating a Totals query or subquery that gives you the total in the original report. You can then use critieria in the query to eliminate these records. 1. Create a query, and depress the Total icon on t...

Cannot sum in report footer based on group footer
I have a calculated control on group footer called txtFlex =IIf([semester]="Fall Full-Time",[FT_Fall],IIf([semester]="Spring Full-Time",[FT_Spring],Sum([Hour]/2))) I want to total it in report footer =sum([txtFlex]) When I run the report, it asks parameter 'txtFlex' in report footer. I double checked the spelling and it's correct. I even tried to use Expression Builder to use mouse to pick the control but it still gives me the error message. If I delete the control in report footer, no error message. But I do need that summary control in report footer. W...

=sum(4150/(1-(.39+.10)) why does this not calculate out to = 8137
The equation I am trying to compute is setup using values from diferent sheets. the 4150 is the total of three cellson sheet 1 divided by 1minus a cell value minus a cell value which gives me a mutiplier to add to a percentage. =sum(C14/(1-('sheet2'!f16+.1))) this is what I have tried to get =sum(4150/(1-(.39+.10))) should = 8137 the problem is in the calculation (1-(.39+.1)) can not figure it out help Hi! Works for me: =SUM(C14/(1-(Sheet2!F16+0.1))) Returns: 8137.254901960780000 What result are you getting? Biff "Dave Keister" <Dave Keister@discussions.micro...

report or query transactions
Is there a way to query or report the transaction types and distinguish the department within the transaction? I need to 1) locate and report on (or query) all credit transactions 2) determine the credit card type. 3) determine the department, and 4) determine the sale amount. ALL input is appreciated! You can't really do that. Tenders (the card transaction) are linked to Transactions (the entire sale). There may be multiple tenders on a single transaction (Cash + Credit, or multiple credit). Departments are linked to Items which are linked to TransactionEntries (the line items ...

Sum up values that falls between two specific dates
Hello :) Please consider this scenario. I have two dates: let's call them receive date and disbursement date. In between them I receive installments and I disburse some as well. Receive Date Received Amount Disbursement Date ------------ --------------- ------------------ 07 May 100000000000 11 May 15 May 100000000000 26 May 22 May 100000000000 26 May Now the question is, how do I sum up the installments I receive between two disbursement dates, that is, between 11th and 26th of May? Thank you for your help. Hi Judicato Assuming your ...