Report based on Record Totals

I need to create a report based on the total amount of records there
are in about 8 different queries.  I don't need any of the information
except the total amount of records that there are.  Any help would be
appreciated.

-Anthony Morano

0
antmorano
6/13/2007 6:34:57 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
656 Views

Similar Articles

[PageSpeed] 32

SELECT COUNT(*) As countInQ1,
    (SELECT COUNT(*) FROM q2) As countInQ2,
    (SELECT COUNT(*) FROM q3) As countInQ3,
    ...
    (SELECT COUNT(*) FROM q8) As countInQ8
FROM q1


where I assume the queries name are q1, q2, ... q8


    If you want 8 rows, rather than one, try:


SELECT COUNT(*) As myCount, 1 as queryNumber FROM q1
UNION ALL
SELECT COUNT(*), 2 FROM q2
UNION ALL
....
SELECT COUNT(*), 8 FROM q8
ORDER BY queryNumber ASC



where the second column gives the query (number) the count belongs to.



Vanderghast, Access MVP



<antmorano@gmail.com> wrote in message 
news:1181759697.343578.128120@a26g2000pre.googlegroups.com...
>I need to create a report based on the total amount of records there
> are in about 8 different queries.  I don't need any of the information
> except the total amount of records that there are.  Any help would be
> appreciated.
>
> -Anthony Morano
> 


0
Michel
6/13/2007 7:02:15 PM
I'm a beginner to Access... I'm assuming I would put this in VB?  Or
as a module?


0
antmorano
6/13/2007 7:11:16 PM
Nope, as a query, in SQL view. Create a new query, switch into SQL view.



Sure, replace the ... by the sequence (involving q3, q4, a5, q6, q7), and 
use the real query names, and field names.



Hoping it may help,
Vanderghast, Access MVP


<antmorano@gmail.com> wrote in message 
news:1181761876.410767.160300@i13g2000prf.googlegroups.com...
> I'm a beginner to Access... I'm assuming I would put this in VB?  Or
> as a module?
>
> 


0
Michel
6/13/2007 7:20:59 PM
When I input it and change the query names I get a syntax error from
FROM.  Here is my SQL

SELECT COUNT(*) As myCount, 1 as queryNumber FROM q<65 H&w (I-IV)
UNION ALL
SELECT COUNT(*), 2 FROM q<65 H&W (V)
UNION ALL
SELECT COUNT(*), 3 FROM q<65 H&W (VI)
ORDER BY queryNumber ASC

0
antmorano
6/13/2007 7:59:26 PM
Assuming that everything after the FROM is the name of a query or a 
table, you need to surround the names with square brackets [query Name]

ELECT COUNT(*) As myCount, 1 as queryNumber FROM [q<65 H&w (I-IV)]
UNION ALL
SELECT COUNT(*), 2 FROM [q<65 H&W (V)]
UNION ALL
SELECT COUNT(*), 3 FROM [q<65 H&W (VI)]
ORDER BY queryNumber ASC


'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


antmorano@gmail.com wrote:
> When I input it and change the query names I get a syntax error from
> FROM.  Here is my SQL
> 
> SELECT COUNT(*) As myCount, 1 as queryNumber FROM q<65 H&w (I-IV)
> UNION ALL
> SELECT COUNT(*), 2 FROM q<65 H&W (V)
> UNION ALL
> SELECT COUNT(*), 3 FROM q<65 H&W (VI)
> ORDER BY queryNumber ASC
> 
0
John
6/14/2007 12:12:31 AM
JOHN- THANK YOU SO MUCH FOR YOUR HELP.  I GOT THE QUERY TO RUN
BEAUTIFULLY.  I REALLY APPRECIATE YOUR PATIENCE.

-ANTHONY MORANO
PENSION FUND INTERN

0
antmorano
6/14/2007 12:48:36 AM
Reply:

Similar Artilces:

