Queries :: Count Only Certain Value

Jun 17, 2014

tbl_myTable:

Columns/Fields:

- pkID (primary auto),
- clientName (text),
- q1 (text) - can only be "ABC" or "DEF" (from form's radio button group),
- q2 (text) - can only be "ABC" or "DEF" (from form's radio button group),
- q3 (text) - can only be "ABC" or "DEF" (from form's radio button group)

Example:

PK= 1
clientName = John
q1 = ABC
q2 = ABC
q3 = DEF

Desired outcome of Query:

1 - John
2 - (count of ABC for John only)

- Irrespective of which column it is (ie. q1,q2,etc)...
- COUNT(sum?) only all the "ABC" for John.

View Replies


ADVERTISEMENT

Queries :: Using Count And MIN Together To Retrieve Only MIN Record With Count

Aug 16, 2015

I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.

BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797

The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.

Right now this is what i get

BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014

This is the output i would like

BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014

Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?

current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));

View 9 Replies View Related

Queries :: Totals In Queries - Count Similar Items

Jan 14, 2014

Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.

The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.

View 7 Replies View Related

Queries :: How To Count A Field And Sum

Jun 27, 2015

I working on simple MS Access program which have 2 tables. As you can see in snap shot. What I am trying to achieve is. I would like to run a query which shows data between certain dates(It is done) and also to show me which Technician has done how many jobs(i have 5 technicians) and how much money a technician received by a payment method like Technician ID 1 did Cash Transactions 4 worth $300 similarly Credit Card transactions 2 worth 120$ and so on for other technicians.

The main theme is to get weekly summary report of technicians how many jobs they done and how many transactions each technician made by cash, by credit card along with their amount.

View 3 Replies View Related

Queries :: Count On One Column

Jan 5, 2015

I have one table with one column that I am trying to extract data from.What I would like to do with the Column is split it into a few counts. The Column has numbers in it with KB usage. What i'm trying to do is count the amount of users that use say 0. Then have a column next to it that count say 500 >= 2000 and so on. This is completely baffling me as to why I can't seem to do this. I've even redesigned the database to see if I could fix this problem, but no luck I can build a query for this to be done sepearetly, but can't seem to find a way to put it into one query.

SELECT Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID, [test Usage].[Nov-14], [test Usage].[Nov-14], Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID1
FROM [test Usage]
GROUP BY [test Usage].[Nov-14], [test Usage].[Nov-14]
HAVING ((([test Usage].[Nov-14])=0) AND (([test Usage].[Nov-14])>=500));

View 5 Replies View Related

Queries :: Sum Overall Total Of Count

Aug 27, 2014

I have a query that is counting perfectly like I want, but I need to sum the overall total of that count. How do I sum a count in SQL?

View 3 Replies View Related

Queries :: Count Boolean By Row?

Feb 11, 2015

I have a table with seven Boolean fields: I need to count all the "trues" and return the row if the sum of the trues is greater than 3.how to do this.

View 2 Replies View Related

Queries :: Count Query For QBF

Apr 27, 2015

I have recently been working on a database that is meant to be quite user friendly. It contains two tables, one that lists 'trainees' and another that lists 'job applications' in a 'one to many' type relationship. It is designed to monitor Job applications being made by people doing courses here. So far, I have created a very practical form for data entry. I've created a QBF to allow the user to filter the Job Applications quite substantially. I'm trying to build a form that allows the same criteria to be entered, but that counts the number of Job Applications, and then creates a pie chart that subdivides the applications by 'source of application' (i.e. Indeed.com, Family, Newspaper'), or Industry of the potential employer.

For example, I would like to be able to select a programme or course that someone is on, their starting date, a date range for the application and some other details- i.e. Select an Industry then view a pie charts for the source of those applications and vice versa.

I've created a form for this, with a button for 'Source' and one for 'Industry' the two different pie chart types I would like to be able to select. I have created two separate query by forms for each, with the buttons linking to the respective Macro. The problem I'm having is that the query seems to ignore the WHEN criteria completely.

SELECT COUNT([Job Applications].[Application ID]), [Job Applications].source
FROM [Job Applications] INNER JOIN Trainees ON [Job Applications].[Trainee ID] = Trainees.[Trainee ID]
WHERE (
[Job Applications].[Application Date] > forms!CustomChart!DateAfter OR
forms!CustomChart!DateAfter IS NULL) AND

[code]...

View 3 Replies View Related

Count Records In Two Queries?

Jan 27, 2014

I have two queries, let's called them Query1 and Query2. I'd like to create a third query that looks like this:

Code:
File RecordCount Date
Query1.txt ### (Current Date)
Query2.txt ### (Current Date)

I know I can use concatenation and a date function for the first and third fields, so my main question is the proper syntax in order to get this layout.

View 2 Replies View Related

Using The Count Function In Access Queries

Mar 13, 2007

Hi

I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.

The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty
FROM [tbl_Audited Classroom for Week 02]
HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));

