Pulling counts out of query results

I have a query that has one field Type which is set to Count
The query results are used in a report.
The report has the fields in the detail section as TYPE and CountOfType (1 
line only in the detail section)
The report looks like this when displayed 

AS 28
AV 17
OR 5

I need to be able to get the individual AS No (28) and add it to another 
number elswhere in the report.  How can I do this?

I have tried using a textbox with an if function (if 
[type]="AS",CountOfType,0 but that did not work.

Any help appreciated.

Ray
0
Utf
6/22/2007 9:27:00 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
599 Views

Similar Articles

[PageSpeed] 42

I think you will need to do it in the query.   
But try adding a new field in your query output like this --
    AS: IIF([TYPE] ="AS",1,0)
Change Group By to Sum 
This will output your AS type in a field so you can use it in the report.

If that is of no help then post the SQL for your query.
-- 
KARL DEWEY
Build a little - Test a little


"Ray" wrote:

> I have a query that has one field Type which is set to Count
> The query results are used in a report.
> The report has the fields in the detail section as TYPE and CountOfType (1 
> line only in the detail section)
> The report looks like this when displayed 
> 
> AS 28
> AV 17
> OR 5
> 
> I need to be able to get the individual AS No (28) and add it to another 
> number elswhere in the report.  How can I do this?
> 
> I have tried using a textbox with an if function (if 
> [type]="AS",CountOfType,0 but that did not work.
> 
> Any help appreciated.
> 
> Ray
0
Utf
6/22/2007 10:45:00 PM
Reply:

Similar Artilces:

Convert datatype using query
I have create a query which combines two tables; ASSETS and UNITINFO. The UNITINFO table is linked from another database and the ASSETS table has a field that looks up a field called UNIT # from the UNITINFO table. Both fields are called UNIT # but the datatype on the UNITINFO table is set to Text and the UNIT # field on the ASSETS table is set to Number since it is a lookup field. I cannot change the datatype on the UNITINFO table to Number as the field contains both numbers and text. So when I ran my query that I created to pull data from both tables it gave me an error bec...

How to pull names and fields
Hello can anyone tell how I can get Excel to pull data from different worksheets if the common thing between them is a person name. I'd guess you'd want to use =vlookup() or =index(match()) And Debra Dalgleish has some nice instructions (for both) at: http://www.contextures.com/xlFunctions02.html and http://www.contextures.com/xlFunctions03.html Brian S. wrote: > > Hello can anyone tell how I can get Excel to pull data from different > worksheets if the common thing between them is a person name. -- Dave Peterson ...

HELP with queries!!!
Hi all, I am a new user to Access and need some help with setting up a query. I am building the framework to an inventory database. Currently I have one table that has the following information: serial # Status (checked in or checked out) equipment type (pc, network printer, standalone printer, scanner, and monitor) EE# (equipment number) model manufacturer work order# department (where the equipment is going) equipment location (the room number) relocation date comments I understand how to run the query, but I am trying to include drop down menu's in the query ins...

DLookup on a query??
Hello, Just wondering if it's possible to use the DLookup function on a query instead of a table? Alan Don't worry about it. I got it to work. Thanks anyway. Alan "Alan" wrote: > Hello, > > Just wondering if it's possible to use the DLookup function on a query > instead of a table? > > Alan ...

how do i count numbers that contain 02 (i.e.7029955)?
how do i count numbers that contain 02 (i.e.7029955)? I tried =COUNTIF(C143:C154,"?02????") but it did not work. The system seems not to recognize the ? wildcard. Try: =SUMPRODUCT(--ISNUMBER(SEARCH("02",C143:C154))) -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "caliche" <caliche@discussions.microsoft.com> wrote in message news:C7563017-D686-4B64-9DBB-197F84DD3127@microsoft.com... > how do i count numbers that contain 02 (i.e.7029955)? I tried > =COUNTIF(C143:C154,"?02????") but...

Counting with multiple criteria
Would be grateful if someone can help me with this A B C D 8-9AM 9-10AM 1 CA03 ON CA03 OF 2 CA03 OF CA03 OF 3 CA03 ON CA03 TR 4 CA03 TR CA03 TR 5 CA05 OF CA04 OF 6 CA05 TR CA05 ON 7 CA04 OF CA05 TR Note: Column A-B has the same header row, and so does C-D I want to count in the range (A1:D7) - How many CA03 and TR: [the result should be 3] - How many CA03 and ON and OF: [the result should be 5] Thanks in advance for your help. Best regards, Thuy Assuming your table is in A1:D8, data in rows 2 to 8 (in A2:D8) > ...

Report available for items IN a stock count?
Is there any report already built in to Dynamics that will show us a particular vendor's items that are currently in a stock cycle count? Or show us if it's ever BEEN in a stock count (other than annual inventory) Is there something in SmartList? We are using v9.0 I could create one via MS Access if someone could tell me which tables would be the ones to use? Pam, From http://victoriayudin.com/gp-reports/inventory-tables/: IV00103 - Item Vendor Master IV10300 - Unposted Stock Count (header) IV10301 - Unposted Stock Count (line detail) IV30700 - Stock Count History (header) IV...

Web Query Not Working
Up until yesterday, my web query in my Excel worksheet has been working perfectly. I have been using the saved query named "Microsoft Investor Stock Quotes.iqy". It looks like the server accessed by the URL has been down. When I ping the domain, I get the message that the ping timed out. The link used by the query is http://investor.msn.com/external/excel/quotes.asp?SYMBOL=AA,fdx... Is anyone else experiencing the same problem? Does anyone know how to contact MSN to jog them to get things up and running? Or, have they stopped supporting the query? Gary Mine went d...

Query Based DL [WILDPACKET]
I want to configure a QBDL so it sends message to all the Active Accounts in our Domain, is this possible? I guess when a message is sent to QBDL it also sends messages to the Disabled Accounts too, right? I aonly want the QBDL to send messages to Enabled Users. Please advise. Thank you On Mon, 5 Dec 2005 08:44:04 -0800, "WILDPACKET" <WILDPACKET@discussions.microsoft.com> wrote: >I want to configure a QBDL so it sends message to all the Active Accounts in >our Domain, is this possible? > >I guess when a message is sent to QBDL it also sends messages to the ...

Help with count formula please,
OFFSET(C6,COUNT(C7:C33),0) I am using this formula as part of a spreadsheet I am using for a social golf club, it has 20 sheets, and this formula keeps track of how many games an individual plays. It works well except for the fact if a player misses a game the result (in C35 shows 0, as per the formula) eg: c7 1 c8 2 c9 3 c10 4 and the result in c35 is 4 ( as I want) but if c7 1 c8 2 c9 (no input,blank) c10 3 the result in c35 is 0 not 3 So what I need is to change the ,0) bit but I am not sure what with or how to replace it so that it ignores a blank cell ......... Thanks for any help Ke...

Queries
I have the following query (for example): countofstates color 3 red 8 blue 7 orange 2 purple 9 yellow I would like to display this table on a report, but when I do it repeats 6 times. How do I only get this information to appear once? Thanks in advance. Try a Totals query... Color States GroupBy Count -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.ne...

Import from web query problem
When importing from a web query the cell value <10E 6/L or <10^ 6/L does not import ie target cell is blank. Same happens whether the middle space is left in or out. Cells such as <100 import OK. Any suggestions as to what is happening here would be appreciated. Thanks. ...

Counting spefic cell (not a range) with a value greater than 1
I am looking for a formula to count specific cells and a range (A5, A10, A15, A20 and so on) but only count when the value is greater than $1.00. So if the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value of cell A15 was $0.50 and the value of cell A20 was $2.00 the total count would be 3. I'm stumped! =SUMPRODUCT(--(LARGE((A5,A10,A15,A20,A25),ROW(INDIRECT("1:"&COUNT(A5,A10,A15,A20,A25))))>1)) however if you want to sum every 5th cell greater than 1 you can use =SUMPRODUCT(--(MOD(ROW(A5:A50),5)=0),--(A5:A50>1)) -- Regards, Peo Sjoblo...

assigning a unique query for each textbox on a form
I've got a table (tblMain) with the following (relevant) fields: tblMain .FirstName .LastName .Seat .RefNum I've got a form that has a graphical display of the seats to which the people listed in tblMain are assigned. I'd like to put a text box on each seat corresponding to first and last name of the person assigned to that seat (stored in tblMain.Seat). The same database is used for various seatings so I'd like only those associated with the RefNum at hand to be called. I can write a simple enough query which I can call from VBA and which relies on a...

Where are the Forms and Queries in 2007
In Access 2003 if you wanted to open your existing Forms and Queries you clicked on the Forms button. In Access 2007 this option seems to be missing. My question is how do I access the Forms,Tables and queries. The only forms button I have found creates new forms, I want to access my existing forms. Many thanks for any advice. This is one of those times when a picture paints a thousand words, so here's a picture ... http://brenreyn.brinkster.net/navpane.jpg -- Brendan Reynolds "Ernmander" <Ernmander@discussions.microsoft.com> wrote in message news:4CDA479B-BFA6...

MFC based client server application query?
Hi experts I have developed an client server application using MFC sockets support. The server is a dialog based application & the client is an SDI application. I am having problem with sending data from client to server. After starting the server application, i start the client and try to connect & send data to server using the following code. void CMainFrame::OnConnect() { CSimpleSDIsockclientApp *pApp = (CSimpleSDIsockclientApp*)AfxGetApp(); int res = pApp->ClientSocket.Create(40000); if(res) { MessageBox("Client Socket at port no. 40000 Successf...

Using a cell to determine a value in a database query.
Hi folks, I've run into a problem recently - maybe there's no good answer, then again perhaps you can help me come to one. I have a spreadsheet on which There are three entry fields: start date end date store number I've managed to map start date to [startdate] in a query and end date to [enddate] - that seems to work fine. The problem I'm running into now has to do with the store number field. In the database, store number is a 4 character field, as of now the numbers of the store are like 01,02,03,04..HQ,RO. When I try to map store number to [storenum] in the query, I ...

Forced Saving of Queries, Forms, Reports, etc.
So I have a strange scenario... I have a complex/large database broken into a FE and BE (multiple actually). If I open the db with the Shift bypass key... everything development wise (designing of queries, forms, reports, etc.) works like normal. However, if I open the db and allow the main form vba code to execute (which includes an append query from a network BE table) and then switch to the db container and edit queries/forms/reports or create a new one... Access will auto save any changes made (when editing) or force me to save (when creating new). I don't get the &quo...

Query Builder (SQL View)
I am trying to join 2 tables. I want to join if 'Field B' is in 'Field A'. ie. Field A = 'ABCDE' Field B= 'BC' ....the join would be successful cause 'BC' is in 'ABCDE'. Is this possible? I tried... SELECT FROM table1 INNER JOIN table2 ON table1.name like (% table2. partial_name %); Keep getting errors. Any help greatly appreciated! Warren Depending on the "flavor" of SQL you are using SELECT * FROM table1 INNER JOIN table2 ON table1.name like '%' & table2.partial_name & '%' Or SELECT * FROM table1 IN...

Count consecutive cells
I have a table which tracks the attendance at an event over the years. Each time someone attends, I enter 1 in the column for that year, otherwise blank. I'd like to know the most consecutive events people have attended. So, if I have rows like (with 'b' representing a blank cell): Name1 b 1 1 1 b 1 1 1 1 b 1 b 1, most consecutive = 4 Name2 1 1 b b 1 b b b b 1 b 1 1, most consecutive = 2 Name3 1 1 1 1 1 1 b 1 1 1 1 b 1, most consecutive = 6 Name4 1 1 1 1 1 1 1 1 1 b 1 1 1, most consecutive = 9 Name5 b b b b b b 1 b 1 b 1 b b, most consecutive = 1 Is there a formula I can use to c...

Macro
With the help of the online Microsoft team they have provided me with the following Macro which is transferring data from Sheet 1 to Sheet 3. All I need to do is display this data from Cell A8 onwards. At present the Macro is copying everything onto sheet 3 from cell A1 onwards, which is not allowing me to put any column titles in place. Can you help? Sub Risks() Dim lngRow As Long, ws As Worksheet, lngNRow As Long Set ws = Sheets("Sheet3") For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) > 0 And Range("A&quo...

Count by short date and group by agent name
I have a tracking table (tbl_Tracker) that now contains date and time in one field [Date] and agent name in another field [SLR]. I would like run a query to do a count of number of records by agent by day. How do I set up the query to recognize only the date and not the time. I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a record for each date and specific time instead of grouping the whole day. Then I need to build another query that looks at another table (tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a date range and ...

Displaying Formula Result Error
When typing a formula, I cannot see the result. I have tried ctrl + ~, however this just expands all columns. Thi only seems to happen on some of my worksheets. Any suggestions are grealtly appreciate. I have also tried searchin for an similar question, but could not find one. Thanks!!! Shan -- Message posted from http://www.ExcelForum.com Shane Maybe the cells were formatted as text when you typed the formula? Andy. "Shane >" <<Shane.zxdn9@excelforum-nospam.com> wrote in message news:Shane.zxdn9@excelforum-nospam.com... > When typing a formula, I canno...

Counting Rows
Hello, I am trying to do the following: 1. Count populated Rows using VB 2. If the # of populated rows is less than 2 post a message box dsiplaying the count 3. If the # of populated rows is greater than 3, continue on with the rest of the macro. Anything you can do to hlep is greatly appreciated. This will look at the last populated cell in the desired row. Sub countrowsincolA() mc = 2 '"a" x = Cells(Rows.Count, mc).End(xlUp).Row If x < 2 Then MsgBox "Only " & x Else 'goon MsgBox "oh boy" End If End Sub -- Don Guillett Microsoft MVP Excel S...

Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...