Report loading pictures base on value...
Dear all, I have a report(labels) that have 5 textboxes with single value from either 0 to 9. so text box 1 could be 5, and text box 2 could be 8, and so on...So when a text box is with a certain value, i want it to load an image. so if text box 5 has a value of five, I want it to load image 5. I looked everywhere but couldn't find the answer. I think i have a concept but cannot code it...Please help. Thanks in advance. i'm looking to use: select case (expression - don't know how to start) case 1 load image 1 case 2 load image 2 case 3 load image 3 and so on... Thanks...

clear records on subform when go to knew record on main form
I am using the unbound combo fields on the main form (date, joblist, personnel) to populate and schedule fields in a subform when I navigate to a new record (new day) on the main form. I would like the subform records to be cleared out for the new record but remain for the day that has already been scheduled. What code and where do I put it. thanks, barb It should be automatic (no code) if your main form and subform are set up properly. Definitions that will make my explanation easier... just sub in your own names: FormMain - Your main form TableMain - The table or recordset und...

Daily Sales Report #2
Hi, I work in a retail outlet that opens at 10:00 am and stays open until around 12:30/01:00am the next day. When we run the daily sales report it will only pick up from 12-12midnight. Due to this we have a headache in Finance trying to tally up the figures. Is there anyway to specify the time and date from a report in RMS or do I need to look at some other reporting software? Any suggestions would be very welcome. Regards Ken Hi Ken, My company, Professional Advantages has Analysis Cubes for RMS. This product is designed to help retailers manage their business by delivering anal...

Insert specific record information into new record
I've got a problem which I don't really know how to tackle. I've got a table with records which I fill out via a form. When I add a new record, I'd like some information to be filled in automatically based upon information available in previous records. But the expression I'm looking for has serveral instances. How do I pick the single information I need. Example. I'm filling out a form that holds order information of a customer. When I'm filling out a new for for the same customer I'd like information like address, contactperson etc to be equal to the informa...

Report codes for Matrix Parent
I'm preparing for an upgrade from QS2000 and would like to know if th following is possible. I'm testing the demo version of RMS an imported my database. I'm a shoe and apparel store and matrixes ar very important to us. My reseller has recommended the Retail Real Fashion bundle, but I would like to try something else in th meantime.. I've modified a Detailed Item Sales report and would like to ad columns for the following items associated with the Matrix Paren Item Matrix Lookup Cod Matrix Descriptio Matrix Dimension 1 (I want to see the value associated with Chil item I...

Yearly Monthly break-down Cost Report
I need to report each month how many kids were in the program (New & Carry-over from previous month), number of days in program and cost (per diem). Time frame can be for calendar or fiscal year or past 2 months so Begin Date: (1-1-07) and End Date: (2-28-07) are variables entered. (example: Johnny "Date Placed" in program on 1-10-07 "Release Date" from program 2-5-07) so for January & Feburary how do I calcualate each month? January 10 - 31 and Feburary 1 - 4 (we don't count actual exit day)...HELP!!! THANKS!!! ...

Report Total
I have a query with total quanity shipped by date. I want a report that will show total shipped by month showing all months of year. I have one showing total for each month but don't know how to get each month total on one report. Thanks! On Fri, 8 Jan 2010 13:32:01 -0800, walters <walters@discussions.microsoft.com> wrote: >I have a query with total quanity shipped by date. I want a report that will >show total shipped by month showing all months of year. I have one showing >total for each month but don't know how to get each month total on one &...

Paid out report
Is anyone aware of a report that will let you view paid out details. I need something to show me date, amount, to who and the cooment. ...

Re: Maximum Value based on multiple criteria
Hi, I have the excel list with fields designation, experience, salary. I want to find out the maximum salary for a particular designation an particular experience range I specify. Is it possible to us sumproduct for this purpose? Can somebody help me out? Thanks in anticipation Regards V S Mohanakrishna -- Message posted from http://www.ExcelForum.com One way: Assume designation in column A, experience in column B, salary in column C. Also assume that the desired designation is in D1 and desired experience in D2: =MAX(IF((A1:A1000=D1)*(B1:B1000=D2),C1:C1000,0)) If you want to...

return items to supplier/reporting
how we can return items to suppliers with in the same purchase order so that we don't effect the posting in QB2003 7 keep the inventory accurate any recomended software for reporting i need to be able to have a charts to compare the sales & customer loyalty to the store in an easy way thanks -- maher almasri maher, The approved RMS way is to issue a Transfer Out back to the supplier. SO Manager | Inventory | Transfer Out. Also I assume that you know that Intuit is forcing an update to QB 2003 by April 30. -- * Get Secure! - www.microsoft.com/security You must be using Outl...

Reports not working in Outlook client but work fine in Browser
I have a client that is using the MS Outlook client to run MSCRM and when they go to the reports they get an error message. They work great in the Browser client. This company has the Report Server running on the SQL Server so I have a suspicition that it has something to do with Kerberos authentication. Any solutions? Is the user experiencing the problem running the Laptop client or the Desktop client? Is there more than one person experiencing the same problem? -- Visit my blog: http://lovepeaceandcrm.blogspot.com/ "X-eqt" wrote: > I have a client that is using the ...

Standard Reports on CRM 3.0
I am trying to setup CRM 3.0; I have set it up on sql 2005 and migrated the data from ACT with no errors. when I click on reports in workplace a message is displayed - an error has occurred - contact the system administrator. I ran the environment diagnostic tool and no errors appeared. Can anybody point me in the right direction to resolve this. For more detail error information you can turn on Dev Errors. To do this, log onto the Microsoft CRM Server. Open a File Explorer window and navigate to the folder where the Microsoft CRM web site is installed. Open the web.config and chang...

How can I totally eliminate a background from a photo of a person.
I have recently done our first church newsletter. I would,however, like to be able to incorporate more real photos of people. I cannot figure out how to eliminate the background from the photos so I am left with just the person. I have seen this done in other newsletters and I think it would be nice for some of the photos to be done in the same way. Can anyone help? "Spokes" wrote: > I have recently done our first church newsletter. I would,however, like to > be able to incorporate more real photos of people. I cannot figure out how > to eliminate the backgro...

Control Button to Create a New Record in Another Form
I've developed a MS Access application for Trade Shows. One of my tables contains all basic client info. This table is used as the data source for both a complete client form and a partial data form. Both forms can show all clients (individually). Using the partial data form I want to create a button that copies the current record and creates a new record in a form titled Trade Show Attendees. Other data in the Trade Show Attendees must be entered to complete that attendee's record. Some of the data entered will populate the TradeShow Attendees table. Can anyone describe t...

Check box in report
I have a query (qryAssyReport) that has fields for approvals (Culus, FM, Ce). The approval fields have image numbers in them so my label software knows what to image to print. What I would like to do is have check boxes on the report (rptAssyReport) for all the approvals that will set themselves to true or false based on whether the approval field is null. Can anyone help with this? Reports have static data and should not respond to data on them except to do calculations. You can check the values in a report and do an update query from VBA code. If the check boxes are not...

Bound combobox only working on certain records
I have a bound combobox on a subform (cboSeqNo, bound to cboSeqNo in qYDetail which is the record source for the subform) for which the rowsource is: SELECT DISTINCT qryGetFirstNames.SeqNo, qryGetFirstNames.Fname, qYtDetail.FamNo FROM qYDetail INNER JOIN qryGetFirstNames ON qYDetail.FamNo = qryGetFirstNames.FamNo WHERE (((qYDetail.FamNo)=[Forms]![fMemberInfo]![FamNo])) ORDER BY qryGetFirstNames.SeqNo; The problem I am having is that when I'm on a record which has no associated records in qYDetail, it does not populate the combobox with the child records (or any record...

no security to publish a report in excel builder
We are trying to start using excel builder since we have upgraded to SP2. When I try to publish the report that we created I get an error saying "You do not have security privileges to publish this report". I have tried it logging is as 3 different people including sa and get the same error message. Is there somewhere else that we need to set security to be able to publish the reports? Robert Fann Omni Packaging ------=_NextPart_0001_987B1BDB Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello, Thanks for posting to Newsgroups. To publish in Excel Report Builde...

exporting a report
how can i exoprt a report to a text file (office) with all the format (inculds lines and tables) and not only the database. Can't be done. -- Dave Hargis, Microsoft Access MVP "una" wrote: > how can i exoprt a report to a text file (office) with all the format > (inculds lines and tables) and not only the database. ...

Is there any way to use CView in Dialog Based App window?
Hi all Is there any way to use CView in Dialog Based App window? I want to use CView for drawings lines/rectangle etc etc Regards Nas You don't need the CView class. Just change your Dialog's OnPaint function and draw your lines and rectangles in there. Regards Guido On Jun 26, 5:43 am, "Guido Franzke" <guido...@yahoo.de> wrote: > You don't need the CView class. > Just change your Dialog's OnPaint function and draw your lines and > rectangles in there. > Regards Guido Ok...thanks for ur help...what will happen if i add button/ edit box etc an...

Counting based on a formula's result
Hi, I had a question about counting based on the result of the formula "Type(A1)". I needed to count cells in a range if their Type was =2. I also wanted to accomplish this in a single formula. In the past I have used Sumproduct to count. So to count the occurances of the word "Car" in the range A1:A100--> =Sumproduct((A1:A100="Car")*1) I tried using sumproduct and the type function but it did not work. Is there any way to do this, maybe with an array formula? Thanks Hi Jeff the following works for me: =COUNTIF(A1:A100,1) and =SUMPRODUCT((A1:A100=1...

How to change cell color based on content
Hi, hope someone can help me. I don't use Excel much, but I'm creating a spreadsheet that keeps track of ticket sales for a charity I volunteer for. Anyway, what I want to do is to make the cells stand out for those that still owe money, either because a check is forthcoming or because they ordered by credit card. I want the credit card orders to show up with a different color (eg YELLOW) than those who are fully paid. I want the forthcoming check orders to show up in a different color (eg RED). So I have a column with payment type that is either Cash, Check, or Credit Card....

Render a RDLC report using VBscripts
I have an application that is not developed in Visual Studio but is connecting to a SQL database and is utilizing a RDLC report. Does anybody have any code that would only be a VB script file to render the report instead of creating a form in VB and running an exe to accomplish this? ...

Report problem with ODBC Call Failed
Good Day! I have a form that has two date fields that are date/time pickers, a combo box and a button. i also have a query that uses serveral tables from an odbc from a pervasive DB. That query is bound to several reports. keep in mind they all use the same query. now on that form, the user chooses the date frame of the query, (the query criteria is bound to these date/time pickers), and then chooses an option from the combo box, then then clicks the button. when the button is clicked, there is a 'select' that is based on the option selected from the combo box, and then opens th...

What's SPF record and is it necessary to have one?
Hi all, What's SPF record? Is it necessary to have one? How to create one? (exchange 2003 Sp2 environment) Thanks in advance On Wed, 13 Sep 2006 06:43:02 -0700, Peter <Peter@discussions.microsoft.com> wrote: >Hi all, > >What's SPF record? Is it necessary to have one? How to create one? >(exchange 2003 Sp2 environment) > > >Thanks in advance > http://www.openspf.org/ Everything you need to know, even a wizard! Not required, but recommended. http://www.exchangeinbox.com/articles/013/antispoofing.htm "Peter" <Peter@discussions....

Date parameter for report
In the Crystal Report viewer we had for CRM 1.2, when a parameter was a date, it would show something like the datetime picker control. With CRM 3 and SSRS, I've only been able to use a text box type control for the users to type in the date they want. Is there a way to show a calendar-type view for the users to click on a date for a date/time report parameter? Thanks. Hi, Date Picker control is available in SRS 2005 but not in SRS 2000. -- PLEASE do click on Yes or No button if this post was helpful or not for our feedback. uMar Khan Email for direct contact: imumar at gmail do...