So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.

Can you please help?
thanks

View 5 Replies View Related

Queries :: Count Number Of Dates Seen Or Zero?

May 28, 2014

I just can't seem to get this one to work right. I've got the following query. I need to count the number of Null dates or show zero if there are no Null Dates.

Code:
SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, Count(qryNoticeResponseNew.[fldResponseSeen]) AS fldCount
FROM qryNoticeResponseNew
GROUP BY qryNoticeResponseNew.fldNoticeID;

Which is just counting the number of dates so far. It got me to thinking I need to do something like this.

Code:
SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, IIf(IsNull(qryNoticeResponseNew.[fldResponseSeen]),1,0) AS fldCount
FROM qryNoticeResponseNew
GROUP BY qryNoticeResponseNew.fldNoticeID;

Which pops a "cannot have aggregate function in expression" error.

View 2 Replies View Related

Queries :: Count By Current Week

Dec 26, 2013

Curretnly I have a query setup with Area, EventDate, & Complete. I want to be able to count each occurance of complete in current week. So far I have the following criteria under EventDate.

DatePart("ww",[EventDate])=DatePart("ww",Date()) And Year([EventDate])=Year(Date()).

So, this does in fact get me the records of current week, the issue I'm having is when an area has occurences daily. It will not bulk that area into a weekly count. I just need to be able to see how many occurances happen in the week total not daily. So my results could be.

Office1 5
Office2 1
Office2 1
Office2 1
Office2 1
Office2 1
Office3 5

View 2 Replies View Related

Queries :: Count Duplicate ID Numbers

Sep 15, 2013

I have the following code and I am trying to show a COUNT of duplicate ID numbers, so for example

ID ID_COUNT
2000 2
2000 2
2001 1
2002 1
2003 1

Code:

SELECT [DaisyServiceData-Import].*, Companies.CompanyName, Company_vs_Services.[Tariff Code], IIf([ClashForce]=True,"",IIf([ClashOveride]=False And [ClashClear]=False,IIf([DaisyServiceData-ClashExc].[DaisyServiceData-Import].[ID] Is Null,"",1),"")) AS ClashExc, IIf([Sell Price]="","",IIf([SellRateForce]=True,"",IIf([CompanyName] Is Null,"",IIf([SellRateOveride]=False And [SellRateClear]=False,IIf([Total Cost]>[Sell Price]+0.001,1,""))))) AS SellRateExc, IIf([Tariff Code]='1',[T1Rate],

[Code] ...

A GROUP BY query will not work for me as the recordset needs to remain updatable.

View 2 Replies View Related

Queries :: Auto-count Once Updated

May 26, 2013

i have a table with a column that reflects the below info, need to work out something for access to keep a count on the numbers of fields, have tried using conditional "count" in queries, the resultant value return is 12, this count omits counting all the "1", which is not i wanted, how do i tell access to start counting from the most recent "1" onward, and in this case the correct count value should return as "5", meaning there are 5 fields being entered after the most recent "1".

3
2
1
5
7
2
2
4
1
6
7
2
7
10

View 6 Replies View Related

Queries :: Running Count Within Group

Sep 15, 2014

I have data like so:

Code:
SubjectID VisitID
1 5
1 5
2 7
2 7
2 9
2 9
etc

And need to return a running count for the number of visits per subject, so:

Code:
SubjectID VisitID VisitCount
1 5 1
1 5 1
2 7 1
2 7 1
2 9 2
2 9 2
etc.

I'd like to use DCount and have tried all variations of the following, but none of them returns the correct number:

VisitNumber: DCount("*","mytable","VisitID >= " & [VisitID] & " AND SubjectID =" & [SubjectID])

View 4 Replies View Related

Queries :: Distinct Count In Subquery

Jun 3, 2015

Extended Cost] and salestran.[Extended Price] for each salestran.[SKU Code/Number], Count the number of unique customers, salestran.[Customer Code/Number], per SKU for any transactions equal to or after salestran.[Transaction Date] 11/1/2014.

