I have designed an attendance database, with fields for personal details and fields for the days of each month.
I need to have a query that will ask for a certain date(i.e. find the specific field) and then search on specific criteria (i.e. ON SITE) to see which staff are available.
What is the best way of doing this? Any help would be greatly appreciated
i'm trying to count the total number of records found for this query. i used the following expression (code) in the QED field row Total Incidents: Count([IncidentID]) which, result in an error message.
Can sum1 help me solve this problem. any help would be highly appreciated.
OK, I have a database that manages Work Orders. There are 7 statuses that each work order could possibly be in. They are:
0 - NEW
1 - Open
2 - WIP (Work In Progress)
3 - Serviced
4 - Reviewed
5 - Followed Up
6 - Closed
I have several menus for Admin, Manager, Supervisor and Technicians. I have 7 textboxes on each of these menus that are all grouped together to display the count of how many work orders are in the system for each status. I have totals queries for each status that use the Count function to get the total number of work orders, but having trouble knowing how to get those numbers to show up in the text boxes...
The work order table has a lot of fields in it so I won't list all the irrelevant ones here, but the fields that are important are:
WorkOrderT (Name of Table)
WorkOrderID
CustomerID
StatusID
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
I have a query where I want to see a percentage of executed demos. I currently have Name of the promo (group by), # of demos (group by), Status (criteria = E for executed demos, count) and Percentage: Status/# of demos. Everytime I run this query I get this message: "Data type mismatch in criteria expression." The only data in Status is either a O for open or an E for executed. The E in Status is the only criteria I have in the whole query. If I take the count function off Status, it runs, but does not give the right results. Does anyone know what I am doing wrong?
View 14 Replies View RelatedDoing a school project and need to add a count function to a sub form that is based on my query. The count function just needs to be displayed on the bottom of the sub form showing how many records are in the sub form.
When I do this, the function works all good.
When I add the function to a header or footer, so that it doesn't show a column and repeat itself each time.
Hi there,
I have a field called Unit in my table. That field references another table which has about 5 entries. The user can select one on the form.
What I want to do is count how many times each one has been selected so that i can create a chart. The unit names are: North, West, East, South and Main.
Any help would be appreciated.
Thanks
i have a table which contains the attendance records for employees. It shows their supervisor their name aemp number and which kind of hours they worked basic, overtime, Been late and absent is all recorded here.
SupervisorEmp NoSurnameForenameCodeValue
JENNIFER BUCKLEY200McCormackBridgetOT14
JENNIFER BUCKLEY200McCormackBridgetOT21
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS5
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS5
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetLTAM0.05
JENNIFER BUCKLEY200McCormackBridgetOT14
JENNIFER BUCKLEY200McCormackBridgetOT21
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS4
this bben a sample from the table. I want to be able to have the employee name, NUMBER AND SUPERVISOR there just once with the appropiate heading for what type of CODe they have BAS, OT1 as a heading with a count of the number of times they done that particular code
Where do i begin any help
Hi
I have a table where is listed all happened faults from a month period. Every fault have start time and end time field and ip field which tells what device has been "broken". Now I'am trying to calculate how many faults has happened in every day. I have make a query from the table which looks like this:
"SELECT (DateValue(Table.[start time])) AS date, Count(Table.[IP adress]) AS [faults happened]
FROM Table
GROUP BY Table.[start time], Table.[IP adress];"
And I get this:
date faults happened
12.8 2
12.8 1
12.8 1
13.8 3
..
But how I can make my query count together how many faults has happened in every day? So it should look like this
date faults happened
12.8 4
13.8 3
..
Hi guys
I'm trying to create a query that will count records from 2 different tables that are both related to a 3rd master table. e.g. table 1 is the master table and contains records of employees, this then has 2 child tables: customers and products. I want to count the number of customers and the number of products that belong to that employee in the same query so that i can then produce a report without the hassle of sub reports etc. any advice on this would be great
Cheers
Hello!
i am trying to sum the total of ID's by corporate titles in query. In the totals row i have selected 'count' in the ID column and 'sum' in the title column but this doesn't work. I tried the reverse and it also was no good :confused:
Can anyone help - i'm sure its something simple
thanks
I wonder if someone could help me with the count function,being as I've never used it. I have two forms, one with a number box called:
Form1![CR2]
The other is called:
Form2![[TotalCR2]
I want to add all the records from Form1![CR2] and put them into
Form2![TotalCR2]
I know its going to be simple and I'm pretty sure I should be using DCount or DSum but I am unable to access the help files on my PC at work for some reason. Thanks!
Can anyone help? I'm running a report that shows conference delegates names and the various workshop options they are choosing for an event. What I am trying to do is work out if there is a way to count the number of people who will be in each workshop. I have set up the database so that when someone registers for a workshop, I show this through a Yes/No box (ie, put a tick in the box). So my end report looks something like this:
Name Workshop 1 Workshop 2 Workshop 3 Workshop 4
Person 1 [tick] [ ] [ ] [tick]
Person 2 [ ] [tick] [ ] [tick] ….and so on
I have managed to count each column by the following control source: =count([workshop 1]) ; =count([workshop 2]) etc. The problem is the number it gives me is the TOTAL number, not just the number of ‘yes’s’ or ticks.
Is there something I can add to my statement to get the formula to just count the ticks? I tried: =count([workshop 2]=YES (and TRUE), but it still counted the total boxes/lines. Basically I don’t want to include the ‘no' boxes in my count.
ANY HELP anyone can give would be HUGELY appreciated. Thanks :)
Hi I need to count the number of words in each cell/field in my access database. Is there some standard function that does this thing or can i write some code to do it for me? thanks
View 1 Replies View RelatedHELP PLEASE!!! There are two fields in my form that need the following relationship:
1. When I enter 1000 in my "spec no" field in my form, I need my "Number Field" to enter a "1"
2. When I enter 1000 in my "spec no" field in the next record, I need the "Number Field" for this record to enter "2" but "1" to remain on the first record.
3. If I enter 1001 in my "spec no" field in the next record, I need the "Number Field" to enter "1"
4. Continue this logic for all records without modifying the previous "Number Field" record.
I was told there was a way to accomplish this using DCount or Count function. Maybe there's an easier way??? Any help is greatly appreciated!! TX!
Greetings!
I'm trying to make an Attendance Report for my students. I want the report to show each student and how many times they've been "Present", "Late" or "Tardy" in a month and in one year.
I've set up my db with two tables.
Table 1 - contains Student ID and Student Names
Table 2 - contains Attendance ID, course id, student id and the Status ("Present", "Late" or "Absent")
I used the sum queries and it doesn't do the count function properly. This is a copy of my query.
Thanks :o
Surfette
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
I have a query with 2 fields.
The first is called:TypeOfMedia: Left([MediaID],1)
Grouped BY
Result
C
D
G
The second is called: CountOfProducts: Count(*)
Expression
Result:
1
15
1
This query has counted the number of CDs, DVDs and Games entered. The first letter of the MediaID fields indicates what type of product it is. A typical DVD entry would be: D2387
I now need to implement the totals on a form with other fields such as the name of the DVD.
The other fields are:
Ptitle: text
Type: text
Cost: currency
Penalty: currency
The trouble I am having is to successfully implement a count procedure where I can have three fields each showing how many of the various products there are, open at the same time.
Would any of my esteemed colleagues out there have a solution to this problem?
Regards
Terence
I am using the “count function” on an MS Access 2003 Form field. The count function seems to be working fine except it is not placing the result into the table field.
Does anybody know what I am doing wrong?
Can someone show me the proper format for using the count function used on a form field, where the result is to be placed in a table field?
Please Help!!!
Seti UNIVERSE
http://www.setiuniverse.com/
All of the SETI (The SEARCH for EXTRA--TERRESTRIAL INTELLIGENCE) disciplines in one place.
(Radio, Optical, Microwave, and Amateur SETI)
Stop by Seti UNIVERSE and find out, what you didn't know about SETI.
What expression do i use to count the number of patientID when there are duplicates (ie patient has come back for more than one visit but I just want to know the number of patients who visited (not how many times))
Count([patientID]) doesn't exclude duplicates
I have a single table that has 20 columns. 19 columns (1 for each question) have cells with 1 of 6 distinct values, "SA", "A", "N", "D", "SD", or "DK". The 20th column has a value that identifies that records source. I am trying to create a report that would prompt an individual for the records source, and then once entered, calculate the number of individuals that answered that particular question with one of the six distinct values. It should also calculate what percentage of individuals specificied that distinct answer for that question.
I thought I would just create a query that prompted the user for the record source id and perform the necessary count functions for my particular arguments. However, I get an error saying the query is too complex.
I also tried using DCount directly in the report with the table as the source, but I got an error and none of the totals added up right.
Any and all help is greatly appreciated. If I am unclear in what is going on, I would be happy to post clarification. Thank you for your time.
I am using several queries with Region Codes coded in the query. My reports are also based on the individual queries and has a Record count statement as well --
=DCount("[State]","Reporting Query1"). All is working fine.
Now to minimize the number of queries I need to write, I want to prompt for the Region code. I can do that [Enter Region Code:]. It prompts and the report shows the expected output (only records for that region code) BUT my count statement ceases to work and I get #Error instead of the number of records. This happens no matter which field I set to prompt.
Not sure if I worded my subject correctly. Here's my problem.. I've used query to combine 2 tables of data..
Here are my fields..
I've assignment_num field, project_num field, project_name field, emplyee_name field.
here are my data..
assignment_num field: 1001, 1002, 1003, 1004, 1005
project_num field: 20,20,15,16,15
employee_name field: tom peter, tom peter, tom peter, julie, sally
What I want is to display the employee name with 2 projects or more...
What i have done?
I've done a group by and then count project_num field.. I also added a condition whereby the countofproject_num > 1.. everything went well except that the countofproject_num display 3 instead 2..
pls help.
DCount function.
Code:
Me.ImprovementNotice5DayCount = DCount("[txtReferralReason]", "qryRTOFileReferralPopupCount", "[ComplianceTargetDate]-[DateNow]<=5")
I am not sure where I have gone wrong.
What I would like Dcount to count are those dates in the ComplianceTargetDate form control that are <=5 to the DateNow form control.
I get a count of 3 when there is only one. I may have the syntax of the Dcount wrong.
I have loads of projects in a table, they all have a status (eg Red, Green, Amber).
I have a query for each so if you run qry_RagRed, it will show in a sheet all Red projects and obviously have a total in there.
So I have a form which has buttons on it for navigation, what I want is to display various things on there so for example, a field that simply has the number of red projects, or green etc.
All I want it to do is tell me a number based upon number of items in a query.
I want to be able to use the Count function that will reside in the header of the main navigation form that will count the records in each navigation sub form (of which there are 6 sub forms)
Each sub form has the same primary key which is what I am doing the count on.
I have done this on another form with no subforms by creating a Textbox with this code: =Count([PCNumber])
I have tried to put this in the header of the main navigation form with various itterations, the last being: =Count([Form]![NavigationSubform].[PCNumber])
But what ever I do seems to come up with "Error" in the textbox.
For info, the form names are:
Main form :StockViewsForm
Subform:NavigationSubform
PCNumber being the PK I want to count.
Is this possible of do I need to specify the names of the actual forms that reside in the Navigation Sub Form ??
[URL] ....