Queries :: Counting Specific Records
Feb 27, 2014
I am working on a Human Resources database for a big project that has a field called 'Position Number' (eg. 290) which is unique for each position. But the position can have up to 4 different stages over the life of the project which is shown in the primary key field 'Position ID' (eg. 290-A, 290-B, 290-C). There are columns for each month of the life of the project and If the Start Date and End Date of the position are in between these months then a number one will appear in the Month column.
For example, position 290 will start at level A (290-A) and will start on Jan-14 and finish on Aug14. The next stage of the project will start and position 290 will do the same role in a different area of the project (290-B) and will start on Nov-14 until May-15. Therefore the 48 columns with months as headings from Jan-14 to Dec-17 will show a number one in those months from Jan-14 to Aug-14 and Nov-14 to May-15.
There are over 150 position numbers with up to 4 different position ID's which is phased throughout the 4 years of the project. I need to do a count of how many "number ones" are in the monthly columns per Position.
View Replies
ADVERTISEMENT
Nov 23, 2005
hi there
I'm building a forum from the scrath. It is already working, but I want to have in each topic the number of replies it has.
there are 2 tables, one for the topics and other to the replies. in the replies table there is a field called id_post that has the id of the post witch it belongs.
I solve the problem by inserting a query inside the loop of the posts:
<%
While Not rs.EOF
%>
<%
Set rs2 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT COUNT(*) as cont FROM replies where id_post= " & rs("id") & " "
rs2.Open sql,Conn,1,2
%>
post: <%=rs("post")%> replies:<%=rs2("cont")%>
<%
rs.MoveNext
wend
rs.close%>
but someone told that this would make the page slower, so I want to know if I can do the same thing without having the query inside the loop.
I don't know if I made myself clear, sorry for that.
tks for the help.
View 1 Replies
View Related
Aug 15, 2014
I have a table that contains names of employees and the name of awards they received.
tblEmployeeAwards
EmployeeName, AwardName
John,Nobel
John, Pulitzer
Michael, Fulbright
Jane, Nobel
Jane, Fulbright
I would like to know the following:
1. How many employees received more than one award (answer: 2).
2. How many employees who received the Nobel prize also received any second prize (Answer: 2).
3. How many employees who received the Nobel prize also received the Fulbright (answer: 1).
View 10 Replies
View Related
Aug 14, 2015
I'm running into an issue where I'm trying to tie several queries together into a list one running total. I have six queries that pull data from the same table, but that meet specific criteria. What I was trying to accomplish was to have a 7th query count the records in each of the six queries, and return the results as a different value for each. Here's an example:
Queries:
qry_1A
qry_1B
qry_2A
qry_2B
qry_3A
qry_3B
Final product:
qry_totals:
1A | 1B | 2A | 2B | 3A | 3B |
20 | 15 | 33 | 19 | 12 | 6 |
What I tried:
Field: 1ATotal: Count([qry_1A].[valueName])
Total: Expression
And I did this for each field that I wanted the query to return, so:
Field: 1BTotal: Count([qry_1B].[valueName])
Field: 2ATotal: Count([qry_2A].[valueName])
Field: 1BTotal: Count([qry_2B].[valueName])
Field: 3ATotal: Count([qry_3A].[valueName])
Field: 3BTotal: Count([qry_3B].[valueName])
The problem is that I don't get what I expected - the query appears to be totaling all the records counted and applying that value to all the fields, so I get this:
1A | 1B | 2A | 2B | 3A | 3B |
105|105 |105 |105 |105 |105 |
View 2 Replies
View Related
May 10, 2013
I have constructed a neat database for randomly quizzing myself on French translations. However I need a simple way of counting the records in an underlying query "vocabularyQ" inside a sub routine. I have tried all sorts of statements the most recent being
SELECT Count(VocabularyQ.ID) AS CountOfID FROM VocabularyQ
View 5 Replies
View Related
Sep 19, 2013
I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.
View 7 Replies
View Related
Nov 14, 2013
I am currently working on a small database to track my own investment records.
I would like to create the function which enable to count the number of records that has exceeds the average value during that period.
Following is the SQL that I have managed to produce:
SELECT [ED 2013].Code, Count([ED 2013].Start) AS CountOfOpen1
FROM [ED 2013]
WHERE [ED 2013]![Date] Between #1/1/2013# And #2/1/2013# AND [ED 2013]![Start]>[ED 2013]![End]
GROUP BY [ED 2013].Symbol;
This SQL does work. However, when I try to add the Avg function. The Query stop working.
Meanwhile, I am wondering if there is any existing Ms Access template (free or commercial - but must be customisable ) available?
View 1 Replies
View Related
Oct 11, 2014
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
View 4 Replies
View Related
Mar 10, 2014
I have a query which I'd like to create which would be to retrieve records from a specific month of a specific year, in this format "01/14" "02/13" etc..
Obviously it would need a prompt box for the query for the user to input the month and year.
View 6 Replies
View Related
Jul 3, 2015
I have a form based on query. Each record has a checkbox.
I would like when activate checkbox to delete the specific record. from a button in the form.
Have not find anything around
View 14 Replies
View Related
Nov 26, 2014
I have a query from a table (to reduce the number of fields).The list is all the positions people have performed, as person could have one record another could have 10 the number is unlimited.
Fields are:-
-employee ID
-start date
-position
I need to find any records that were active ON or AFTER 01/09/2014. This will be 1 record for most but some could have multiple.I think it makes it more difficult that there is no END DATE.
View 6 Replies
View Related
Nov 16, 2014
I have 7 combos, which for the most part set the value in another field - however, there are two values in the combo that do not have a value attached - one is AH, the other is SK. I have two textboxes (one for AH, one for SK) that I wish to have a count (preferably running) of how many times each of these values appears in the seven combo boxes.
View 5 Replies
View Related
Jan 26, 2007
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
View 1 Replies
View Related
Dec 14, 2013
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
View 1 Replies
View Related
Nov 17, 2014
I have a task completion database. Each record of model consists of an expiry date. I need to review and update the records before the expiry date. Each model may have more than one record as I have updated the models a couple of times. I would like to select all of records with the expiry date less than 30 days so I can plan to review and update them on time. How to select all these records.
View 2 Replies
View Related
Jun 7, 2015
I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
DoCmd.TransferText acImportDelim, "fo Import Specification", "fo", FileName:="C:UserswelcomeDesktopfo.csv", HasFieldNames:=True
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
I use this code for export :
DoCmd.TransferText acExportDelim, "NewFnoSpec", "fnoquery", "C:UserswelcomeDesktopFO Output.txt", True
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.
View 14 Replies
View Related
Mar 20, 2006
Hi,
I wonder if someone could possibly help me.
I'm working on a database used to dispatch first aiders to events. The general structure is a form detailing the event with a subform (currently pulling info straight from a join table though I may change the data source to a query at a later date) containing details of attendees in list format.
I have a record in the events form that shows the number of first aiders attending which is currently updated manually. In the subform however, there is a tick box as to whether they attended as sometimes they enlist but have to cancel for whatever reason.
I wanted to implement something that will count the ticks and update the number attended field automatically.
I don't know alot about VB and have tried using the count expression function in the subform footer then setting the number attended field to equal the count field. The problem I find with this though is firstly there can sometime be a delay in updating this and secondly I need the number attended to appear in a report listing all the duties attended each month for expense claims.
I may be half way to hitting the mark with what I've tried but if anyone could suggest anything, I'd be very grateful.
Thanks in advance for the help
Ian
View 1 Replies
View Related
Mar 23, 2006
Quick overview. I have a site # and Subject #. The subject # is 7 digits and the first 4 are the site # (exp. Site # 1000, Subject # 1000001, 1000002, etc). At times the subjects switch sites but their subject # remains the same so Subject # 1000001 now resides at site # 2000.
How would I write a query like the following?
Count [tbl_Enrollment]![Subject #] WHERE [first 4 digits of the subject #] LIKE [tbl_Site_Dem]![Site #]
View 3 Replies
View Related
Jan 26, 2007
I have a student with an access table that has fields names week1, week2 etc. The data in these fields is either a '1' - meaning present or a '0' meaning absent. We want to be able to put a formula in a query that counts how many absences there have been (similar to a =countif formula in excel)
any ideas?
View 1 Replies
View Related
Apr 7, 2008
Hello everyone,
Got another problem that I'm sure you guys will solve with your eyes closed ;)
I've got a database with a field called courier_no. Imagine this set of data.
1234
1234
1266
1277
1277
1288
1299
I want to run a query to count the number of instances that each number appears. For example I would expect the results
1234 2
1266 1
1277 2
1288 1
1299 1
I then want to run a criteria on the count field (But I think I'm ok with that bit once I've solved the counting of the data.)
I've tried the count function in the query builder but it doesnt total the instances that each record appears.
Help please (and please keep it simple cos you know I struggle with all that VBA stuff :D)
Stu
View 4 Replies
View Related
Feb 8, 2005
hi all...
i'm sure there is a simple soultion to what i want to do but my brain isn't helping me at the moment...
i have a table called bookings with a column called nono which consists of dates with each date being a booking for a bed....
i want to be able to run a report for instance which tells me how many beds have been booked between two dates and year to date...
how do i go about doing this the most efficient way.... i can get a count for one particular date without a problem... and i know i could go and write out totals for each date in the range and then add them up...
however, is there a better more effieicient way of doing this???
cheers in advance.....
View 1 Replies
View Related
Jul 22, 2005
I have a table with two fields: Company, Product_Name
Currently the table is not normalized (which is what i am now trying to change). While the Product_Name are unique, the Company names repeat themselves (one company has several products).
I will like to find out which company has more than 10 products and see the names of these companies in a query.
Can I achieve this simply through SQL? If not, How do I accomplish this with VB?
As always, all assistance is highly appreciated. Thank you.
View 2 Replies
View Related
Nov 2, 2006
In my form (on which I have removed record selectors) I would like to display the number of total records. Is there some way of inserting this information in a text box or other control?
Thx
View 1 Replies
View Related
Dec 5, 2005
Hey all,
I am building a database to help my unit in Iraq. Here are the fields I have so far.
Field 1: Date
Field 2: Time
Field 3: Location
Field4: SIGACT (Significant Act)
Well that is the basics look like. I would like to be able to count the number of times a type of SIGACT occured in durring the day, week, and month. I would like to have this come out in a report I could then take to Excel and graph. The SIGACT has a couple different options like IED, and SAF. Thanks for any help you can provide
View 5 Replies
View Related
Dec 18, 2005
i'm trying to get a count of how many customers made billing requests during the month of november. the query is based on one table in the database. some customers made multiple requests, but i'm looking for the number of different customers who made requests, not the total number of requests made. the following sql stuff weeds out the duplicate customer numbers and lists each unique customer number.
SELECT DISTINCT TBLBillingRequest.MemberID
FROM TBLBillingRequest
WHERE (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));
my problem occurs when i try to to incorporate the count function like so.
SELECT DISTINCT Count(TBLBillingRequest.MemberID) AS CountOfMemberID
FROM TBLBillingRequest
HAVING (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));
it gives me a count, but the count includes duplicate customer numbers as well, not the total of unique customer numbers, which is what i want.
any suggestions/help would be greatly appreciated!
View 2 Replies
View Related
Dec 19, 2005
Hello All, I have what I hope to be a simple problem to fix.
I have 2 tables: tbl-Agents and tbl-Reviews
Every employee is an agent however; not every employee has a review for
month(x).
I am wanting a count of all reviews done for each employee even if they do not have a review done.
Joe Smith exist in tbl-Agents and also has 10 instances in tbl-Reviews for October. He will show up given the sql below.
Cathy Jones exist in tbl-Agents but does not have any reviews in tbl-Reviews for October. She does not show up given the sql below.
I need the output to look like
Joe Smith October 10
Cathy Jones October 0
SELECT [tbl-Agents].AgentID, [tbl-Agents].[Dept/Bucket], [tbl-Agents].AgentName, [HISTORY-Q].ReviewMonth, Count(*) AS Expr1
FROM [tbl-Agents] LEFT JOIN [HISTORY-Q] ON [tbl-Agents].AgentID = [HISTORY-Q].[Emp Num]
GROUP BY [tbl-Agents].AgentID, [tbl-Agents].[Dept/Bucket], [tbl-Agents].AgentName, [HISTORY-Q].ReviewMonth
HAVING ((([tbl-Agents].[Dept/Bucket])="Bank Card 61-90dpd") AND (([HISTORY-Q].ReviewMonth)="October"));
Please Help :confused:
View 4 Replies
View Related