Problem is, doing a regular count on the Customer field returns an incorrect value. It counts the number of times the sku appears within the given date range, ie 6 transactions with 2 customers, my count says 6. I need a distinct count on the customer, for the above example I want to see 2. Here is the code I have so far which yields an error of "At most one record can be returned.." distinct count in my Select statement along with the other fields I want to see, ie Summary data and SKU.

SELECT Salestran.[SKU Code/Number], (SELECT COUNT(cd.[Customer Code/Number]) AS Count FROM (SELECT Distinct [SKU Code/Number], [Customer Code/Number] FROM Salestran) as cd GROUP BY cd.[SKU Code/Number]) AS [Number of Customers]
FROM Salestran
WHERE (((Salestran.[Transaction Date])>=#11/1/2014#))
GROUP BY Salestran.[SKU Code/Number];

View 4 Replies View Related

Queries :: Count Duplicates By Month?

Jun 4, 2015

I want to be able to find duplicate numbers in the "claim #" field that exceed 20 within the "date of service" field for a month. I have a date box which allows me to select data from only one month at a time or all. What I'd like to be able to do is group the results by month. Currently it is only matching numbers by day not month.

SELECT HMR.[Claim #], Count(HMR.[Org #]) AS Total, HMR.[Date of Service], Count(HMR.[Date of Service]) AS [CountOfDate of Service]
FROM HMR
GROUP BY HMR.[Claim #], HMR.[Date of Service]

[Code] ......

View 14 Replies View Related

Queries :: Updating Table With A Count Or Sum

Aug 15, 2015

i am trying top create a stock take form. the form is now set up so each time an item is scanned a new record is created in the tblStockUpdate. i ow need to create an update query that updates tblItems with the correct quantities.

the relational data will be the barcode and is unique to each record in tblItems. because each time an item is scanned a new record is created i need to count records grouped by barcode or sum as each new record has a 1 as default in the qty column.i have created a qry that sums the records by group(Barcode) but now i need to update the tblItems quantities. update queries have always troubled me and this one is beating me at present.

View 3 Replies View Related

Queries :: How To Count Unique Dates

Aug 4, 2015

I want to do a unique count of dates when an activity was done in my table. The table may have multiple entries of the activity performed possibly on the same date by an individual

e.g. table entries

Code:

approvalNoSys dateAssessed Activity
100 01/08/2015 Audit
100 01/08/2015 Audit
100 01/05/2015 Audit
100 01/05/2015 Audit
100 01/02/2015 Audit
100 01/01/2015 Audit

Unique audit Count must equal 4

Code:

totV = ECount("[dateAssessed]", "R_P_Data_P", "[approvalNoSys] = '" & [Forms]![cmrOverview]![txtappNoSys] & "' AND [Activity] Like '*audit*'")
totV = Unique count
dateAssessed = date field in R_P_Data_P table
R_P_Data_P = table
"[approvalNoSys] = '" & [Forms]![cmrOverview]![txtappNoSys] & "' = criteria for the customer in question to separate them from many other customers in the table.
Activity = text field in R_P_Data_P table
audit = the activity

I'm also trying to avoid having to build total queries etc to them reference them, I'd like getting the desired outcome in an expression or small code.

I read about Ecount but my complier doesn't recognise the function

View 6 Replies View Related

Queries :: Count Based On Date

Sep 8, 2014

In the fields of the table, among others, there is a date field which represents the starting date of the flat availability. I'd like to create a query which displays the available nights per flat for each month in the current year. For example, Flat1 is available from 11.08.2014, then I'd like to see in the query result 20 nights for August and 30 for September, 31 for October and so on.I created a query which displays only the dates for the current year. Then I joined this query with the Flats table, the join condition is query.date >= flat.valid_from_date and displayed 1 in each row in the result as night. This way, I have the available flats for each day with 1 as available night. Then I tried to summarize the number of nights by month and flat.

View 5 Replies View Related

Queries :: One To Many Join / Count Query

Oct 16, 2013

I am looking to create a query using a main table and 3 related tables to produce a count of evaluations completed and evaluations passed by month (using the 3 related tables) and site location (using main table) which is joined by agent name. I have the following so far but:

Code:
SELECT
[MainTbl].Location,
Format$([Tbl1].[EvaluationDate],'mmmm yyyy') AS [EvaluationPeriod],
Count([Tbl1].[Agent Name]) AS [NoCompleted],
Count(IIF([EvaluationScore]>=0.9,0)) AS [NoPassed]

[Code] .....

But I get the following results:

Code:
Location EvaluationPeriod NoCompleted NoPassed
Location1 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location1 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location1 Month1 Tbl3.NoCompleted Tbl3.NoPassed
Location2 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location2 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location2 Month1 Tbl3.NoCompleted Tbl3.NoPassed
etc..

View 4 Replies View Related

Queries :: Getting A Percentage From A Text Count

Jun 11, 2013

I have a query that has clients sorted in the row area and a column from my table that is set up as text but only has yes/no options for the data. I have a pivot table that counts the yes's and counts the no's and finds a grand total; however, I want to be able to find the percentage of pass's given the grand total. I'm able to do this in Excel and was wondering if it was possible in Access.

View 2 Replies View Related

Queries :: Count Number Of Duties

Jan 25, 2015

Consider the table

Date Shift-1 Shift-2 Shift-3
1-1-15 OP-1 op-2 op-3
2-1-15 op-4 op-3 op-5
3-1-15 op-2 op-1 op-4
4-1-15 op-3 op-6 op-1
5-1-15 op-5 op-1 op-3

How can calculate the duties of operators (Op-1 to Op-6)?

Possible result is

Op-1 = 4, Op-2 = 2, Op-3 = 4, Op-4 = 2, Op-5 = 2, Op-6 = 1

View 1 Replies View Related

Queries :: Highest Value Of A Count Field

Jan 23, 2014

I have 4 columns

PostCode
District
District Name
Count of Postcodes = Count(Left([hull_PostCode.PostCode],4))

Now some Postcodes display 4 lines as they cover 4 Districts, my questions is I only want to show the highest Count per postcode and disregard the rest, Ive tried many variations of the Max function but am a little stuck.

View 1 Replies View Related

Queries :: Matching Two Tables With Count

Jan 20, 2014

trying to create a query in which I can select a count across two tables with matching postcodes.

Table 1 : Address database with column called 'STRIPCODE' (e.g. ME8, ME9, ME10)
Table 2 : Area Code table; each column has a list of area codes to match to the STRIPCODE column. (e.g. MEDWAY column will have all of the relevant area codes)

I would pretty much want a count of each match as below :

STRIPCODE
MEDWAY 650
BLACKPOOL 563
HULL 352

Have a few hundred areas to match and tired of going through these manually and entering counts!

View 3 Replies View Related

Queries :: Count Returns Null Instead Of Zero

Sep 12, 2013

I have a count column in this query, and i would like for it to return a zero instead of null if it doesnt find anything to count. Here's the SQL for the query.

Code:
SELECT Documents.Status, Count(Documents.Document) AS CountOfDocument
FROM [Request Details] INNER JOIN Documents ON [Request Details].Request_ID = Documents.Request_ID
GROUP BY Documents.Status, [Request Details].Contract, [Request Details].CDRL, [Request Details].Change_Cycle
HAVING (((Documents.Status)="No Record") AND (([Request Details].Contract)=[Forms]![Report Runner]![Contract]) AND (([Request Details].CDRL)=[Forms]![Report Runner]![CDRL]) AND (([Request Details].Change_Cycle)=[Forms]![Report Runner]![ChangeCycle]));

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved